Command Button / Inputbox Questions

[Guest]

I have a workbook that contains several sheets. I am aware that one can
click on the sheettab to change sheets. However, when I was wanting to learn
VBA, I played around with creating command buttons on each sheet that would
switch to the corresponding sheet when one was clicked. I also learned that
each commandbutton was sheet specific, ie everytime a commandbutton was
created, it was created with reference to Sheet1, 2, 3, etc. FYI: The names
of the different sheets in my workbook are "STATS", "PCEMS", "PAS", "2005",
"2006", "2007".

Now I am trying to consolidate in order to try & learn different ways to do
the same thing:

1) Can a command button be created that works across the board for the whole
workbook? The idea I had, especially for the different years, is to have a
commandbutton w/ caption "YEAR". When it is clicked an Inputbox would pop up
asking for the year you wish to view, then switch according when OK clicked.
I don't want to have to write the code for each sheet. I can handle the code
for the Inputbox, just need to know about the first part of question.

2) As I write this question, different options are already flowing through
my little brain. Can in Inputbox contain a listbox or combo box? If I have
a command button that simply says "Change Screens" and then have "something"
pop up that allows the user to choose which screen to change to. Can this be
done, first of all, and secondly, can it only be written once with each
commandbutton on each sheet referencing the same code?

Thanks,
Les

 

[Tom Ogilvy]

Use buttons from the forms toolbar instead. You can assign a single macro
to all the buttons.

Public Btn_click() ' you can use any name for the macro
Dim ans as string, sh as Worksheet
ans = InputBox("Enter Year")
on error resume next
set sh = worksheets(ans)
On error goto 0
if not sh is nothing then
sh.Activate
else
msgbox "Bad answer"
end if
end Sub

If you want dropdowns and so forth, then you will need to create a userform
(or dialogsheet. )