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

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

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

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

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

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

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

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)


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