Custom right click menu Excel Add-in not loading
up vote
0
down vote
favorite
I've used the Ron de Bruin tutorial to create a custom right click menu.
This works no problem when using it in an XLSM file type, but when I try to turn this into an Excel Add-in, the right click menu doesn't appear. Is there something specific I need to add in to the code to enable it?
The code I used in 'ThisWorkbook' is:
Private Sub Workbook_Activate()
Call AddToCellMenu
End Sub
Private Sub Workbook_Deactivate()
Call DeleteFromCellMenu
End Sub
The code I used in a separate module is:
Sub AddToCellMenu()
Dim ContextMenu As CommandBar
Dim MySubMenu As CommandBarControl
Call DeleteFromCellMenu
Set ContextMenu = Application.CommandBars("Cell")
Set MySubMenu = ContextMenu.Controls.Add(Type:=msoControlPopup, before:=3)
With MySubMenu
.Caption = "Recruitment"
.Tag = "My_Cell_Control_Tag"
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Rejections"
.FaceId = 97
.Caption = "Format Rejections"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Hires"
.FaceId = 87
.Caption = "Format Hires"
End With
End With
ContextMenu.Controls(4).BeginGroup = True
End Sub
Sub DeleteFromCellMenu()
Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl
Set ContextMenu = Application.CommandBars("Cell")
For Each ctrl In ContextMenu.Controls
If ctrl.Tag = "My_Cell_Control_Tag" Then
ctrl.Delete
End If
Next ctrl
On Error Resume Next
ContextMenu.FindControl(ID:=3).Delete
On Error GoTo 0
End Sub
Rejections and Hires are the two macros I'm asking for it to call. These are simple macros that I know work ok so didn't bother adding the code for those.
excel vba excel-vba excel-2010 excel-addins
asked Nov 19 at 10:34
S. Laycock
12
add a comment |
up vote
0
down vote
favorite
I've used the Ron de Bruin tutorial to create a custom right click menu.
This works no problem when using it in an XLSM file type, but when I try to turn this into an Excel Add-in, the right click menu doesn't appear. Is there something specific I need to add in to the code to enable it?
The code I used in 'ThisWorkbook' is:
Private Sub Workbook_Activate()
Call AddToCellMenu
End Sub
Private Sub Workbook_Deactivate()
Call DeleteFromCellMenu
End Sub
The code I used in a separate module is:
Sub AddToCellMenu()
Dim ContextMenu As CommandBar
Dim MySubMenu As CommandBarControl
Call DeleteFromCellMenu
Set ContextMenu = Application.CommandBars("Cell")
Set MySubMenu = ContextMenu.Controls.Add(Type:=msoControlPopup, before:=3)
With MySubMenu
.Caption = "Recruitment"
.Tag = "My_Cell_Control_Tag"
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Rejections"
.FaceId = 97
.Caption = "Format Rejections"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Hires"
.FaceId = 87
.Caption = "Format Hires"
End With
End With
ContextMenu.Controls(4).BeginGroup = True
End Sub
Sub DeleteFromCellMenu()
Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl
Set ContextMenu = Application.CommandBars("Cell")
For Each ctrl In ContextMenu.Controls
If ctrl.Tag = "My_Cell_Control_Tag" Then
ctrl.Delete
End If
Next ctrl
On Error Resume Next
ContextMenu.FindControl(ID:=3).Delete
On Error GoTo 0
End Sub
Rejections and Hires are the two macros I'm asking for it to call. These are simple macros that I know work ok so didn't bother adding the code for those.
excel vba excel-vba excel-2010 excel-addins
asked Nov 19 at 10:34
S. Laycock
12
Please edit your question and show exactly the code you use in your project (alternatively add a Minimal, Complete, and Verifiable example). Links to similar code are not sufficient we need to see your code (links might break in future and the question becomes useless to other readers). Also note that "it won't work" is no useful error description. Tell us which error you get and in which line or what your code actually does vs. what you expect it to do.
– Pᴇʜ
Nov 19 at 10:42
2
If you're using an add-in, you probably want theWorkbook_OpenandWorkbook_BeforeCloseevents rather than the Activate/Deactivate ones.
– Rory
Nov 19 at 10:51
@Rory that seems to have done the trick. The Workbook_BeforeClose command didn't work so I reverted to using deactivate. Thank you.
– S. Laycock
Nov 19 at 11:35
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I've used the Ron de Bruin tutorial to create a custom right click menu.
This works no problem when using it in an XLSM file type, but when I try to turn this into an Excel Add-in, the right click menu doesn't appear. Is there something specific I need to add in to the code to enable it?
The code I used in 'ThisWorkbook' is:
Private Sub Workbook_Activate()
Call AddToCellMenu
End Sub
Private Sub Workbook_Deactivate()
Call DeleteFromCellMenu
End Sub
The code I used in a separate module is:
Sub AddToCellMenu()
Dim ContextMenu As CommandBar
Dim MySubMenu As CommandBarControl
Call DeleteFromCellMenu
Set ContextMenu = Application.CommandBars("Cell")
Set MySubMenu = ContextMenu.Controls.Add(Type:=msoControlPopup, before:=3)
With MySubMenu
.Caption = "Recruitment"
.Tag = "My_Cell_Control_Tag"
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Rejections"
.FaceId = 97
.Caption = "Format Rejections"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Hires"
.FaceId = 87
.Caption = "Format Hires"
End With
End With
ContextMenu.Controls(4).BeginGroup = True
End Sub
Sub DeleteFromCellMenu()
Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl
Set ContextMenu = Application.CommandBars("Cell")
For Each ctrl In ContextMenu.Controls
If ctrl.Tag = "My_Cell_Control_Tag" Then
ctrl.Delete
End If
Next ctrl
On Error Resume Next
ContextMenu.FindControl(ID:=3).Delete
On Error GoTo 0
End Sub
Rejections and Hires are the two macros I'm asking for it to call. These are simple macros that I know work ok so didn't bother adding the code for those.
excel vba excel-vba excel-2010 excel-addins
asked Nov 19 at 10:34
S. Laycock
12
I've used the Ron de Bruin tutorial to create a custom right click menu.
This works no problem when using it in an XLSM file type, but when I try to turn this into an Excel Add-in, the right click menu doesn't appear. Is there something specific I need to add in to the code to enable it?
The code I used in 'ThisWorkbook' is:
Private Sub Workbook_Activate()
Call AddToCellMenu
End Sub
Private Sub Workbook_Deactivate()
Call DeleteFromCellMenu
End Sub
The code I used in a separate module is:
Sub AddToCellMenu()
Dim ContextMenu As CommandBar
Dim MySubMenu As CommandBarControl
Call DeleteFromCellMenu
Set ContextMenu = Application.CommandBars("Cell")
Set MySubMenu = ContextMenu.Controls.Add(Type:=msoControlPopup, before:=3)
With MySubMenu
.Caption = "Recruitment"
.Tag = "My_Cell_Control_Tag"
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Rejections"
.FaceId = 97
.Caption = "Format Rejections"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Hires"
.FaceId = 87
.Caption = "Format Hires"
End With
End With
ContextMenu.Controls(4).BeginGroup = True
End Sub
Sub DeleteFromCellMenu()
Dim ContextMenu As CommandBar
Dim ctrl As CommandBarControl
Set ContextMenu = Application.CommandBars("Cell")
For Each ctrl In ContextMenu.Controls
If ctrl.Tag = "My_Cell_Control_Tag" Then
ctrl.Delete
End If
Next ctrl
On Error Resume Next
ContextMenu.FindControl(ID:=3).Delete
On Error GoTo 0
End Sub
Rejections and Hires are the two macros I'm asking for it to call. These are simple macros that I know work ok so didn't bother adding the code for those.
excel vba excel-vba excel-2010 excel-addins
excel vba excel-vba excel-2010 excel-addins
asked Nov 19 at 10:34
S. Laycock
12
asked Nov 19 at 10:34
S. Laycock
12
edited Nov 19 at 11:25
asked Nov 19 at 10:34
S. Laycock
12
asked Nov 19 at 10:34
S. Laycock
12
asked Nov 19 at 10:34
S. Laycock
12
12
Please edit your question and show exactly the code you use in your project (alternatively add a Minimal, Complete, and Verifiable example). Links to similar code are not sufficient we need to see your code (links might break in future and the question becomes useless to other readers). Also note that "it won't work" is no useful error description. Tell us which error you get and in which line or what your code actually does vs. what you expect it to do.
– Pᴇʜ
Nov 19 at 10:42
2
If you're using an add-in, you probably want theWorkbook_OpenandWorkbook_BeforeCloseevents rather than the Activate/Deactivate ones.
– Rory
Nov 19 at 10:51
@Rory that seems to have done the trick. The Workbook_BeforeClose command didn't work so I reverted to using deactivate. Thank you.
– S. Laycock
Nov 19 at 11:35
add a comment |
Please edit your question and show exactly the code you use in your project (alternatively add a Minimal, Complete, and Verifiable example). Links to similar code are not sufficient we need to see your code (links might break in future and the question becomes useless to other readers). Also note that "it won't work" is no useful error description. Tell us which error you get and in which line or what your code actually does vs. what you expect it to do.
– Pᴇʜ
Nov 19 at 10:42
2
If you're using an add-in, you probably want theWorkbook_OpenandWorkbook_BeforeCloseevents rather than the Activate/Deactivate ones.
– Rory
Nov 19 at 10:51
@Rory that seems to have done the trick. The Workbook_BeforeClose command didn't work so I reverted to using deactivate. Thank you.
– S. Laycock
Nov 19 at 11:35
Please edit your question and show exactly the code you use in your project (alternatively add a Minimal, Complete, and Verifiable example). Links to similar code are not sufficient we need to see your code (links might break in future and the question becomes useless to other readers). Also note that "it won't work" is no useful error description. Tell us which error you get and in which line or what your code actually does vs. what you expect it to do.
– Pᴇʜ
Nov 19 at 10:42
Please edit your question and show exactly the code you use in your project (alternatively add a Minimal, Complete, and Verifiable example). Links to similar code are not sufficient we need to see your code (links might break in future and the question becomes useless to other readers). Also note that "it won't work" is no useful error description. Tell us which error you get and in which line or what your code actually does vs. what you expect it to do.
– Pᴇʜ
Nov 19 at 10:42
2
2
If you're using an add-in, you probably want the
Workbook_Open and Workbook_BeforeClose events rather than the Activate/Deactivate ones.– Rory
Nov 19 at 10:51
If you're using an add-in, you probably want the
Workbook_Open and Workbook_BeforeClose events rather than the Activate/Deactivate ones.– Rory
Nov 19 at 10:51
@Rory that seems to have done the trick. The Workbook_BeforeClose command didn't work so I reverted to using deactivate. Thank you.
– S. Laycock
Nov 19 at 11:35
@Rory that seems to have done the trick. The Workbook_BeforeClose command didn't work so I reverted to using deactivate. Thank you.
– S. Laycock
Nov 19 at 11:35
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53372731%2fcustom-right-click-menu-excel-add-in-not-loading%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please edit your question and show exactly the code you use in your project (alternatively add a Minimal, Complete, and Verifiable example). Links to similar code are not sufficient we need to see your code (links might break in future and the question becomes useless to other readers). Also note that "it won't work" is no useful error description. Tell us which error you get and in which line or what your code actually does vs. what you expect it to do.
– Pᴇʜ
Nov 19 at 10:42
2
If you're using an add-in, you probably want the
Workbook_OpenandWorkbook_BeforeCloseevents rather than the Activate/Deactivate ones.– Rory
Nov 19 at 10:51
@Rory that seems to have done the trick. The Workbook_BeforeClose command didn't work so I reverted to using deactivate. Thank you.
– S. Laycock
Nov 19 at 11:35