Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 27

Thread: Custom menu (Office 2002)

  1. #1
    VBAX Regular Sphinx No. 4's Avatar
    Joined
    May 2004
    Location
    Stockholm, Sweden
    Posts
    9
    Location

    Custom menu (Office 2002)

    This is probably a piece of cake for you, guys.

    I have created 3 workbooks, each holding 1 macro hiding certain rows, and 1 macro that shows these rows again, since I don't want to print several
    pages with just zeros in 5 columns. I have also added a new menu to the default menu, where the user then can either hide or show the rows. My custom menu opens and closes when the workbook opens or closes. Very neat!

    Now to my problem. If I open two of these books, and then closes one of them, the custom menu also closes, although I still have one workbook open.

    Can I avoid closing the custom menu as long as any of these workbooks are open?
    Last edited by Sphinx No. 4; 05-25-2004 at 06:15 AM.

  2. #2
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Hi
    Something along below approach should get You started:


    Option Explicit
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wbBook As Workbook
    Dim wsCommandbar As CommandBar
    Dim bFlag As Boolean
    bFlag = False
    With Application
        Set wsCommandbar = .CommandBars(1)
        For Each wbBook In .Workbooks
            If wbBook.Name = "AA.xls" Or wbBook.Name = "BB.xls" Then
                bFlag = True
                Exit For
            End If
        Next wbBook
    End With
    On Error Resume Next
    If bFlag = False Then wsCommandbar.Controls("MyMenu").Delete
    On Error GoTo 0
    End Sub

    Kind regards,
    Dennis
    Last edited by Anne Troy; 05-25-2004 at 09:04 AM.
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  3. #3
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    I think the best idea for you here would be to make use of the Workbook_Activate() and Workbook_Deactivate() Events.

    You will still need to create your Menus within the Workbook_Open() Event, but when you use Controls.Add() make sure that you utilize the 'Temporary' parameter, setting it = True. This way you will not have to worry about deleting the Control when you Workbook closes, for when Excel Quits, these temporary controls will not be present the next time Excel is opened (unless explicitly created again, of course).

    You may also need to pick up if your Control already exists due to another Workbook having already created it. In this case, you would not call Controls.Add(), but would simply Set a variable to the Control in question so that you can set it's .Visible property as required.

    Within the Workbook_Activate() and Workbook_Deactivate() Events I would set the .Visible property of this Control to True and False, respectively.

    Another thought is to toggle it's .Enabled property, so that you can see where it is all the time, but grayed out and unusable.

    That's about it, I hope this was clear!

    -- Mike
    Last edited by Mike_R; 05-25-2004 at 09:02 AM.

  4. #4
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Howdy Mike,

    Good suggestion

    That's about it, I hope this was clear!
    With an example it would be clear for newbies as well

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  5. #5
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Dennis:

    I changed the code tag from code to vba in your post above

    This has now been implemented.
    I'm just not sure it looks right...
    Last edited by Anne Troy; 05-25-2004 at 09:05 AM.
    ~Anne Troy

  6. #6
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Nice

    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  7. #7
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    Missing some Keywords, but I know that Mark can take care of that in a jiffy.

    From the above, it's missing:
    Dim
    End
    With
    If
    On Error
    Resume

    And he may wish to pick up 'On Error Goto 0' as a block, for the linelabel is generally Black, but is Blue when using 'On Error Goto 0'.

    Very cool site going on here... I'm most impressed...

    -- Mike

    (P.S.: You guys may want to shunt off this part of the discussion to an Administrative area or something...)

  8. #8
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location

    Required Code:

    Ok, now back to our show...

    The following should run nicely. It's designed to be placed in the 'ThisWorkbook' class module:

    Option Explicit
    Dim WithEvents m_cbButton As Office.CommandBarButton
    Const m_TAG = "My Unique Tag"
     
    Private Sub m_cbButton_Click(ByVal Ctrl As Office.CommandBarButton, _
    CancelDefault As Boolean)
    ' The Sub MyMacro() is where your code to Hide/Show rows would be:
    Call MyMacro
    End Sub
     
    Private Sub Workbook_Open()
    Dim cbBar As Office.CommandBar
    Set cbBar = Application.CommandBars(1)
    ' (Note that CommandBars(1) is the "Worksheet Menu Bar".)
    On Error Resume Next
    ' Try to find the existing CommandBarButton, if present:
    Set m_cbButton = cbBar.FindControl(msoControlButton, Tag:=m_TAG)
    On Error GoTo 0
    If m_cbButton Is Nothing Then
        ' If we did not find the CommandBarButton, then we make it:
        Set m_cbButton = cbBar.Controls.Add(msoControlButton, Temporary:=True)
        m_cbButton.Tag = m_TAG
        m_cbButton.FaceId = 2950 ' <-- 2950 is a Smiley Face :-)
    End If
    End Sub
     
    Private Sub Workbook_Activate()
    m_cbButton.Visible = True
    End Sub
     
    Private Sub Workbook_Deactivate()
    m_cbButton.Visible = False
    End Sub[/vba] Within a standard code module, you would then need code that looks something like this:[vba]Sub MyMacro()
    If ThisWorkbook Is ActiveWorkbook Then 
        ' Your code goes here.
        ' Your code goes here.
        ' Your code goes here.
    End If
    End Sub
    The point of this is to avoid all your workbooks from fireing every time you hit this button, for it sounds like your Macro is designed to apply to only the ActiveWorkbook.

    See the attached Workbook, which you can make copies of and try running 2 or 3 of them at the same time along with Workbooks that do not have this button-related code within it. You'll see that it runs pretty smoothly...

    I hope this helps! ,
    Mike

    [Edit: Hmm... struggling with the VB-Tags a bit! Ok, switched to Code-Tags You really have a great site here.]
    Last edited by Mike_R; 06-01-2004 at 06:50 PM.

  9. #9
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Mike - very nice

    In case the OP use a non-english version of Excel then this line may fail:

    Set cbBar = Application.CommandBars("Worksheet Menu Bar")

    Which could be replace with the indexnumber:

    Set cbBar = Application.CommandBars(1)

    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  10. #10
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Edit: Hmm... struggling with the VB-Tags a bit! Ok, switched to Code-Tags You really have a great site here.
    I'm so glad you like it! But that's WE HAVE A GREAT SITE!!

    Let us know if we can do anything....
    Last edited by Anne Troy; 05-25-2004 at 10:40 AM.
    ~Anne Troy

  11. #11
    VBAX Regular Mike_R's Avatar
    Joined
    May 2004
    Location
    Plainsboro, NJ
    Posts
    46
    Location
    Hi Dreamy ,

    Yes, very nice site, thanks for making it so friendly.

    Dennis,

    Good pickup! I've now edited the Code shown above as well as the Attachment. So now it's Internationally-compatible. Cool :cool

    Thanks guys ,
    Mike
    Last edited by Mike_R; 05-25-2004 at 10:54 AM.

  12. #12
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Indeed.

    I hope that...six months from now when we're daily hammered with questions...we all still take the time to welcome new members.

    Probably 90+% of the current membership are all people who are more likely to answer questions than to ask. I suspect that six months from now, that percentage will have reversed itself.



    Let's hope the site doesn't go...erm...tits up!
    (Actually, we used to say that about the airplanes when they went hard down when I was in the Navy working on P3s over there in Rota, Spain. I thought it was a military expression and hadn't heard it in 25 years...)
    Last edited by Anne Troy; 05-25-2004 at 11:08 AM.
    ~Anne Troy

  13. #13
    VBAX Regular
    Joined
    May 2004
    Location
    Adelaide, Australia
    Posts
    28
    Location
    A few observations:

    1. Dennis solution involves hardcoding of the Workbooks>>??

    2. Mike
    "Dim WithEvents m_cbButton As Office.CommandBarButton"
    will fail miserably for users of older versions: ie 97

    3.if the user resets the toolbars after Workbook is opened you will get errors with :
    Private Sub Workbook_Activate() 
        m_cbButton.Visible = True 
    End Sub 
     
    Private Sub Workbook_Deactivate() 
        m_cbButton.Visible = False 
    End Sub
    (I only say as from experience!).

    4. Mike with your code only the creating Workbook can run the code:
    If ThisWorkbook Is ActiveWorkbook Then
    (all subsequent books will fail?)

    5. Personally I would make an Addin for the control and check for it's existence in the Workbook_Activate() / Workbook_Deactivate() events,
    I would place all the code in the addin to do enable, delete etc.

    6. If the user also delete your custom control or move it to another toolbar you can check that with addin code.

    Maybe am being a bit pedantic, please feel free to shoot me down in flames

  14. #14
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Dang! That's the icon I forgot! The machine gun, LOL!!
    ~Anne Troy

  15. #15
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    Insomniac,

    Why should anyone shoot You down? We don't do that on this board
    (Remember that You're not at ...... now )

    Anyway, yes they are hardcoded as I didn?t write a generic solution.
    (Is that a requirement for posting?)

    Feel free to write a generic solutions where the number of workbooks to be checked are changed as well as their names and post it in this thread

    BTW, welcome to the board
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  16. #16
    VBAX Regular
    Joined
    May 2004
    Location
    Adelaide, Australia
    Posts
    28
    Location
    Well no one will ever say your not quick with response Anne

  17. #17
    VBAX Regular
    Joined
    May 2004
    Location
    Adelaide, Australia
    Posts
    28
    Location
    Anyway, yes they are hardcoded as I didn?t write a generic solution.
    (Is that a requirement for posting?)
    Just a friendly observation Dennis,

  18. #18
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Or you, Insomniac!

    ~Anne Troy

  19. #19
    VBAX Mentor XL-Dennis's Avatar
    Joined
    May 2004
    Location
    ?stersund, Sweden
    Posts
    499
    Location
    I always assume that all post are friendly :jack here

    Anyway, how would You go on to write an add-in for it

    (This it the price You pay when You make friendly observations )


    Kind regards,
    Dennis
    Kind regards,
    Dennis

    ExcelKB | .NET & Excel | 2nd edition PED


  20. #20
    VBAX Regular
    Joined
    May 2004
    Location
    Adelaide, Australia
    Posts
    28
    Location
    Ha,ha, are you biting Anne

Posting Permissions

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