Hi all,

I am trying to do a simple dialog box. I can't figure out how I can
do this. The user has to select appropriate cell ( by clicking into a
cell) in a work sheet to have the output of a macro go into that cell.
Now I wish to provide a dialog box that has ok and cancel. If the user
is ready for the output, he needs to click and select the appropriate
cell in the sheet and then click ok. If user wants to cancel, he can
click on cancel and the function will not run. I am trying to implement
this..Any ideas..thanks in advance...

shishi

Hello Shishi,

You may have a problem doing this. Both the MsgBox and InputBox use
modal dialogs. Once displayed you can not select anything on the
Worksheet. When is the MsgBox called and by what?

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=507811

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address(False, False) = "A1" Then
If MsgBox("Ready?", vbOKCancel) = vbOK Then MyMacroRunsHere
End If

End Sub


"shishi" wrote:

Hi all,

I am trying to do a simple dialog box. I can't figure out how I can
do this. The user has to select appropriate cell ( by clicking into a
cell) in a work sheet to have the output of a macro go into that cell.
Now I wish to provide a dialog box that has ok and cancel. If the user
is ready for the output, he needs to click and select the appropriate
cell in the sheet and then click ok. If user wants to cancel, he can
click on cancel and the function will not run. I am trying to implement
this..Any ideas..thanks in advance...

shishi

Sub Test()
Dim v As Object

On Error Resume Next
Set v = Application.InputBox(prompt:="Select a cell for output, or
cancel", Type:=8)
On Error GoTo 0

If Not v Is Nothing Then
MsgBox "User selected range " & v.Address
Else
MsgBox "User cancelled"
End If

End Sub

--
Tim Williams
Palo Alto, CA


"shishi" wrote in message
oups.com...
Hi all,

I am trying to do a simple dialog box. I can't figure out how I can
do this. The user has to select appropriate cell ( by clicking into a
cell) in a work sheet to have the output of a macro go into that cell.
Now I wish to provide a dialog box that has ok and cancel. If the user
is ready for the output, he needs to click and select the appropriate
cell in the sheet and then click ok. If user wants to cancel, he can
click on cancel and the function will not run. I am trying to implement
this..Any ideas..thanks in advance...

shishi

Here is the code snippet...

Sub DesignateStartingCell()
MsgBox "Select the starting cell for the first column"
Call GenerateNames("START_SPEC", "SPECS")

End Sub

The GenerateNames("START_SPEC", "SPECS"), is going to the generate a
NAMED RANGE at the selected cell. The name is START_SPEC and the sheet
name is SPECS.

May be I should use a msg like "Have you selected appropriate starting
Cell?" If the user clicks on YES, the code runs and if the user clicks
on NO the exit without calling GenerateNames("START_SPEC", "SPECS")...