When no workbooks are open, most toolbuttons and menu options in Excel are
disabled.

I have a menu and toolbar created by an add-in I built. How can I detect in
my addin that no workbooks are open so I can set the toolbuttons and menu
options to disabled?

simon
if workbooks.count = 0 then

'disable buttons
end if
--
When you lose your mind, you free your life.


"Simon Shaw" wrote:

When no workbooks are open, most toolbuttons and menu options in Excel are
disabled.

I have a menu and toolbar created by an add-in I built. How can I detect in
my addin that no workbooks are open so I can set the toolbuttons and menu
options to disabled?

where do I put the code? in ThisWorkbook?

How do I get it to execute? what event occurs when all workbooks are closed?

thanks

Simon


"ben" wrote:

simon
if workbooks.count = 0 then

'disable buttons
end if
--
When you lose your mind, you free your life.


"Simon Shaw" wrote:

When no workbooks are open, most toolbuttons and menu options in Excel are
disabled.

I have a menu and toolbar created by an add-in I built. How can I detect in
my addin that no workbooks are open so I can set the toolbuttons and menu
options to disabled?

um depends where you have the menu being created, but because that workbook
may be closed before all others the code would do you no good. reccomend
putting it in an add-in and the in the workbook close event on the
application level


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

--
When you lose your mind, you free your life.


"Simon Shaw" wrote:

where do I put the code? in ThisWorkbook?

How do I get it to execute? what event occurs when all workbooks are closed?

thanks

Simon


"ben" wrote:

simon
if workbooks.count = 0 then

'disable buttons
end if
--
When you lose your mind, you free your life.


"Simon Shaw" wrote:

When no workbooks are open, most toolbuttons and menu options in Excel are
disabled.

I have a menu and toolbar created by an add-in I built. How can I detect in
my addin that no workbooks are open so I can set the toolbuttons and menu
options to disabled?

Note: that this also count the personal.xls workbook that is hidden

Dim wb As Workbook
Dim Num As Integer
Num = 0
For Each wb In Application.Workbooks
If wb.Windows(1).Visible Then
Num = Num + 1
End If
Next
MsgBox Num


--
Regards Ron de Bruin
http://www.rondebruin.nl


"ben" (remove this if mailing direct) wrote in message
...
simon
if workbooks.count = 0 then

'disable buttons
end if
--
When you lose your mind, you free your life.


"Simon Shaw" wrote:

When no workbooks are open, most toolbuttons and menu options in Excel are
disabled.

I have a menu and toolbar created by an add-in I built. How can I detect in
my addin that no workbooks are open so I can set the toolbuttons and menu
options to disabled?

thanks,

How do I get it to execute when a different workbook is closed?


"Ron de Bruin" wrote:

Note: that this also count the personal.xls workbook that is hidden

Dim wb As Workbook
Dim Num As Integer
Num = 0
For Each wb In Application.Workbooks
If wb.Windows(1).Visible Then
Num = Num + 1
End If
Next
MsgBox Num


--
Regards Ron de Bruin
http://www.rondebruin.nl


"ben" (remove this if mailing direct) wrote in message
...
simon
if workbooks.count = 0 then

'disable buttons
end if
--
When you lose your mind, you free your life.


"Simon Shaw" wrote:

When no workbooks are open, most toolbuttons and menu options in Excel are
disabled.

I have a menu and toolbar created by an add-in I built. How can I detect in
my addin that no workbooks are open so I can set the toolbuttons and menu
options to disabled?

Hi Simon

You can test the count when you try to open the userform of the add-in
But you can also try to set the activesheet and trap the error


Sub OpenUserform()
Dim asheet As Worksheet
Set asheet = Nothing
On Error Resume Next
Set asheet = ActiveSheet
On Error GoTo 0

If asheet Is Nothing Then
MsgBox "Error: There is no active worksheet.", _
vbOKOnly, "Hi there"
Exit Sub
Else
userform1.Show
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ...
thanks,

How do I get it to execute when a different workbook is closed?


"Ron de Bruin" wrote:

Note: that this also count the personal.xls workbook that is hidden

Dim wb As Workbook
Dim Num As Integer
Num = 0
For Each wb In Application.Workbooks
If wb.Windows(1).Visible Then
Num = Num + 1
End If
Next
MsgBox Num


--
Regards Ron de Bruin
http://www.rondebruin.nl


"ben" (remove this if mailing direct) wrote in message
...
simon
if workbooks.count = 0 then

'disable buttons
end if
--
When you lose your mind, you free your life.


"Simon Shaw" wrote:

When no workbooks are open, most toolbuttons and menu options in Excel are
disabled.

I have a menu and toolbar created by an add-in I built. How can I detect in
my addin that no workbooks are open so I can set the toolbuttons and menu
options to disabled?