Automatically Assigning a Macro to a button

[David]

Hi, this is my first post on Google groups, here goes, I have this
program that, given a lifespan of years say from 2004 to 2008, it
creates a sheet for each year, then it creates a button for each sheet
on the "Cover Sheet". What i'm trying to do is to get my program to
assign a macro to each button that takes it to its corresponding
sheet. I can get it to assign a macro to it but the macro doesn't
know what sheet the button is supposed to send you to. I was thinking
if i can read the name of the button put it into a variable in the
little macro takes you to whatever page then do a Sheets().Select with
the variable name in the parenthesis. Problem is I don't know how to
read the buttons name. I just want one macro that can be flexible
enough to take you to any sheet given the buttons name. Any help would
be greatly appreciated, thank you.

 

[Bob Phillips]

You can test the button's name and act upon that, like so

If Application.Caller = "Button 1" Then
MsgBox "hello"
ElseIf Application.Caller = "Button 2" Then
MsgBox "goodbye"
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Building on Bob's reply:

Option Explicit
Sub testme03()

On Error Resume Next
Worksheets(ActiveSheet.Buttons(Application.Caller).Caption).Select
If Err.Number <> 0 Then
MsgBox "Design error. Contact David!"
Err.Clear
End If

End Sub

These are buttons from the Forms toolbar, right????

 

[Bob Phillips]

Building on Bob's reply:

Option Explicit
Sub testme03()

On Error Resume Next
Worksheets(ActiveSheet.Buttons(Application.Caller).Caption).Select

That's neat, like it.

If Err.Number <> 0 Then
MsgBox "Design error. Contact David!"

Are you offering a Help Desk service Dave<vbg>?

 

[Dave Peterson]

Not Dave, David.

(and not David McRitchie, either! Well, unless he wants to???)

 

[David]

Yes, thank you this line of code that you provided did the trick!,

Worksheets(ActiveSheet.Buttons(Application.Caller).Caption).Select

The buttons are made from the forms toolbar. The program is for a
school project, it creates a table of sunrise and sunset times for a
given year using data from the US Naval Observatoy website. The
buttons are created on a cover sheet and will take you to any year
that has been created in the workbook. Thank you very much.

 

[David McRitchie]

Wouldn't you be able to that with a hyperlink for the cell or
a HYPERLINK Worksheet Formula. See
Worksheets in VBA Coding and in Worksheet Formulas
http://www.mvps.org/dmcritchie/excel/sheets.htm#hyperlink

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

Additional possibilities in
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm

Just like in your web browser you can use the Back and Forward
buttons (Alt+ArrowLeft and Alt+ArrowRight) or the turquoise web
toolbar buttons you can install.