no workbooks open

[Guest]

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?

 

[Guest]

simon
if workbooks.count = 0 then

'disable buttons
end if

 

[Guest]

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

 

[Ron de Bruin]

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

 

[Guest]

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

 

[Guest]

thanks,

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

 

[Ron de Bruin]

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