0

I have a code below to send message to all the contacts in column A. Even though I have input screenupdating = false, I will still see my screen flickering.

Anyone know how to turn off the screen movement?

Sub msg_click()

Application.ScreenUpdating = False

Range("A3").Activate

Do Until ActiveCell.Value = ""
'executing
Application.ScreenUpdating = False
ActiveWorkbook.FollowHyperlink Address:="https://wa.me/" & ActiveCell.Value & "?text=" & ActiveCell.Offset(0, 8).Text
Application.Wait Now() + TimeSerial(0, 0, 3) 'ok just one wait and sendkeys :v
SendKeys "~"

ActiveCell.Offset(1, 0).Activate
Loop

Application.ScreenUpdating = True
End

End Sub
Jia Liang
  • 3
  • 1
  • 3

2 Answers2

0

Try this

   Sub msg_click()
        
        Application.ScreenUpdating = False
        
        i = 3
        
        Do Until Cells(i, 3).Value = "" 'executing
            ActiveWorkbook.FollowHyperlink Address:="https://wa.me/" & Cells(i ,3).Value & "?text=" & Cells(i, 9).Text 
            Application.Wait Now() + TimeSerial(0, 0, 3) 'ok just one wait and sendkeys :v SendKeys "~"
        
            i = i + 1  
        Loop
        Application.ScreenUpdating = True
    End Sub

I think you should be able to take out the screen updating part and it should still work.

Jimber
  • 58
  • 5
  • Sorry. I had some trouble getting the format right. If you use Cells(*, *).Value instead of ActiveCell.value you can avoid the movement. Note that it is also much faster. – Jimber Oct 31 '20 at 04:55
  • Thanks for your reply. The screen still moving here and there. – Jia Liang Oct 31 '20 at 13:00
0

1. Remove Application.ScreenUpdating = False from inside the loop.

2. Avoid the use of .Text. You may want to see What is the difference between .text, .value, and .value2?

3. Avoid use of .Activate, ActiveWorkbook, ActiveCell etc. You may want to see How to avoid using Select in Excel VBA. Declare and work with objects directly.

4. Find the Last Row in the range and use a For loop to traverse through the range. That would be much easier.

5. Since you are handling events, use proper error handling

Does this help? (Untested)

Option Explicit

Sub msg_click()
    Dim ws As Worksheet
    Dim prevStateScreenUpdating As Boolean
    Dim lRow As Long
    Dim i As Long
    
    On Error GoTo Whoa
    
    '~~> Store previous state
    prevStateScreenUpdating = Application.ScreenUpdating
    
    '~~> Change it to false
    Application.ScreenUpdating = False
    
    '~~> Set this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Loop through the range
        For i = 3 To lRow
            ThisWorkbook.FollowHyperlink Address:="https://wa.me/" & _
            .Range("A" & i).Value2 & _
            "?text=" & _
            .Range("I" & i).Value2

           '~~> Wait for 3 seconds. Change as applicable
            Wait 3
        
            SendKeys "~"
        Next i
    End With
    
LetContinue:
    '~~> Reset user settings
    Application.ScreenUpdating = prevStateScreenUpdating
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetContinue
End Sub

Private Sub Wait(ByVal nSec As Long)
    nSec = nSec + Timer
    While nSec > Timer
        DoEvents
    Wend
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks for the reply, Unfortunately it is not working. The first row number do not send out the message , and the screen still moving here and there. – Jia Liang Oct 31 '20 at 12:59
  • `The first row number do not send out the message ,` Ah I made a small mistake. I have rectified it. `the screen still moving here and there.` What do you mean? – Siddharth Rout Oct 31 '20 at 15:41
  • I mean Application.screenupdating do not work here, I will still see the screen moving when the code is running. Apart from this, after the code is executed, there will be number of the browser tabs open, is there anyway to close the tab via VBA ? – Jia Liang Nov 01 '20 at 07:08