Results 1 to 3 of 3
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Toolbar Macro buttons - remove path specificity? (Excel 2002)

    This is making me crazy. Every time I copy my Excel workbook somewhere, open it, and click a custom toolbar button that runs a macro in a module in that workbook, it wants to open the workbook from a previous path. This is the code in ThisWorkbook that shows (or creates, if needed) and hides the toolbar:

    <pre>Private Sub Workbook_Open()
    Dim cBar As CommandBar, cbCtrl As CommandBarButton
    On Error Resume Next
    Set cBar = Application.CommandBars("QVTool")
    On Error GoTo 0
    If cBar Is Nothing Then
    Set cBar = Application.CommandBars.Add("QVTool")
    With cBar
    Set cbCtrl = .Controls.Add(Type:=msoControlButton)
    With cbCtrl
    .Caption = "Import QuickView Data"
    .OnAction = "ImportQVInfo"
    .Style = msoButtonCaption
    End With
    Set cbCtrl = .Controls.Add(Type:=msoControlButton)
    With cbCtrl
    .Caption = "JURIS Lookup"
    .OnAction = "ValidateClientMatters"
    .Style = msoButtonCaption
    End With
    Set cbCtrl = .Controls.Add(Type:=msoControlButton)
    With cbCtrl
    .Caption = "Insert Subtotals"
    .OnAction = "CreateChargesSubtotal"
    .Style = msoButtonCaption
    End With
    Set cbCtrl = .Controls.Add(Type:=msoControlButton)
    With cbCtrl
    .Caption = "Remove Subtotals"
    .OnAction = "RemoveChargesSubtotal"
    .Style = msoButtonCaption
    End With
    End With
    End If
    cBar.Visible = True
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim cBar As CommandBar
    Set cBar = Application.CommandBars("QVTool")
    If Not (cBar Is Nothing) Then
    cBar.Visible = False
    End If
    End Sub</pre>

    I tried Tools>Customize..., right-click button, Assign Macro..., getting rid of the path by manually deleting it, but <big>it keeps coming back</big>. Will this haunt me forever or is there a solution?

    I guess one solution is to change the above code to delete the old toolbar every time this workbook is opened, rather than hiding it. Is there no easier way?

    Thanks. -Jefferson

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,910
    Thanks
    0
    Thanked 89 Times in 85 Posts

    Re: Toolbar Macro buttons - remove path specificity? (Excel 2002)

    Jefferson,
    I would delete and recreate the toolbar every time. Toolbar customisation is stored in the .xlb file and it (presumably) includes the path. Alternatively, you could manually attach the toolbar to the workbook. I say manually because, last time I tried, there was no way of getting at attached toolbars via VBA. (Ours not to reason why...)
    Hope that helps.
    Regards,
    Rory
    Microsoft MVP - Excel.

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

    Re: Toolbar Macro buttons - remove path specificity? (Excel 2002)

    Jefferson,

    I agree with Rory. If you are going to move and/or rename your Excel workbook regularly, create the toolbar in the Workbook_Open event and delete it in the Workbook_BeforeClose event. Or, if you prefer, delete, then create it in the Workbook_Open event.

Posting Permissions

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