Dull addin menu item

[Rob]

Hello,

Is it possible to dull the menu item of an addin that I've created when a
workbook isn't open, if so, advise much appreciated. Rob

 

[Bob Phillips]

set the enabled property to false.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rob]

Bob,

Can you please point me in the right direction as to where in the code below
I would enter False to disable and how to switch to active (True) once a
workbook is open.

Thanks, Rob

Sub AddMenuItem()
Dim ToolsMenu As CommandBarPopup
Dim NewMenuItem As CommandBarButton

' Delete the menu if it already exists
Call DeleteMenuItem

' Find the Tools Menu
Set ToolsMenu = CommandBars(1).FindControl(ID:=30007)
If ToolsMenu Is Nothing Then
MsgBox "Cannot add a menu item."
Exit Sub
Else
Set NewMenuItem = ToolsMenu.Controls.Add _
(Type:=msoControlButton)
With NewMenuItem
.Caption = "&Reset last cell on each worksheet"
.FaceId = 10
' .ShortcutText = "Ctrl+Shift+C"
.OnAction = "DeleteUnused"
.BeginGroup = True
End With
End If

' Create the shortcut key
' Application.MacroOptions _
' Macro:="DeleteUnused", _
' HasShortcutKey:=True, _
' ShortcutKey:=""
End Sub

 

[Bob Phillips]

Rob,

Sub AddMenuItem()
Dim ToolsMenu As CommandBarPopup
Dim NewMenuItem As CommandBarButton

' Delete the menu if it already exists
Call DeleteMenuItem

' Find the Tools Menu
Set ToolsMenu = CommandBars(1).FindControl(ID:=30007)
If ToolsMenu Is Nothing Then
MsgBox "Cannot add a menu item."
Exit Sub
Else
Set NewMenuItem = ToolsMenu.Controls.Add _
(Type:=msoControlButton)
With NewMenuItem
.Caption = "&Reset last cell on each worksheet"
.FaceId = 10
.Enabled = False
.Tag = "ResetCell"
' .ShortcutText = "Ctrl+Shift+C"
.OnAction = "DeleteUnused"
.BeginGroup = True
End With
End If

' Create the shortcut key
' Application.MacroOptions _
' Macro:="DeleteUnused", _
' HasShortcutKey:=True, _
' ShortcutKey:=""
End Sub

I added a tag to make it easy to find again, so you can enable with

Dim NewMenuItem As CommandBarButton
With Application.CommandBars
Set NewMenuItem = .FindControl(Tag:="ResetCell")
NewMenuItem.Enabled = True
End With



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rob]

Bob,

Setting Enabled = False makes the menu item inactive as required but what
would cause the following code to run when any workbook is open. I can add
this code to Auto Open in a known file and the menu item becomes active but
I need to activate the menu when any workbook is opened.

Your help is much appreciated. Rob

 

[Bob Phillips]

Rob,

You need to enable application events. This a multi-step process, which I
would add to the same workbook as that menu code

This is what you should so


Firstly, all of this code goes in the designated workbook.



'========================================Insert a class module, rename it to
'clsAppEvents', with this code

Option Explicit


Public WithEvents App As Application


Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim NewMenuItem As CommandBarButton

With Application.CommandBars
Set NewMenuItem = .FindControl(Tag:="ResetCell")
NewMenuItem.Enabled = True
End With
End Sub


'========================================In ThisWorkbook code module, add
this event code

Dim AppClass As New clsAppEvents


Private Sub Workbook_Open()


Set AppClass.App = Application


End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rob]

Bob,
This isn't working for me, is it because its an Addin and this can't sense
whether or not a workbook is open or not? I already had Call AddMenuItem in
the Workbook_Open() but this doesn't appear to be required with your
suggested text, however, the menu item is still active when there's no
workbook open.

I've had a look at some other Addins and these also show active menu items
even though they do nothing until a workbook is open. Maybe I'm too fussy!

Regards, Rob

 

[Bob Phillips]

Rob,

It should all be fine. You do need the have the menu code in Workbook_Open
as well.

Send me the workbook, I will sort it for you (be quick, the festivities
start soon ).

--

HTH

RP
(remove nothere from the email address if mailing direct)