I have added a menu item with several sub-menu commands as part of an add-in
I am developing. Many of these commands deal with modifying or inserting
cell formulas, so I would like to disable those commands if no workbook is
open or if the activesheet is not a worksheet. How do I go about doing
this? I am using WinXP, XL2003.

Look at application level events

http://www.cpearson.com/excel/appevent.htm

You would have to use the application level workbook close event to
determine when all the visible workbooks are closed.

You would use the sheetactivate and deactivate events to determine if it is
a worksheet or not.

--
Regards,
Tom Ogilvy


"M. Authement" wrote in message
...
I have added a menu item with several sub-menu commands as part of an
add-in I am developing. Many of these commands deal with modifying or
inserting cell formulas, so I would like to disable those commands if no
workbook is open or if the activesheet is not a worksheet. How do I go
about doing this? I am using WinXP, XL2003.

Maybe i'm just confused but if no Workbook is open why would you need to
disable the commands?

"M. Authement" wrote:

I have added a menu item with several sub-menu commands as part of an add-in
I am developing. Many of these commands deal with modifying or inserting
cell formulas, so I would like to disable those commands if no workbook is
open or if the activesheet is not a worksheet. How do I go about doing
this? I am using WinXP, XL2003.

Thanks Tom. I will give it a try tonight and see how it goes.


"Tom Ogilvy" wrote in message
...
Look at application level events

http://www.cpearson.com/excel/appevent.htm

You would have to use the application level workbook close event to
determine when all the visible workbooks are closed.

You would use the sheetactivate and deactivate events to determine if it
is a worksheet or not.

--
Regards,
Tom Ogilvy


"M. Authement" wrote in message
...
I have added a menu item with several sub-menu commands as part of an
add-in I am developing. Many of these commands deal with modifying or
inserting cell formulas, so I would like to disable those commands if no
workbook is open or if the activesheet is not a worksheet. How do I go
about doing this? I am using WinXP, XL2003.

Because Excel can be open without a workbook being open. I don't want a
user trying to select a program if/when this situation is present.

"JLGWhiz" wrote in message
...
Maybe i'm just confused but if no Workbook is open why would you need to
disable the commands?

"M. Authement" wrote:

I have added a menu item with several sub-menu commands as part of an
add-in
I am developing. Many of these commands deal with modifying or inserting
cell formulas, so I would like to disable those commands if no workbook
is
open or if the activesheet is not a worksheet. How do I go about doing
this? I am using WinXP, XL2003.

OK, I got it to work if I want to disable the entire menu. Here is the code
I used in the sheet activate event.

Private Sub xlApp_SheetActivate(ByVal Sh As Object)

Dim bEnableDisable As Boolean

If TypeName(Sh) = "Worksheet" Then
bEnableDisable = True
Else
bEnableDisable = False
End If
Debug.Print TypeName(Sh) & ", " & bEnableDisable
'On Error Resume Next
xlApp.CommandBars(1).Controls("PIMS TIPS").Enabled = bEnableDisable
'On Error GoTo 0

End Sub

I have several controls under the PIMS TIPS menu and would prefer to disable
them at that level but could not figure out how to access them. What I have
above will work, but can you help me understand how to reference a control's
controls?


"Tom Ogilvy" wrote in message
...
Look at application level events

http://www.cpearson.com/excel/appevent.htm

You would have to use the application level workbook close event to
determine when all the visible workbooks are closed.

You would use the sheetactivate and deactivate events to determine if it
is a worksheet or not.

--
Regards,
Tom Ogilvy


"M. Authement" wrote in message
...
I have added a menu item with several sub-menu commands as part of an
add-in I am developing. Many of these commands deal with modifying or
inserting cell formulas, so I would like to disable those commands if no
workbook is open or if the activesheet is not a worksheet. How do I go
about doing this? I am using WinXP, XL2003.

with xlApp.CommandBars(1).Controls("PIMS TIPS")
for i = 1 to .count
controls(i).Enabled = False
Next i
End with


or
xlApp.CommandBars(1).Controls("PIMS TIPS").Controls("MyFirstItem").Enabled
= False

for the specific control with a caption of "MyFirstItem" as an example.

--
Regards,
Tom Ogilvy


"M. Authement" wrote in message
...
OK, I got it to work if I want to disable the entire menu. Here is the
code I used in the sheet activate event.

Private Sub xlApp_SheetActivate(ByVal Sh As Object)

Dim bEnableDisable As Boolean

If TypeName(Sh) = "Worksheet" Then
bEnableDisable = True
Else
bEnableDisable = False
End If
Debug.Print TypeName(Sh) & ", " & bEnableDisable
'On Error Resume Next
xlApp.CommandBars(1).Controls("PIMS TIPS").Enabled = bEnableDisable
'On Error GoTo 0

End Sub

I have several controls under the PIMS TIPS menu and would prefer to
disable them at that level but could not figure out how to access them.
What I have above will work, but can you help me understand how to
reference a control's controls?


"Tom Ogilvy" wrote in message
...
Look at application level events

http://www.cpearson.com/excel/appevent.htm

You would have to use the application level workbook close event to
determine when all the visible workbooks are closed.

You would use the sheetactivate and deactivate events to determine if it
is a worksheet or not.

--
Regards,
Tom Ogilvy


"M. Authement" wrote in message
...
I have added a menu item with several sub-menu commands as part of an
add-in I am developing. Many of these commands deal with modifying or
inserting cell formulas, so I would like to disable those commands if no
workbook is open or if the activesheet is not a worksheet. How do I go
about doing this? I am using WinXP, XL2003.

that should have been

for i = 1 to .Controls.count

I think I left off the "controls" before count in the first example.

--
regards,
Tom Ogilvy

"M. Authement" wrote in message
...
OK, I got it to work if I want to disable the entire menu. Here is the
code I used in the sheet activate event.

Private Sub xlApp_SheetActivate(ByVal Sh As Object)

Dim bEnableDisable As Boolean

If TypeName(Sh) = "Worksheet" Then
bEnableDisable = True
Else
bEnableDisable = False
End If
Debug.Print TypeName(Sh) & ", " & bEnableDisable
'On Error Resume Next
xlApp.CommandBars(1).Controls("PIMS TIPS").Enabled = bEnableDisable
'On Error GoTo 0

End Sub

I have several controls under the PIMS TIPS menu and would prefer to
disable them at that level but could not figure out how to access them.
What I have above will work, but can you help me understand how to
reference a control's controls?


"Tom Ogilvy" wrote in message
...
Look at application level events

http://www.cpearson.com/excel/appevent.htm

You would have to use the application level workbook close event to
determine when all the visible workbooks are closed.

You would use the sheetactivate and deactivate events to determine if it
is a worksheet or not.

--
Regards,
Tom Ogilvy


"M. Authement" wrote in message
...
I have added a menu item with several sub-menu commands as part of an
add-in I am developing. Many of these commands deal with modifying or
inserting cell formulas, so I would like to disable those commands if no
workbook is open or if the activesheet is not a worksheet. How do I go
about doing this? I am using WinXP, XL2003.

Here is a rework:

with xlApp.CommandBars(1).Controls("PIMS TIPS")
for i = 1 to .Controls.count
.controls(i).Enabled = False
Next i
End with

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
that should have been

for i = 1 to .Controls.count

I think I left off the "controls" before count in the first example.

--
regards,
Tom Ogilvy

"M. Authement" wrote in message
...
OK, I got it to work if I want to disable the entire menu. Here is the
code I used in the sheet activate event.

Private Sub xlApp_SheetActivate(ByVal Sh As Object)

Dim bEnableDisable As Boolean

If TypeName(Sh) = "Worksheet" Then
bEnableDisable = True
Else
bEnableDisable = False
End If
Debug.Print TypeName(Sh) & ", " & bEnableDisable
'On Error Resume Next
xlApp.CommandBars(1).Controls("PIMS TIPS").Enabled = bEnableDisable
'On Error GoTo 0

End Sub

I have several controls under the PIMS TIPS menu and would prefer to
disable them at that level but could not figure out how to access them.
What I have above will work, but can you help me understand how to
reference a control's controls?


"Tom Ogilvy" wrote in message
...
Look at application level events

http://www.cpearson.com/excel/appevent.htm

You would have to use the application level workbook close event to
determine when all the visible workbooks are closed.

You would use the sheetactivate and deactivate events to determine if it
is a worksheet or not.

--
Regards,
Tom Ogilvy


"M. Authement" wrote in message
...
I have added a menu item with several sub-menu commands as part of an
add-in I am developing. Many of these commands deal with modifying or
inserting cell formulas, so I would like to disable those commands if no
workbook is open or if the activesheet is not a worksheet. How do I go
about doing this? I am using WinXP, XL2003.

I agree. You could only use it for the problem you originally described
which doesn't appear to have much correlation to what you are now asking.

--
Regards,
Tom Ogilvy


"M. Authement" wrote in message
...
Because Excel can be open without a workbook being open. I don't want a
user trying to select a program if/when this situation is present.

"JLGWhiz" wrote in message
...
Maybe i'm just confused but if no Workbook is open why would you need to
disable the commands?

"M. Authement" wrote:

I have added a menu item with several sub-menu commands as part of an
add-in
I am developing. Many of these commands deal with modifying or
inserting
cell formulas, so I would like to disable those commands if no workbook
is
open or if the activesheet is not a worksheet. How do I go about doing
this? I am using WinXP, XL2003.

I thought I tried .Controls().Controls last night and it didn't work, but it
is working now so I must have done something wrong. I set the Tag property
for each of the controls I want to enable/disable the same and use an If
statement within the For i= statement. Thanks for all of your help Tom, I
really appreciate it!


Private Sub xlApp_SheetActivate(ByVal Sh As Object)

Dim bEnableDisable As Boolean
Dim i As Integer

If TypeName(Sh) = "Worksheet" Then
bEnableDisable = True
Else
bEnableDisable = False
End If

On Error Resume Next
With xlApp.CommandBars(1).Controls("PIMS TIPS")
For i = 1 To .Controls.Count
If .Controls(i).Tag = "PTDisable" Then .Controls(i).Enabled =
bEnableDisable
Next i
End With
On Error GoTo 0

End Sub

"Tom Ogilvy" wrote in message
...
Here is a rework:

with xlApp.CommandBars(1).Controls("PIMS TIPS")
for i = 1 to .Controls.count
.controls(i).Enabled = False
Next i
End with

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
that should have been

for i = 1 to .Controls.count

I think I left off the "controls" before count in the first example.

--
regards,
Tom Ogilvy

"M. Authement" wrote in message
...
OK, I got it to work if I want to disable the entire menu. Here is the
code I used in the sheet activate event.

Private Sub xlApp_SheetActivate(ByVal Sh As Object)

Dim bEnableDisable As Boolean

If TypeName(Sh) = "Worksheet" Then
bEnableDisable = True
Else
bEnableDisable = False
End If
Debug.Print TypeName(Sh) & ", " & bEnableDisable
'On Error Resume Next
xlApp.CommandBars(1).Controls("PIMS TIPS").Enabled = bEnableDisable
'On Error GoTo 0

End Sub

I have several controls under the PIMS TIPS menu and would prefer to
disable them at that level but could not figure out how to access them.
What I have above will work, but can you help me understand how to
reference a control's controls?


"Tom Ogilvy" wrote in message
...
Look at application level events

http://www.cpearson.com/excel/appevent.htm

You would have to use the application level workbook close event to
determine when all the visible workbooks are closed.

You would use the sheetactivate and deactivate events to determine if
it is a worksheet or not.

--
Regards,
Tom Ogilvy


"M. Authement" wrote in message
...
I have added a menu item with several sub-menu commands as part of an
add-in I am developing. Many of these commands deal with modifying or
inserting cell formulas, so I would like to disable those commands if
no workbook is open or if the activesheet is not a worksheet. How do I
go about doing this? I am using WinXP, XL2003.

Disregard, this was posted in the wrong thread.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
I agree. You could only use it for the problem you originally described
which doesn't appear to have much correlation to what you are now asking.

--
Regards,
Tom Ogilvy


"M. Authement" wrote in message
...
Because Excel can be open without a workbook being open. I don't want a
user trying to select a program if/when this situation is present.

"JLGWhiz" wrote in message
...
Maybe i'm just confused but if no Workbook is open why would you need to
disable the commands?

"M. Authement" wrote:

I have added a menu item with several sub-menu commands as part of an
add-in
I am developing. Many of these commands deal with modifying or
inserting
cell formulas, so I would like to disable those commands if no workbook
is
open or if the activesheet is not a worksheet. How do I go about doing
this? I am using WinXP, XL2003.