pause macro until user selects correct range

[Guest]

Within an event handler for a worksheet command button I display a msgBox
telling user to select a specific cell. When user clicks OK on the msgbox
how do I pause the macro until he selects the correct cell?

 

[Tom Ogilvy]

Possibly something along the lines of

Dim rng as Range
Dim cnt as Long
do
cnt = cnt + 1
On error resume Next
set rng = Application.InputBox("Select cell with mouse",type:=8)
On error goto 0
do while not rng is nothing or cnt > 4
if cnt > 4 then exit sub

 

[Ron de Bruin]

hi JCIrish

Check out

Application.InputBox in the VBA help with Type:=8

 

[Guest]

Thanks, Tom, for the help. What I did is seen below, for pasting Quicken Data
into a specific cell. First, a command button which when clicked gives
instructons to select B2. Then a Selection change to force selection of B2.
My problem is I don't know how to shut off the selection change handler once
B2 has been selected and the Quicken data pasted there. I continues to prompt
selection of B2 ever after!!
Any suggestions?

JCIrish

Private Sub btnStartQuicken_Click()

Dim readyCheck

MsgBox("Are you ready to enter Quicken Values?", vbYesNoCancel)

If readyCheck = vbYes Then

MsgBox "Select Cell B2"
End If
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = Range("B2").Address Then
MsgBox "Paste Quicken Values"

Else
MsgBox "You must select Cell B2"

End If
End Sub

 

[Guest]

Hi, Ron, and thanks. I will try that approach. Here's some code I showed to
Tom. With the command button I ask if the user is ready to paste Quicken
data. The selection change handler is designed to force selection of B2. The
problem I have is that I don't know how to disable the handler after the
selection B2 has been made and the Quicken data pasted. Ever after I'm
prompted to select B2 with each new click! Any solution to this problem?
Thanks.


Private Sub btnStartQuicken_Click()

Dim readyCheck

MsgBox("Are you ready to enter Quicken Values?", vbYesNoCancel)

If readyCheck = vbYes Then

MsgBox "Select Cell B2"
End If
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = Range("B2").Address Then
MsgBox "Paste Quicken Values"

Else
MsgBox "You must select Cell B2"

End If
End Sub

 

[Tom Ogilvy]

Get rid of the selectionchange event

Private Sub btnStartQuicken_Click()
Dim readyCheck as Long
readyCheck = MsgBox("Ready to Quicken Values in clipboard?",
vbYesNoCancel)
If readyCheck = vbYes Then
On Error goto ErrHandler:
Range("B2").Select
Activesheet.Paste
End If
exit sub
ErrHandler:
Msgbox "Apparently the clipboard was empty"
End Sub

If B2 is the only choice, why horse around.

 

[Guest]

Wow, Tom! That's a whole lot neater than the cumbersome code this rookie
wrote! I'll give it a shot. Thanks again

JCIrish