Get non active worksheet name by mouse click excel 2007 VBA

[Phrayzur]

I'm new to this (actually any) forum. So please bear with me...

The problem I have is:

In a workbook I have a calculation sheet and worksheets that contain the data to calculate named in a "mm-dd-yyyy" format. When I switch to a different day I have a macro that prompts me for the worksheet name. I need to get the worksheet name that I want to switch to by clicking on the worksheet tab.

I can't for the life of me figure it out...

Any help would be appreciated.

 

[shg]

You could list the worksheet names in an InputBox and prompt the user to enter the number:

Select a sheet:
1. Bob
2. Joe
3. Bill

 

[Phrayzur]

That's a good idea. Actually, that's a great idea! Thanks!

I wonder how you'd do the other tho...

 

[shg]

You could set a public Boolean ("bLooking"). Instead of an InputBox, display a modeless userform. In the SheetActivate event, check if the Boolean is true, and if so, set a public Worksheet variable, close the userform, clear the Boolean, and kick off the rest of the code.