Main Page Button In excel

To Whom it may concern:

When I create a "Main Page" button to help navigate through my workbook, I
have to constantly re-create the button properties & code.

How would I create one button to be used on several worksheets within a work
book?

Example:
Private Sub CMD_Main_Index_Click()
Sheets("Main Index").Select
End Sub

--
Thanks,
Andy

One method, may seem complicated at first, but works nice:

1) Copy the following to the "ThisWorkbook" module:

Private Sub Workbook_Activate()
Call ShortCutMenuModify
End Sub

Private Sub Workbook_Deactivate()
Call ShortCutMenuReset
End Sub

2) Copy the following into a standard code module:

Private Const cstrShortCutMenu1 As String = "CUSTOM SHORTCUT MENU"

Public Function ShortCutMenuModify()
'ADD CUSTOM CONTROL TO TOP OF EXCEL'S BUILT-IN RIGHT-CLICK SHORTCUT MENU:
CALLED ON WORKBOOK ACTIVATE
Dim cmdBar As CommandBar
Dim lngX As Long
For lngX = 1 To Application.CommandBars.Count
If CommandBars(lngX).Type = msoBarTypePopup _
And CommandBars(lngX).BuiltIn = True _
And lngX <> 35 Then
Set cmdBar = Application.CommandBars(lngX)
With cmdBar
.Controls.Add Type:=msoControlButton, Before:=1
.Controls(1).Caption = "Go Home"
.Controls(1).FaceId = 5828
.Controls(1).OnAction = "Run_Shortcut_Menu_1"
End With
End If
Next lngX
End Function

Public Function ShortCutMenuReset()
'RESET EXCEL'S BUILT-IN RIGHT-CLICK SHORTCUT MENU: CALLED ON WORKBOOK
DEACTIVATE
Dim cmdBar As CommandBar
Dim lngX As Long
For lngX = 1 To Application.CommandBars.Count
If CommandBars(lngX).Type = msoBarTypePopup And
CommandBars(lngX).BuiltIn = True Then CommandBars(lngX).Reset
Next lngX
End Function

Public Sub Run_Shortcut_Menu_1()
'CommandBars("Workbook Tabs").ShowPopup
Sheets("TEST").Activate
End Sub

3) Modify the "ShortCutMenuModify" procedure where it says: "Go Home" to
whatever you want it to be.

4) Modify the "Run_Shortcut_Menu_1" procedure where it says: "TEST" to your
home page sheet name.

TO USE: Right click in any sheet and "Go Home" (or your custom description)
appears as the first choice in the menu; when user clicks this choice, the
home sheet is activated. Non-invasive and runs nice. Does not affect any
other workbooks.

HTH

"Andy" wrote:

> To Whom it may concern:
>
> When I create a "Main Page" button to help navigate through my workbook, I
> have to constantly re-create the button properties & code.
>
> How would I create one button to be used on several worksheets within a work
> book?
>
> Example:
> Private Sub CMD_Main_Index_Click()
> Sheets("Main Index").Select
> End Sub
>
> --
> Thanks,
> Andy

XP,

How do I remove the "Go Home" from my right-click?

Thank you very much, but unfortunately this will be to complicate for the
Nurses & Dr's to right click & select a menu option, yet I love this. They
need a simple button on a worksheet that they may click to take them back to
an index page & then help re-navigate back to different hospital floors.

For Example: Sheet("4thFloor") may have 4 buttons such as hand-washing, vap,
cardiac, etc... which when clicked would take them to a new worksheet.

Again, THANK YOU! so much for your help thus far.


Thanks,
Andy


"XP" wrote:

> One method, may seem complicated at first, but works nice:
>
> 1) Copy the following to the "ThisWorkbook" module:
>
> Private Sub Workbook_Activate()
> Call ShortCutMenuModify
> End Sub
>
> Private Sub Workbook_Deactivate()
> Call ShortCutMenuReset
> End Sub
>
> 2) Copy the following into a standard code module:
>
> Private Const cstrShortCutMenu1 As String = "CUSTOM SHORTCUT MENU"
>
> Public Function ShortCutMenuModify()
> 'ADD CUSTOM CONTROL TO TOP OF EXCEL'S BUILT-IN RIGHT-CLICK SHORTCUT MENU:
> CALLED ON WORKBOOK ACTIVATE
> Dim cmdBar As CommandBar
> Dim lngX As Long
> For lngX = 1 To Application.CommandBars.Count
> If CommandBars(lngX).Type = msoBarTypePopup _
> And CommandBars(lngX).BuiltIn = True _
> And lngX <> 35 Then
> Set cmdBar = Application.CommandBars(lngX)
> With cmdBar
> .Controls.Add Type:=msoControlButton, Before:=1
> .Controls(1).Caption = "Go Home"
> .Controls(1).FaceId = 5828
> .Controls(1).OnAction = "Run_Shortcut_Menu_1"
> End With
> End If
> Next lngX
> End Function
>
> Public Function ShortCutMenuReset()
> 'RESET EXCEL'S BUILT-IN RIGHT-CLICK SHORTCUT MENU: CALLED ON WORKBOOK
> DEACTIVATE
> Dim cmdBar As CommandBar
> Dim lngX As Long
> For lngX = 1 To Application.CommandBars.Count
> If CommandBars(lngX).Type = msoBarTypePopup And
> CommandBars(lngX).BuiltIn = True Then CommandBars(lngX).Reset
> Next lngX
> End Function
>
> Public Sub Run_Shortcut_Menu_1()
> 'CommandBars("Workbook Tabs").ShowPopup
> Sheets("TEST").Activate
> End Sub
>
> 3) Modify the "ShortCutMenuModify" procedure where it says: "Go Home" to
> whatever you want it to be.
>
> 4) Modify the "Run_Shortcut_Menu_1" procedure where it says: "TEST" to your
> home page sheet name.
>
> TO USE: Right click in any sheet and "Go Home" (or your custom description)
> appears as the first choice in the menu; when user clicks this choice, the
> home sheet is activated. Non-invasive and runs nice. Does not affect any
> other workbooks.
>
> HTH
>
> "Andy" wrote:
>
> > To Whom it may concern:
> >
> > When I create a "Main Page" button to help navigate through my workbook, I
> > have to constantly re-create the button properties & code.
> >
> > How would I create one button to be used on several worksheets within a work
> > book?
> >
> > Example:
> > Private Sub CMD_Main_Index_Click()
> > Sheets("Main Index").Select
> > End Sub
> >
> > --
> > Thanks,
> > Andy

Andy,

Sorry this didn't help you, perhaps you will find a future use for it. I use
it very frequently - all my users are well trained on how to find the
"button".

Actually, you don't need to remove the custom item, it removes itself
automatically anytime you close or switch to another workbook.

Regards---

"Andy" wrote:

> XP,
>
> How do I remove the "Go Home" from my right-click?
>
> Thank you very much, but unfortunately this will be to complicate for the
> Nurses & Dr's to right click & select a menu option, yet I love this. They
> need a simple button on a worksheet that they may click to take them back to
> an index page & then help re-navigate back to different hospital floors.
>
> For Example: Sheet("4thFloor") may have 4 buttons such as hand-washing, vap,
> cardiac, etc... which when clicked would take them to a new worksheet.
>
> Again, THANK YOU! so much for your help thus far.
>
>
> Thanks,
> Andy
>
>
> "XP" wrote:
>
> > One method, may seem complicated at first, but works nice:
> >
> > 1) Copy the following to the "ThisWorkbook" module:
> >
> > Private Sub Workbook_Activate()
> > Call ShortCutMenuModify
> > End Sub
> >
> > Private Sub Workbook_Deactivate()
> > Call ShortCutMenuReset
> > End Sub
> >
> > 2) Copy the following into a standard code module:
> >
> > Private Const cstrShortCutMenu1 As String = "CUSTOM SHORTCUT MENU"
> >
> > Public Function ShortCutMenuModify()
> > 'ADD CUSTOM CONTROL TO TOP OF EXCEL'S BUILT-IN RIGHT-CLICK SHORTCUT MENU:
> > CALLED ON WORKBOOK ACTIVATE
> > Dim cmdBar As CommandBar
> > Dim lngX As Long
> > For lngX = 1 To Application.CommandBars.Count
> > If CommandBars(lngX).Type = msoBarTypePopup _
> > And CommandBars(lngX).BuiltIn = True _
> > And lngX <> 35 Then
> > Set cmdBar = Application.CommandBars(lngX)
> > With cmdBar
> > .Controls.Add Type:=msoControlButton, Before:=1
> > .Controls(1).Caption = "Go Home"
> > .Controls(1).FaceId = 5828
> > .Controls(1).OnAction = "Run_Shortcut_Menu_1"
> > End With
> > End If
> > Next lngX
> > End Function
> >
> > Public Function ShortCutMenuReset()
> > 'RESET EXCEL'S BUILT-IN RIGHT-CLICK SHORTCUT MENU: CALLED ON WORKBOOK
> > DEACTIVATE
> > Dim cmdBar As CommandBar
> > Dim lngX As Long
> > For lngX = 1 To Application.CommandBars.Count
> > If CommandBars(lngX).Type = msoBarTypePopup And
> > CommandBars(lngX).BuiltIn = True Then CommandBars(lngX).Reset
> > Next lngX
> > End Function
> >
> > Public Sub Run_Shortcut_Menu_1()
> > 'CommandBars("Workbook Tabs").ShowPopup
> > Sheets("TEST").Activate
> > End Sub
> >
> > 3) Modify the "ShortCutMenuModify" procedure where it says: "Go Home" to
> > whatever you want it to be.
> >
> > 4) Modify the "Run_Shortcut_Menu_1" procedure where it says: "TEST" to your
> > home page sheet name.
> >
> > TO USE: Right click in any sheet and "Go Home" (or your custom description)
> > appears as the first choice in the menu; when user clicks this choice, the
> > home sheet is activated. Non-invasive and runs nice. Does not affect any
> > other workbooks.
> >
> > HTH
> >
> > "Andy" wrote:
> >
> > > To Whom it may concern:
> > >
> > > When I create a "Main Page" button to help navigate through my workbook, I
> > > have to constantly re-create the button properties & code.
> > >
> > > How would I create one button to be used on several worksheets within a work
> > > book?
> > >
> > > Example:
> > > Private Sub CMD_Main_Index_Click()
> > > Sheets("Main Index").Select
> > > End Sub
> > >
> > > --
> > > Thanks,
> > > Andy

THank you very much for trying. I will keep this handy as I do see a need for
it. I did learn a lot from this as well.

Yet, I deletd out all the code & I still have the options, but I don't
really care.

--
Thanks,
Andy


"XP" wrote:

> Andy,
>
> Sorry this didn't help you, perhaps you will find a future use for it. I use
> it very frequently - all my users are well trained on how to find the
> "button".
>
> Actually, you don't need to remove the custom item, it removes itself
> automatically anytime you close or switch to another workbook.
>
> Regards---
>
> "Andy" wrote:
>
> > XP,
> >
> > How do I remove the "Go Home" from my right-click?
> >
> > Thank you very much, but unfortunately this will be to complicate for the
> > Nurses & Dr's to right click & select a menu option, yet I love this. They
> > need a simple button on a worksheet that they may click to take them back to
> > an index page & then help re-navigate back to different hospital floors.
> >
> > For Example: Sheet("4thFloor") may have 4 buttons such as hand-washing, vap,
> > cardiac, etc... which when clicked would take them to a new worksheet.
> >
> > Again, THANK YOU! so much for your help thus far.
> >
> >
> > Thanks,
> > Andy
> >
> >
> > "XP" wrote:
> >
> > > One method, may seem complicated at first, but works nice:
> > >
> > > 1) Copy the following to the "ThisWorkbook" module:
> > >
> > > Private Sub Workbook_Activate()
> > > Call ShortCutMenuModify
> > > End Sub
> > >
> > > Private Sub Workbook_Deactivate()
> > > Call ShortCutMenuReset
> > > End Sub
> > >
> > > 2) Copy the following into a standard code module:
> > >
> > > Private Const cstrShortCutMenu1 As String = "CUSTOM SHORTCUT MENU"
> > >
> > > Public Function ShortCutMenuModify()
> > > 'ADD CUSTOM CONTROL TO TOP OF EXCEL'S BUILT-IN RIGHT-CLICK SHORTCUT MENU:
> > > CALLED ON WORKBOOK ACTIVATE
> > > Dim cmdBar As CommandBar
> > > Dim lngX As Long
> > > For lngX = 1 To Application.CommandBars.Count
> > > If CommandBars(lngX).Type = msoBarTypePopup _
> > > And CommandBars(lngX).BuiltIn = True _
> > > And lngX <> 35 Then
> > > Set cmdBar = Application.CommandBars(lngX)
> > > With cmdBar
> > > .Controls.Add Type:=msoControlButton, Before:=1
> > > .Controls(1).Caption = "Go Home"
> > > .Controls(1).FaceId = 5828
> > > .Controls(1).OnAction = "Run_Shortcut_Menu_1"
> > > End With
> > > End If
> > > Next lngX
> > > End Function
> > >
> > > Public Function ShortCutMenuReset()
> > > 'RESET EXCEL'S BUILT-IN RIGHT-CLICK SHORTCUT MENU: CALLED ON WORKBOOK
> > > DEACTIVATE
> > > Dim cmdBar As CommandBar
> > > Dim lngX As Long
> > > For lngX = 1 To Application.CommandBars.Count
> > > If CommandBars(lngX).Type = msoBarTypePopup And
> > > CommandBars(lngX).BuiltIn = True Then CommandBars(lngX).Reset
> > > Next lngX
> > > End Function
> > >
> > > Public Sub Run_Shortcut_Menu_1()
> > > 'CommandBars("Workbook Tabs").ShowPopup
> > > Sheets("TEST").Activate
> > > End Sub
> > >
> > > 3) Modify the "ShortCutMenuModify" procedure where it says: "Go Home" to
> > > whatever you want it to be.
> > >
> > > 4) Modify the "Run_Shortcut_Menu_1" procedure where it says: "TEST" to your
> > > home page sheet name.
> > >
> > > TO USE: Right click in any sheet and "Go Home" (or your custom description)
> > > appears as the first choice in the menu; when user clicks this choice, the
> > > home sheet is activated. Non-invasive and runs nice. Does not affect any
> > > other workbooks.
> > >
> > > HTH
> > >
> > > "Andy" wrote:
> > >
> > > > To Whom it may concern:
> > > >
> > > > When I create a "Main Page" button to help navigate through my workbook, I
> > > > have to constantly re-create the button properties & code.
> > > >
> > > > How would I create one button to be used on several worksheets within a work
> > > > book?
> > > >
> > > > Example:
> > > > Private Sub CMD_Main_Index_Click()
> > > > Sheets("Main Index").Select
> > > > End Sub
> > > >
> > > > --
> > > > Thanks,
> > > > Andy