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
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" <jhahes.1t777b_1123089037.2843@excelforum-nospam.com> wrote in
message news:jhahes.1t777b_1123089037.2843@excelforum-nospam.com...
>
> 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
>
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
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" <jhahes.1t7cqz_1123096032.2606@excelforum-nospam.com> wrote in
message news:jhahes.1t7cqz_1123096032.2606@excelforum-nospam.com...
>
> 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
>
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" <jhahes.1t7cqz_1123096032.2606@excelforum-nospam.com> wrote in
message news:jhahes.1t7cqz_1123096032.2606@excelforum-nospam.com...
>
> 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
>
? "AAAA" > "Sheet1"
False
--
Regards,
Tom Ogilvy
"STEVE BELL" <AYNrand451@verizon.net> 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" <jhahes.1t7cqz_1123096032.2606@excelforum-nospam.com> wrote in
> message news:jhahes.1t7cqz_1123096032.2606@excelforum-nospam.com...
> >
> > 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
> >
>
>
Tom,
My typo (the following worked)
If ActiveSheet.Name <> "Sheet1" Then
ActiveSheet.Visible = xlVeryHidden
Sheets("Sheet1").Activate
End If
Change "Sheet1" to actual worksheet name
or use Activesheet.Index <>1
Sheets(1).acitvate
--
steveB
Remove "AYN" from email to respond
"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:umRgnOGmFHA.2904@TK2MSFTNGP14.phx.gbl...
>? "AAAA" > "Sheet1"
> False
>
> --
> Regards,
> Tom Ogilvy
>
> "STEVE BELL" <AYNrand451@verizon.net> 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" <jhahes.1t7cqz_1123096032.2606@excelforum-nospam.com> wrote in
>> message news:jhahes.1t7cqz_1123096032.2606@excelforum-nospam.com...
>> >
>> > 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
>> >
>>
>>
>
>
Thank you both for the help. It works great! Really appreciate it, now I don't have to install commandbuttons on 30 sheets.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks