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

hollifd

Board Regular
Joined
Apr 3, 2002
Messages
248
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
 
Upvote 0
TsTom,

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

Thanks for your time and help.

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

Code:
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,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-05 11:06
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top