pause a macro after a message box comes up

[still learning]

Hi
How would I pause this macro till the user moves the cursor?

Sub numberoffill() 
Dim I As Long
MsgBox " put cursor on start "
For I = 1 To 6
tofillrows
Next I
lastrow
End Sub

mike

 

[Micron]

AFAIK, the message box is modal, so code will not advance until it is dismissed. That means mouse events won't fire before that (e.g. cursor movement) so it's not clear (to me at least) what it is that you want to happen.

EDIT -now thinking that you want code to resume when user dismisses the message but not until user places cursor somewhere. I don't think you can, but the right approach might be to use a mouse event and the coordinates of the cursor. I believe that event would be MouseMove (at least it would be in Access).

 

[still learning]

Hi Micron
yea. after the user starts the macro from an icon on the ribbon, the message box will tell the user to move the cursor to where the next macro should start. The next macro will change the fill color of a few cells across, drop down two lines and do it again for 6 times. Then the cursor will find the last cell in "A" and move to 1 cell below.
The "START" is always changing. I had a input box that the user would enter the cell number in, but i was wondering if i could make it different (easier) !!!
I've never used ACCESS
I'll go back to using the input box.
Thanks for your time.

mike

 

[Micron]

You probably should be using the sheet SelectionChange event. When user selects (mouse, Enter or Tab key) this event runs. You can verify that the selected cell is in a column, row, cell address or range if you want (and proceed if so or cancel if not). You might even just use sheet BeforeDoubleClick or BeforeRightClick event and capture the exact cell you want to use as the anchor/starting point (but I think not if it's to be a range of cells). If you stick with an input box, use type 8 because it captures a range address as the input, and this can be 1 cell or a range of cells.

 

[Jaafar Tribak]

With a couple of win32 calls, you can mimic a 'Modeless' Msgbox which will allow the user to select worksheet cells while the MsgBox is on display and then the code will resume after the MsgBox is dismissed.

1- Place this code in a new Standard Module:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function EnableWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal fEnable As Long) As Long
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
#Else
    Private Declare Function EnableWindow Lib "user32" (ByVal hwnd As Long, ByVal fEnable As Long) As Long
    Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
#End If

Public Sub Make_MsgBox_Modeless()
    Call SetTimer(Application.hwnd, 0&, 0&, AddressOf MakeModeless)
End Sub

Private Sub MakeModeless()
    Call KillTimer(Application.hwnd, 0&)
    Call EnableWindow(Application.hwnd, -1&)
    Application.Cursor = xlDefault
End Sub

2- Usage

Sub Test()
    Call Make_MsgBox_Modeless
    MsgBox "Put cursor on start"
    'Do something on Start cell(s) after dismissing the MsgBox.
    ActiveWindow.RangeSelection.Interior.Color = vbRed
End Sub