Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AddIn event procedure to detect no workbooks open? (VBA/Excel/2000)

    I wrote an Excel AddIn, which among other things, if loaded, adds a custom menu to the Excel menubar.

    The code is something like this:

    Private Sub Workbook_AddinInstall()
    With Application.CommandBars("Worksheet Menu Bar").Controls.Add(msoControlPopup)
    .Caption = "T&est"
    With .Controls
    With .Add(msoControlButton)
    .Caption = "&1. Macro 1"
    .OnAction = "Macro1"
    End With
    With .Add(msoControlButton)
    .Caption = "&2. Macro 2"
    .OnAction = "Macro2"
    End With
    End With
    End With
    End Sub

    Private Sub Workbook_AddinUninstall()
    Application.CommandBars("Worksheet Menu Bar").Controls("T&est").Delete
    End Sub

    Okay, no big deal. But what I can't figure out is what event procedure to use to determine if there are no workbooks open, because if that's the case, what I want to do is disable the "Macro1" and "Macro2" menu items in my custom menu, and only re-enable them if there's at least one workbook open.

    Does anyone have an idea how to do this?

    Thanks!

    Stephan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AddIn event procedure to detect no workbooks open? (VBA/Excel/2000)

    Perhaps this function will help - I don't know how robust it is.

    Function CountWB() As Integer
    Dim i As Integer, n As Integer
    For i = 1 To Workbooks.Count
    If Workbooks(i).Windows(1).Visible = True Then
    n = n + 1
    End If
    Next i
    CountWB = n
    End Function

    The function counts the number of visible workbooks (so as to exclude Personal.xls, which is usually hidden.) You could test for CountWB > 0.

  3. #3
    New Lounger
    Join Date
    Aug 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AddIn event procedure to detect no workbooks open? (VBA/Excel/2000)

    Hans,

    Yes, thank you, your function would definitely help, but the question is where do I call it from?

    Basically, my situation is this: if the user has my Excel AddIn loaded, my custom menu is visible. But let's say he closes all the workbooks, then I don't want my custom menu items enabled. Excel disables some of its own built-in menu items when no workbooks are open. Can we replicate this with VBA for custom AddIn menus like mine, or is this simply not possible?

    Thanks again,

    Stephan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AddIn event procedure to detect no workbooks open? (VBA/Excel/2000)

    If you want to keep your menus up-to-date, you must use application-level events, probably the WindowActivate and WindowDeactivate events of the Application object. The long thread starting at <post#=91268>post 91268</post#> provides examples of how to use these. It's a rather technical stuff!

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AddIn event procedure to detect no workbooks open? (VBA/Excel/2000)

    You could add a Class module to your AddIn, say named xlAppClass.

    In that class module you could incorporate Hans

  6. #6
    New Lounger
    Join Date
    Aug 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AddIn event procedure to detect no workbooks open? (VBA/Excel/2000)

    Thanks guys, I appreciate your responses. They've been very helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •