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? |
pause macro until user selects correct range
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" wrote in message ... 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? |
pause macro until user selects correct range
hi JCIrish
Check out Application.InputBox in the VBA help with Type:=8 -- Regards Ron de Bruin http://www.rondebruin.nl "JCIrish" wrote in message ... 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? |
pause macro until user selects correct range
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" wrote in message ... 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? |
pause macro until user selects correct range
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" wrote in message ... 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? |
pause macro until user selects correct range
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" wrote in message ... 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" wrote in message ... 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? |
pause macro until user selects correct range
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" wrote in message ... 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" wrote in message ... 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? |
All times are GMT +1. The time now is 03:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com