Consulting

Results 1 to 7 of 7

Thread: Advice: Custom Menu Woes

  1. #1
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    2
    Location

    Advice: Custom Menu Woes

    I recently re-vamped/re-designed the cash-counting workbook we use at work. As part of my re-design, I added several macros to make things easier. Everything seemed fine (aside from a few easily corrected glitches) until I moved the macros to a manually-created custom menu on the toolbar after Help.

    Every day, we save our master copy of the workbook as a new workbook based on the date, say 05.29.04. Everything is fine until the next day. When the master is next opened, we enable macros for the master.xls, but when we try to use a macro from the custom menu (either on the master or after the workbook is "saved as" that day's date), the previous day's workbook opens, and we are once again promted to enable macros.

    Once the macros in the menus stopped without opening the previous day's workbook, I checked to see that the macro assigned to the menu item was still correct by going into Customize mode, clicking my way through the menu system, right-clicking a particular menu item, and then clicking "Assign Macro..." The macro assigned was "C:\Windows\Desktop\New Cash Handler Excel\05.29.04.xls"!MacroPrintThisSheet. Because it was May 30, using this macro was forcefully opening the May 29 workbook.

    So I went back to the master, removed its read-only status, changed all the macros back to their original !MacroThisIsMyMacro, and everything was fine until I used the macros in a different workbook. Once again, the macro names were changed to reflect the dated workbook and would force the opening of previous workbooks. Strangely, or perhaps not so strangely, this did not happen with my macros that were assigned to buttons on the worksheet.

    I've tried to create a menu with VBA just to see if that would be successful, and while adding a menu item to the tools menu was, adding a new menu altogether (which I need) was not. Here's the code I used:

    Sub AddNewMenuItem() 
    Dim CmdBar As CommandBar 
    Dim CmdBarMenu As CommandBarControl 
    Dim CmdBarMenuItem As CommandBarControl 
    Set CmdBarMenu = CmdBar.Controls.Add 
    .Caption = "New Menu" 
    End With 
        Set CmdBarMenuItem = CmdBarMenu.Controls.Add 
        .Caption = "New Item" 
        .OnAction = "'" & ThisWorkbook.Name & "'!MyMacro" 
        .Tag = SomeString 
    End With 
    End Sub
    Any ideas? How can I keep Excel from forcefully opening previous days' workbooks? Thanks in advance.

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

    See what the VBA tags do to your code? Cool, huh?
    See: www.vbaexpress.com/vbatags.htm for instructions.

    And, right off the bat...
    I see two "end with"s and no "with"s?
    ~Anne Troy

  3. #3
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hello kreeves,

    This is a native functionality in Excel. If you notice something, your macros should also copy over (if you're doing a SaveAs type command in your macro) all macros, to be readily accessible in your new book. BUT, your buttons/objects are still linked to their previous macros. One way (probably a better way out there, but an option) would be to re-assign the macros every time the book is saved new.

    An example may look like this:

    Sub trnsfrMcrs()
        ActiveSheet.Shapes("Button 1").Select 'Whatever yours is
        Selection.OnAction = "blahblah" 'This is the macro name, copied over
        'from the original sheet
    End Sub
    
    Sub blahblah() 'just an oddball testing macro
        Dim x As String
        Dim y As Integer
        x = "Hello!"
        y = 0
        Do
        MsgBox x
        x = x & " Again!" 'We really want them to get it... ;)
        y = y + 1
        Loop Until y = 3
        MsgBox ":)" 'Just a test :)
    End Sub
    
    Sub saveNew() 'testing the addition of a date
        ActiveWorkbook.SaveAs Filename:="blah " & _
            Format(Date, "mm.dd.yyyy") & ".xls"
    End Sub
    The first macro will change the 'Assigned Macro' to that specific shape. The rest were just for testing purposes.

    Does this help any?

  4. #4
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    There was mention of this thread being cross-posted at MrExcel. Because we are VBA only, and because there was no one working toward a solution over at MrExcel, we don't consider this to be a cross-post.
    ~Anne Troy

  5. #5
    VBAX Regular vonpookie's Avatar
    Joined
    Jun 2004
    Location
    Are we there yet?
    Posts
    74
    Location
    Ok, just to clear some things up:

    This was also posted at MrExcel, and the only response it had was from me.

    I simply suggested he have a look at J-Walk's menu maker ( http://j-walk.com/ss/excel/tips/tip53.htm ).

    This morning, I had a PM from kreeves @ MrExcel stating that he went with the Menu Maker route.

  6. #6
    VBAX Newbie
    Joined
    Jun 2004
    Posts
    2
    Location
    Thank you everyone for your help. I did end up going with the menumaker route simply because it seemed to be the easiest (both for me and anyone who might tackle modifying the workbook after I am gone), mostly because it was largely a copy and paste routine. However, firefytr's technique printed out and filed at work, just in case the menumaker fails me. Again, thank you all for your quick help. I was really stressing out about trying to get the workbook working again since it is used by everyone in the management team, and now, thanks to you all, I can rest easy.

    Cheers,
    Kevin

  7. #7
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Okay. I changed our title here to have "Advice:"

    That way, we know it's resolved, but the answer isn't really right here in the posts.
    Thanks for clarification, Kev!
    ~Anne Troy

Posting Permissions

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