Resctricted menu

  • Thread starter Thread starter Manuel Murieta
  • Start date Start date
M

Manuel Murieta

I understand how to add a custom menu to excel using the right click on the
menu and creating a custom menu. What I would like to have is a custom menu
that only is available for a specific worksheet. For example I have
invoice.xls. In that there are 3 sheets: Sheet1, Sheet2 and Sheet 3. I
would like a menu to come up when sheet 1 is active, and different menu
options to come up for sheet2 and sheet3. I would also like the menu to
disappear whenever a diferent xls file is active.
 
Add it with the worksheet_activate event, remove it with the
worksheet_deactivate.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Hi Manuel

In thisworkbook module there are events that run on Activate and Deactivate

In the Sheet modules there are also events like this
You can run your code to create/delete the menu items in this events

See this site for more information about events
http://www.cpearson.com/excel/events.htm
 
I am just starting to learn VBA. Can you give me a quick macro to place in
the code on the sheet sheet1 that would activate a menu called "invoice"
whenever the xls file is active and changer the items in the file when
sheet1, sheet2 and sheet3 come up.

For example I want the menu called invoice to be visible as soon as I open
the invoice.xls. When sheet 1 is active the choices in the item would run2
macros, macro1 and macro2. When sheet2 is active then the menu choices
change and macro1 and macro3 and macro4 are visible and active. As soon as
the xls file is no longer active then the menu chjoice disappears.
 
Option Explicit

Private Sub Workbook_Activate()
With Application.CommandBars("Cell")
On Error Resume Next
.Controls("macro 1").Delete
.Controls("macro 2").Delete
.Controls("macro 3").Delete
.Controls("macro 4").Delete
On Error GoTo 0
With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.Caption = "invoice"
If ActiveSheet.Name = "Sheet1" Then
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "macro 1"
.OnAction = "macro1"
End With
ElseIf ActiveSheet.Name = "Sheet1" Then
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "macro 2"
.OnAction = "macro2"
End With
End If
End With
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub

Private Sub Workbook_Deactivate()
With Application.CommandBars("Cell").Controls("invoice")
On Error Resume Next
.Controls("macro 1").Delete
.Controls("macro 2").Delete
.Controls("macro 3").Delete
.Controls("macro 4").Delete
On Error GoTo 0
End With
End Sub

Private Sub Workbook_Deactivate()
With Application.CommandBars("Cell")
On Error Resume Next
.Controls("invoice").Delete
On Error GoTo 0
End With
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Application.CommandBars("Cell").Controls("invoice")
If Sh.Name = "Sheet1" Then
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "macro 1"
.OnAction = "macro1"
End With
ElseIf Sh.Name = "Sheet2" Then
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "macro 2"
.OnAction = "macro2"
End With
End If
End With
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

With Application.CommandBars("Cell").Controls("invoice")
On Error Resume Next
If Sh.Name = "Sheet1" Then
.Controls("macro 1").Delete
.Controls("macro 2").Delete
ElseIf Sh.Name = "Sheet2" Then
.Controls("macro 3").Delete
.Controls("macro 4").Delete
End If
On Error GoTo 0
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top