Jack Feeman
09-01-2005, 12:47 PM
HI,
I designed an Excel Form which included a custom toolbar which I created from the Excel UI (Tools>Customize>New). I recorded macros to accomplish some specific tasks and saved them to the Workbook. I then used the UI to assign a task to each of the buttons I created for the custom toolbar. Problem: everytime, I e-mailed or distributed the file, the toolbar buttons could not find the macros since the referenced location of the macros had shifted to the new location of the file. However, if I physically went to that machine and reassigned the macros the buttons would work fine. Yet if they sent to to someone the same problem would appear.
After months of trying everything under the sun, I tried some suggestions and they were created in the VB Editor. Now I get the toolbar, but it is not detachable and I cannot find how to make the custom toolbar detachable. Here is the code used to create the toolbar:
Sub addToolbar()
Dim oCBMenuBar As CommandBar
Dim oCBCLeave As CommandBarControl
Dim iMenu As Integer
Dim i As Integer
On Error Resume Next
Application.CommandBars("PTB").Delete
Set oCBMenuBar = Application.CommandBars.Add(Name:="PTB")
With oCBMenuBar
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = " Add DMA "
.Style = msoButtonCaption
.TooltipText = "Add another DMA/NTA to the Proposal"
.OnAction = "AddDMA"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = " Print Proposal "
.Style = msoButtonCaption
.TooltipText = "Always use this button to print the proposal"
.OnAction = "Autoprint"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = " New Proposal "
.Style = msoButtonCaption
.TooltipText = "Create another Proposal"
.OnAction = "NewProposal"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = " Print Instructions "
.Style = msoButtonCaption
.TooltipText = "Print the instructions for using this tool"
.OnAction = "PrintInstructions"
End With
.Position = msoBarTop
.Protection = msoBarNoMove
.Visible = True
End With
End Sub
Sub deleteToolbar()
On Error Resume Next
Application.CommandBars("PTB").Delete
End Sub
Any help would be much appreciated.
Jack
I designed an Excel Form which included a custom toolbar which I created from the Excel UI (Tools>Customize>New). I recorded macros to accomplish some specific tasks and saved them to the Workbook. I then used the UI to assign a task to each of the buttons I created for the custom toolbar. Problem: everytime, I e-mailed or distributed the file, the toolbar buttons could not find the macros since the referenced location of the macros had shifted to the new location of the file. However, if I physically went to that machine and reassigned the macros the buttons would work fine. Yet if they sent to to someone the same problem would appear.
After months of trying everything under the sun, I tried some suggestions and they were created in the VB Editor. Now I get the toolbar, but it is not detachable and I cannot find how to make the custom toolbar detachable. Here is the code used to create the toolbar:
Sub addToolbar()
Dim oCBMenuBar As CommandBar
Dim oCBCLeave As CommandBarControl
Dim iMenu As Integer
Dim i As Integer
On Error Resume Next
Application.CommandBars("PTB").Delete
Set oCBMenuBar = Application.CommandBars.Add(Name:="PTB")
With oCBMenuBar
With .Controls.Add(Type:=msoControlButton)
.BeginGroup = True
.Caption = " Add DMA "
.Style = msoButtonCaption
.TooltipText = "Add another DMA/NTA to the Proposal"
.OnAction = "AddDMA"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = " Print Proposal "
.Style = msoButtonCaption
.TooltipText = "Always use this button to print the proposal"
.OnAction = "Autoprint"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = " New Proposal "
.Style = msoButtonCaption
.TooltipText = "Create another Proposal"
.OnAction = "NewProposal"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = " Print Instructions "
.Style = msoButtonCaption
.TooltipText = "Print the instructions for using this tool"
.OnAction = "PrintInstructions"
End With
.Position = msoBarTop
.Protection = msoBarNoMove
.Visible = True
End With
End Sub
Sub deleteToolbar()
On Error Resume Next
Application.CommandBars("PTB").Delete
End Sub
Any help would be much appreciated.
Jack