pause macro until user selects correct range

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?

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


--
Regards,
Tom Ogilvy


"JCIrish" <JCIrish@discussions.microsoft.com> wrote in message
news:D3649B6A-4058-4026-9B0B-42CF13A60471@microsoft.com...
> 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?

hi JCIrish

Check out

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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"JCIrish" <JCIrish@discussions.microsoft.com> wrote in message news:D3649B6A-4058-4026-9B0B-42CF13A60471@microsoft.com...
> 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?

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

"Tom Ogilvy" wrote:

> 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
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "JCIrish" <JCIrish@discussions.microsoft.com> wrote in message
> news:D3649B6A-4058-4026-9B0B-42CF13A60471@microsoft.com...
> > 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?
>
>
>

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


"Ron de Bruin" wrote:

> hi JCIrish
>
> Check out
>
> Application.InputBox in the VBA help with Type:=8
>
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "JCIrish" <JCIrish@discussions.microsoft.com> wrote in message news:D3649B6A-4058-4026-9B0B-42CF13A60471@microsoft.com...
> > 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?
>
>
>

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.

--
Regards,
Tom Ogilvy




"JCIrish" <JCIrish@discussions.microsoft.com> wrote in message
news:66DDED46-0E07-4CE0-A15B-240E9BEAE0D7@microsoft.com...
> 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
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "JCIrish" <JCIrish@discussions.microsoft.com> wrote in message
> > news:D3649B6A-4058-4026-9B0B-42CF13A60471@microsoft.com...
> > > 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?
> >
> >
> >

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

JCIrish

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "JCIrish" <JCIrish@discussions.microsoft.com> wrote in message
> news:66DDED46-0E07-4CE0-A15B-240E9BEAE0D7@microsoft.com...
> > 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
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "JCIrish" <JCIrish@discussions.microsoft.com> wrote in message
> > > news:D3649B6A-4058-4026-9B0B-42CF13A60471@microsoft.com...
> > > > 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?
> > >
> > >
> > >
>
>
>