Automatically Assigning a Macro to a button
 

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.

Automatically Assigning a Macro to a button
 

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)

"David" wrote in message
m...
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.

Automatically Assigning a Macro to a button
 

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 wrote:

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)

"David" wrote in message
m...
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.

--

Dave Peterson

Automatically Assigning a Macro to a button
 

"Dave Peterson" wrote in message
...
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?

Automatically Assigning a Macro to a button
 

Not Dave, David.

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



Bob Phillips wrote:

"Dave Peterson" wrote in message
...
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

Automatically Assigning a Macro to a button
 

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.


Dave Peterson wrote in message ...
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????

Automatically Assigning a Macro to a button
 

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....htm#hyperlink

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheeton e!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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David" wrote in message om...
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.


Dave Peterson wrote in message ...
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????