Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New Menu Items (XP)

    I have a spreadsheet in which I have added two new menu items to the toolbar. I have assigned macros to them: one to print and the other to open a userform. All works well until I save and exit. When I reopen and try to use the new menu items, an error message appears stating that two instances of the workbook are open and I can proceed no further. To cure this, I have to reassign the macros and all appears ok until I exit and reopen when the same thing happens again.

    Any suggestions?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: New Menu Items (XP)

    If you need the toolbar buttons only in that spreadsheet, you should put them on a custom toolbar, attach the toolbar to the workbook and delete it each time the workbook is closed. See Pieterse's <!post=Distributing an Excel application with toolbars (5/95/97/2000/2002),200526>Distributing an Excel application with toolbars (5/95/97/2000/2002)<!/post>.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Menu Items (XP)

    You should know, that the menuitems are not added to a workbook but added to a file called Excel.xlb (or similar, name depends on version). The path to the file that contains the macro that gets fired when you click one of the menubuttons is hardcoded into that file. When you have the right file open and save-as to a new name, the link of the buttons is changed too.

    What I suspect has happened i this:
    - you had your file open (let me call that File0).
    - you saved-as to a new location but with the same name (let us call that File1)
    - you closed Excel.
    - you opened Excel and loaded File0
    - you clicked a menubutton, but that button points to File1 and so Excel tries to open that file.
    Since there is already a file open by that name, excel gives you the error message you saw.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Menu Items (XP)

    Or use code to add the buttons at opening and remove them at closure of the workbook.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Menu Items (XP)

    Thanks, Jan Karel. You divined my actions accurately. Thanks too, Hans for the link. I now know what to do (perhaps).
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Menu Items (XP)

    Would you be so kind as to provide an example of such code?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Menu Items (XP)

    Code to remove a toolbar when a workbook closes:
    (put it in the ThisWorkbook object )

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next 'In case Toolbar is absent
    Application.CommandBars("your toolbar name").Delete
    End Sub</pre>


    As for adding a toolbar, I've not tried it but I'd guess you want something like:
    <pre>Application.CommandBars("your toolbar name").Add</pre>

    in the Workbook_Open event

    stuck

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

    Re: New Menu Items (XP)

    The instruction Application.CommandBars("your toolbar name").Add won't work. To create a new toolbar, use

    Application.CommandBars.Add "your toolbar name"

    but if you attach the toolbar to the workbook, that is not necessary.

  9. #9
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Menu Items (XP)

    Thank you for setting the record straight, I should no better than stray off the edge of my map.

    stuck

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

    Re: New Menu Items (XP)

    "no better"? Watch out for John Gray!

  11. #11
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Menu Items (XP)

    oops! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    stuck
    PS I think I'll be OK, can't recall ever seeing John on this board

  12. #12
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Menu Items (XP)

    Thanks all for your input but my single, active brain cell appears particularly unreceptive to the advice given. I have tried all suggestions but I am still lacking a solution.

    Here's what I am trying to do. On opening the workbook:

    1# Remove normal toolbar

    2#Display, in its place, 2 buttons - Print Sheet and Main Menu - Print Sheet runs a procedure "PrintSheet" and Main Menu runs a procedure "GoToMenu".

    3# on exiting workbook, after saving changes, delete the buttons and reestablish the normal toolbar.

    I have trawled through VBA help and Jan Karel's post but I cannot get this to work.
    Any further help would be much appreciated.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

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

    Re: New Menu Items (XP)

    I have attached a different approach. The custom toolbar has been created manually; it has been attached to the workbook using the Customize dialog.

    Open the ThisWorkbook module to see the code that handles the toolbar. The Workbook_Open event positions the toolbar, the Workbook_Activate event shows the custom toolbar and hides the standard one; the Workbook_Deactivate event hides the custom toolbar and shows the standard one, and the Workbook_BeforeClose event deletes the custom toolbar.

  14. #14
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Menu Items (XP)

    Magnifique! Thanks, John, it does everything I want and so easy to modify. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: New Menu Items (XP)

    Here's some code that I cut and pasted from old code, that does some of what you want in hiding standard commandbars and creating a custom toolbar on the fly, and should give you a feel for the techniques. Since it's cut-&-paste, it may not run as posted. The "OnAction lines are calls to other routines.

    Sub custToolBar()
    Dim tempFETB As CommandBar
    Application.CommandBars(2).Enabled = False
    For Each tempFETB In CommandBars
    If tempFETB.Name = "TbMyToolBar" Then _
    Application.CommandBars("TbMyToolBar").Delete
    Next tempFETB
    Set tempFETB = Application.CommandBars.Add _
    ("TbMyToolBar", msoBarTop, False, True)
    tempFETB.Protection = msoBarNoCustomize + msoBarNoMove
    tempFETB.Controls.Add Type:=msoControlButton, Id:=3
    tempFETB.Controls.Add Type:=msoControlButton, Id:=4
    With tempFETB.Controls.Add(1)
    .Style = msoButtonIconAndCaption
    .BeginGroup = True
    .FaceId = 41
    .OnAction = "DoThis"
    .Caption = " DoThis "
    .TooltipText = " DoThis "
    .Enabled = True
    End With
    With tempFETB.Controls.Add(1)
    .Style = msoButtonIconAndCaption
    .BeginGroup = True
    .FaceId = 39
    .OnAction = " DoThat "
    .Caption = " DoThat "
    .TooltipText = " DoThat "
    .Enabled = True
    End With
    With tempFETB.Controls.Add(1, 2188)
    .Style = msoButtonIconAndCaption
    .BeginGroup = True
    .Caption = "E-Mail me"
    .TooltipText = "E-Mail me "
    .OnAction = "EMailButton_Click"
    .Enabled = True
    End With
    tempFETB.Visible = True
    End Sub

    Sub KillCustomToolBar()
    Application.CommandBars(2).Enabled = True
    Application.CommandBars("TbMyToolBar").Delete
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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