Lars,
See the macros below. They go into the ThisWorkbook module.
The beforeclose macro deletes the toolbar. (amend per your needs).
The open macro creates the toolbar.
The OnAction sets the macro to be run by each button.
The face ID # was gotton from a special workbook showing the different
choices and their number.
You position the toolbar with
cbrCommandBar.Position = msoBarTop
check out help for other options.
[all code "stolen" from this ng]
Copy and paste into a workbook and watch it run. (of course you won't have
the associated macros for the buttons to run).
steve
=============================================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars(" Prepare Request ").Delete
End Sub
Private Sub Workbook_Open()
'Private Sub Workbook_Activate()
Sheets(1).Cells(1, 1) = Application.UserName
Dim cbrCommandBar As CommandBar
Dim cbcCommandBarButton As CommandBarButton
Application.DisplayAlerts = False
Application.ScreenUpdating = False
' Application.DisplayFullScreen = True
' If the command bar exits, remove it.
On Error Resume Next
Application.CommandBars(" Prepare Request ").Delete
' Add the command bar to the application's
' CommandBars collection.
Set cbrCommandBar = _
Application.CommandBars.Add
cbrCommandBar.Name = " Prepare Request "
' Add command button control to the control's
' collection of CommandBar objects.
' Prepare Request
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
' Set properties of the command button.
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = " New Request "
.FaceId = 139
.TooltipText = _
"Press me to Prepare Request."
.OnAction = "QuoteCreate"
.Tag = "Quoter"
End With
End With
' Add Reps/Service personnel
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
' Set properties of the command button.
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = " Add Sales/CS "
.FaceId = 2131
.TooltipText = _
"Press me to Add."
.OnAction = "AddRS"
.Tag = "AddRS"
End With
End With
' Delete Reps/Service personnel
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
' Set properties of the command button.
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = " Delete Sales/CS "
.FaceId = 1657
.TooltipText = _
"Press me to Delete."
.OnAction = "DeleteRS"
.Tag = "DeleteRS"
End With
End With
' Amend Quote
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
' Set properties of the command button.
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = " Amend Request "
.FaceId = 593
.TooltipText = _
"Press me to Amend Request."
.OnAction = "QuoteAmend"
.Tag = "Amend"
End With
End With
' Send email
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
' Set properties of the command button.
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = " Send email "
.FaceId = 1981
.TooltipText = _
"Press me to email Quote."
.OnAction = "SendMyMail"
.Tag = "emailquote"
End With
End With
cbrCommandBar.Visible = True
cbrCommandBar.Position = msoBarTop
' For Each sht In ActiveWorkbook.Sheets
' sht.Protect _
' password:="", _
' DrawingObjects:=True, _
' Contents:=True, _
' Scenarios:=True, _
' Userinterfaceonly:=True
' Next sht
Set cbrCommandBar = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Sub CommandBarDelete()
On Error Resume Next
Application.CommandBars(" Prepare Request ").Delete
End Sub
--
sb
Lars Peter Nielsen said:
Hi
I have made a macro which create a toolbar with the following buttons:
Sub toolbar()
'
' toolbar Makro
' Makro indspillet 21-08-2003 af Lars P
'
'
Application.CommandBars.Add(Name:="Zoom").Visible = True
Application.CommandBars("Zoom").Controls.Add Type:=msoControlButton,
ID:= _
2949, Before:=1
Application.CommandBars("Zoom").Controls.Add Type:=msoControlButton,
ID:= _
2949, Before:=2
Application.CommandBars("Zoom").Controls.Add Type:=msoControlButton,
ID:= _
2949, Before:=3
Application.CommandBars("Zoom").Controls.Add Type:=msoControlButton,
ID:= _
2949, Before:=4
Application.CommandBars("Zoom").Controls.Add Type:=msoControlButton,
ID:= _
2949, Before:=5
End Sub
**************
How can I rename these buttons and link a macro to it - all in the same
code?? I want to put it into a autoopen, so I get this toolbar up which is
used for the workbook and then I can also share the workbook with others who
hasnt made this toolbar - of course I will delete it when I close the
workbook (autoclose). Is it also possible to let it the toolbar go up to the
other toolbars (so its not just in the middle of everything)?
Rgds Lars
