no workbooks open

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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
 
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
 
Back
Top