How do I ask the user to click on a sheet and have that shee

[hollifd]

How do I ask the user to click on a sheet and have that sheet name be stored in a variable that I can later use in a statement like...

Let MyVariable = the worksheet name that the user selected.

Worksheets!MyVariable.Select


'Then do some processing with with this worksheet

Any point in the right direction would be appreciated.

Thanks,

David

[Tom Schreiner]

Hi
This is my idea, not necc the best
The following code will prompt the user to click 'Ok' and select a sheet.
If none is chosen after a few seconds, the prompt will re-appear.
If a sheet is selected, the loop will end and the Public variable SheetNameToSave will store the value of the selected sheet for you to use in your code elswhere.

Dim SheetNameToSave as a public variable in a standard module.

There are sexier ways of using a counter, but this will do for this macro. Adjust the counter's step to more or less to adjust the delay between prompts.

Sub PickSheet()
Dim Cntr
SheetNameToSave = ActiveSheet.Name
ReTry:
MsgBox "Please click 'Ok' and select a sheet"
Cntr = 0
Do Until SheetNameToSave <> ActiveSheet.Name
Cntr = Cntr + 0.005
DoEvents
If Cntr > 100 Then GoTo ReTry:
Loop
SheetNameToSave = ActiveSheet.Name

End Sub

Have a Nice Day!
Tom

[hollifd]

TsTom,

Your idea worked and also gave me other ideas how I could accomplish my needs.

Thanks for your time and help.

David

[NateO]

Knowing full well that the problem is solved, I thought that I would add an inputbox, point & click method.

Sub ShtName()
Dim z As String, n As Range
On Error GoTo errorhandler
Set n = Application.InputBox(prompt:="Click on a range on target worksheet", Type:=8)
z = n.Worksheet.Name
'Worksheets(z).Select 'to select the sheet that was clicked on
Exit Sub
errorhandler: MsgBox ("Unable to determine your 'selected' range. Please try again")
End Sub

Have a nice weekend.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-05 11:06 ]