PDA

View Full Version : Solved: TOOLBAR -> HELP!



pster
05-17-2007, 11:45 AM
Hi,

I have this VBA's code and i have a question.
I want to put 4 buttons in the same TOOLBAR but it doesnt seem to work, so i have this code to create 4 different TOOLBAR.




Private Sub Workbook_Open()

AddNewToolBar
AddNewToolBar_1
AddNewToolBar_2
AddNewToolBar_3


End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteToolbar
End Sub

Option Explicit
Sub AddNewToolBar()
' This procedure creates a new temporary toolbar.
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
' Create a new floating toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("My Toolbar").Delete
Set ComBar = CommandBars.Add(name:="My Toolbar", Position:= _
msoBarFloating, Temporary:=True)
ComBar.Visible = True
' Create a button with text on the bar and set some properties.
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
With ComBarContrl
.Caption = "ALL TRADES - New Sheets & Volumes"
.Style = msoButtonCaption
.TooltipText = "ALL TRADES - New Sheets & Volumes"
'the onaction line tells the button to run a certain marcro
.OnAction = "x_GERAL_TPR_VOLUME"
End With
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub
Sub AddNewToolBar_1()
' This procedure creates a new temporary toolbar.
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
' Create a new floating toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("My Toolbar_1").Delete
Set ComBar = CommandBars.Add(name:="My Toolbar_1", Position:= _
msoBarFloating, Temporary:=True)
ComBar.Visible = True
' Create a button with text on the bar and set some properties.
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
With ComBarContrl
.Caption = "ALL TRADES - Volumes"
.Style = msoButtonCaption
.TooltipText = "ALL TRADES - Volumes"
'the onaction line tells the button to run a certain marcro
.OnAction = "x_GERAL_VOLUME"
End With

Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub
Sub AddNewToolBar_2()
' This procedure creates a new temporary toolbar.
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
' Create a new floating toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("My Toolbar_2").Delete
Set ComBar = CommandBars.Add(name:="My Toolbar_2", Position:= _
msoBarFloating, Temporary:=True)
ComBar.Visible = True
' Create a button with text on the bar and set some properties.
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
With ComBarContrl
.Caption = "ALCT - New Sheets & Volumes"
.Style = msoButtonCaption
.TooltipText = "ALCT - New Sheets & Volumes"
'the onaction line tells the button to run a certain marcro
.OnAction = "x_ALCT_TPR_VOLUME"
End With

Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub
Sub AddNewToolBar_3()
' This procedure creates a new temporary toolbar.
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
' Create a new floating toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("My Toolbar_3").Delete
Set ComBar = CommandBars.Add(name:="My Toolbar_3", Position:= _
msoBarFloating, Temporary:=True)
ComBar.Visible = True
' Create a button with text on the bar and set some properties.
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
With ComBarContrl
.Caption = "ALCT - Volumes"
.Style = msoButtonCaption
.TooltipText = "ALCT - Volumes"
'the onaction line tells the button to run a certain marcro
.OnAction = "x_ALCT_VOLUME"

End With

Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub
Sub DeleteToolbar()
On Error Resume Next
CommandBars("My Toolbar").Delete
End Sub


Is it possible to put 4 buttons in the same TOOLBAR?

Tkx!

lucas
05-17-2007, 12:30 PM
Try this (http://vbaexpress.com/kb/getarticle.php?kb_id=921)

Bob Phillips
05-17-2007, 01:02 PM
Sub AddNewToolBar()
' This procedure creates a new temporary toolbar.
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
' Create a new floating toolbar and make it visible.
On Error Resume Next
'Delete the toolbar if it already exists
CommandBars("My Toolbar").Delete
On Error GoTo 0
Set ComBar = CommandBars.Add(Name:="My Toolbar", Position:= _
msoBarFloating, Temporary:=True)
ComBar.Visible = True
' Create a button with text on the bar and set some properties.
With ComBar
With .Controls.Add(Type:=msoControlButton)
.Caption = "ALL TRADES - New Sheets & Volumes"
.Style = msoButtonCaption
.TooltipText = "ALL TRADES - New Sheets & Volumes"
'the onaction line tells the button to run a certain marcro
.OnAction = "x_GERAL_TPR_VOLUME"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "ALL TRADES - Volumes"
.Style = msoButtonCaption
.TooltipText = "ALL TRADES - Volumes"
'the onaction line tells the button to run a certain marcro
.OnAction = "x_GERAL_VOLUME"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "ALCT - New Sheets & Volumes"
.Style = msoButtonCaption
.TooltipText = "ALCT - New Sheets & Volumes"
'the onaction line tells the button to run a certain marcro
.OnAction = "x_ALCT_TPR_VOLUME"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "ALCT - Volumes"
.Style = msoButtonCaption
.TooltipText = "ALCT - Volumes"
'the onaction line tells the button to run a certain marcro
.OnAction = "x_ALCT_VOLUME"
End With
End With

Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub
Sub DeleteToolbar()
On Error Resume Next
CommandBars("My Toolbar").Delete
End Sub

pster
05-18-2007, 04:45 AM
Tks xld!

u helped a lot!