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" wrote in message
...
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

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

"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
set the enabled property to false.

--

HTH

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


"Rob" wrote in message
...
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