Automatically Assigning a Macro to a button

  • Thread starter Thread starter David
  • Start date Start date
D

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.
 
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)
 
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????
 
Dave Peterson said:
Building on Bob's reply:

Option Explicit
Sub testme03()

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

That's neat, like it.
If Err.Number <> 0 Then
MsgBox "Design error. Contact David!"
Click to expand...

Are you offering a Help Desk service Dave<vbg>?
 
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.
 
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.
 
Back
Top