calling a macro
 

How do I call a macro, for instance I have 50 sheets on every sheet I
want to put a command button to go back to the main page (Sheet1). How
do I create one macro that goes Sheet1.activate and be able to call that
to run. I am new to the terminology of vba. Do I have to create a new
module or put it in this workbook?

Thanks for any help


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=392594

calling a macro
 

Why not make a floating toolbar/commandbar with a single button that
activates sheet1.

Create the toolbar in the workbook_Open event and destroy it in the
workbook_BeforeClose event:

Here is an article about creating commandbars with code:
http://msdn.microsoft.com/library/techart/ofcmdbar.htm

Chip Pearson's page on Events:
http://www.cpearson.com/excel/events.htm

More on CommandBars:
http://support.microsoft.com/default...02&Product=xlw
How to customize menus and menu bars in Excel

http://support.microsoft.com/default...b;en-us;166755
File Title: Customizing Menu Bars, Menus, and Menu Items in Microsoft(R)
Excel 97
File Name: WE1183.EXE
File Size: 58041 bytes
File Date: 06/20/97
Keywords: kbfile kbappnote
Description: This Application Note can help you learn techniques for writing
Visual Basic(R) for Applications code to customize menus in Microsoft Excel
97. This Application Note contains code examples that you can use with the
following elements: menu bars, menus, menu items, submenus, and shortcut
menus.

--
Regards,
Tom Ogilvy


"jhahes" wrote in
message ...

How do I call a macro, for instance I have 50 sheets on every sheet I
want to put a command button to go back to the main page (Sheet1). How
do I create one macro that goes Sheet1.activate and be able to call that
to run. I am new to the terminology of vba. Do I have to create a new
module or put it in this workbook?

Thanks for any help


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=392594

calling a macro
 

Thanks Tom, I like that idea, didn't think of it. However the reason I
want each page to go back to the home page is that I would also put
this code in each command bar code.

Call Macro ("Sheet1".activate)
Sheet55.visible = xlhidden ' or whatever sheet i am on

Basically from the main page, a user can go to about 50 or so sheets
and look at the detail of some products, however when done looking I
would like for them to go back to the main page, but it leaves up the
page they just looked at the bottom, and pretty soon I have 25 or so
sheets open. I understand that I can hide all of them on Workbook_Open
or close.

Is there a better solution to this

Thanks for any help


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=392594

calling a macro
 

You can incorporate this in the button to return to the main sheet

If ActiveSheet.Name "Sheet1" Then
ActiveSheet.Visible = xlVeryHidden
Sheets("Sheet1").Activate
End If


--
steveB

Remove "AYN" from email to respond
"jhahes" wrote in
message ...

Thanks Tom, I like that idea, didn't think of it. However the reason I
want each page to go back to the home page is that I would also put
this code in each command bar code.

Call Macro ("Sheet1".activate)
Sheet55.visible = xlhidden ' or whatever sheet i am on

Basically from the main page, a user can go to about 50 or so sheets
and look at the detail of some products, however when done looking I
would like for them to go back to the main page, but it leaves up the
page they just looked at the bottom, and pretty soon I have 25 or so
sheets open. I understand that I can hide all of them on Workbook_Open
or close.

Is there a better solution to this

Thanks for any help


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=392594

calling a macro
 

when the macro fires using the commandbar button, the sheet will still be
the activesheet

Sub Btn_Click()
if activesheet.name < sheet1.Name then
activesheet.visible = xlSheetHidden
End if
Sheet1.Activate
End sub

--
Regards,
Tom Ogilvy

"jhahes" wrote in
message ...

Thanks Tom, I like that idea, didn't think of it. However the reason I
want each page to go back to the home page is that I would also put
this code in each command bar code.

Call Macro ("Sheet1".activate)
Sheet55.visible = xlhidden ' or whatever sheet i am on

Basically from the main page, a user can go to about 50 or so sheets
and look at the detail of some products, however when done looking I
would like for them to go back to the main page, but it leaves up the
page they just looked at the bottom, and pretty soon I have 25 or so
sheets open. I understand that I can hide all of them on Workbook_Open
or close.

Is there a better solution to this

Thanks for any help


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=392594

calling a macro
 

? "AAAA" "Sheet1"
False

--
Regards,
Tom Ogilvy

"STEVE BELL" wrote in message
news:PS8Ie.32788$Tk6.17013@trnddc02...
You can incorporate this in the button to return to the main sheet

If ActiveSheet.Name "Sheet1" Then
ActiveSheet.Visible = xlVeryHidden
Sheets("Sheet1").Activate
End If


--
steveB

Remove "AYN" from email to respond
"jhahes" wrote in
message ...

Thanks Tom, I like that idea, didn't think of it. However the reason I
want each page to go back to the home page is that I would also put
this code in each command bar code.

Call Macro ("Sheet1".activate)
Sheet55.visible = xlhidden ' or whatever sheet i am on

Basically from the main page, a user can go to about 50 or so sheets
and look at the detail of some products, however when done looking I
would like for them to go back to the main page, but it leaves up the
page they just looked at the bottom, and pretty soon I have 25 or so
sheets open. I understand that I can hide all of them on Workbook_Open
or close.

Is there a better solution to this

Thanks for any help


--
jhahes
------------------------------------------------------------------------
jhahes's Profile:
http://www.excelforum.com/member.php...o&userid=23596
View this thread:
http://www.excelforum.com/showthread...hreadid=392594

calling a macro
 

Thank you both for the help. It works great! Really appreciate it, now
I don't have to install commandbuttons on 30 sheets.


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=392594