Solved: Macro Problem

[bopo]

Hi

Basically i made a toolbar using toolbar wizard, and have used a macro to record it, I have assigned the marco to a button, (although I would like it to appear when the workbook is opened. Anyway the problem

When I click the button, an error message occurs saying invalid arguement or procedure, any ideas what could be causing it? below is the code record using the marco


Sub Toolbar()
'
' Toolbar Macro
' Macro recorded 23/01/2007 by Rob
'

'
Application.CommandBars.Add(Name:="SummaryToolbar").Visible = True
Application.CommandBars("SummaryToolbar").Controls.Add Type:=msoControlButton _
, ID:=928, Before:=1
Application.CommandBars("SummaryToolbar").Controls.Add Type:=msoControlButton _
, ID:=210, Before:=2
Application.CommandBars("SummaryToolbar").Controls.Add Type:=msoControlButton _
, ID:=458, Before:=3
End Sub


help appriciated

[Bob Phillips]

Sub Toolbar()
Application.CommandBars.Add(Name:="SummaryToolbar").Visible = True
With Application.CommandBars("SummaryToolbar")
.Controls.Add Type:=msoControlButton, ID:=928, Before:=1
.Controls.Add Type:=msoControlButton, ID:=210, Before:=2
.Controls.Add Type:=msoControlButton, ID:=458, Before:=3
End With
End Sub

[lucas]

Hi Bopo,
I have submitted this simple floating toolbar to the kb but it sounds like what your looking for. It's pretty well commented:

Add this bit of code To the Thisworkbook module:
Option Explicit
Private Sub Workbook_Open()
AddNewToolBar
End Sub

Add all of the following code To a standard module:
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 = "Macro1"
.Style = msoButtonCaption
.TooltipText = "Run Macro1"
'the onaction line tells the button to run a certain marcro
.OnAction = "Macro1"
End With
' Create a button with an image on the bar and set some
' properties.
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
With ComBarContrl
'the facId line will let you choose an icon
' If you choose to use the faceId then the caption is not displayed
.FaceId = 1000
.Caption = "Icon Button"
.TooltipText = "Run Macro2"
'the onaction line tells the button to run a certain marcro
.OnAction = "Macro2"
End With
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub
Sub Macro1()
MsgBox "You have clicked a button to run Macro1"
End Sub
Sub Macro2()
MsgBox "You have clicked a button to run Macro2"
End Sub



After you add the code to the appropriate modules, save the file and close it when you reopen it your toolbar should show....

[lucas]

Darn Bob, your fast.

[bopo]

Thankyou :), however is there a method of totally earasing it until the specific workbook is opened?

Also thanks Bob, I have used some of that code to make mine more efficient

[lucas]

Huh, it is specific the the workbook with the code only....


oh, you must be talking to Bob...sorry.

[Bob Phillips]

Yes,



Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("SummaryToolbar").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()

On Error Resume Next
Application.CommandBars("SummaryToolbar").Delete
On Error GoTo 0

Application.CommandBars.Add(Name:="SummaryToolbar").Visible = True
With Application.CommandBars("SummaryToolbar")
.Controls.Add Type:=msoControlButton, ID:=928, Before:=1
.Controls.Add Type:=msoControlButton, ID:=210, Before:=2
.Controls.Add Type:=msoControlButton, ID:=458, Before:=3
End With
End Sub

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[bopo]

I recorded a new toolbar marco (as I wanted to add more tools etc) and is has broken again, I have looked a xld's code, and applied what he did to my previous macro, anyway heres the code

edit: figured it out :) , however, anyone know the marco code to place my floating toolbar in the toolbar bit therefore making it static.

[Bob Phillips]

Application.CommandBars.Add(Name:="SummaryToolbar", Position:=msoBarTop).Visible = True