toolbar macro on template

[bryan]

I have successfully created a toolbar macro which savesas xls to network drive.
template is called "Reinsurance WS.xlt". The macro created is called SaveIR.
I created a toolbar button and assigned this macro to it.
One problem:
When I opened a blank spreadsheet for a different purpose, this macro
appeared on the toolbar.
How do I tie the macro, a toobar button, to the Reinsurance template only?
I do not want it on any other excel.

Thanks in advance,
Bryan

 

[Gord Dibben]

Toolbar and menu customizations are saved in your Excelxx.xlb file and show
up whenever Excel is opened.

Best practice is to not add/customize to the default but to create the
Toolbar just when the workbook is opened.

Then delete it when that workbook is closed.

See Debra Dalgleish's site for instructions by Dave Peterson.

http://www.contextures.on.ca/xlToolbar02.html

I think in your case, ignore the part about saving as an add-in and just
save the code in the one workbook in question.


Gord Dibben MS Excel MVP

 

[bryan]

So where does my macro 'SaveIR' go? Would it be AAA?
The SaveIR macro saves the worksheet to the network drive to be imported
into an imaging system.

I have done this in Word and have had no issue in creating a toolbar macro
in a template but, Excel is giving me grief!


Thanks,
Bryan

 

[Gord Dibben]

If just the one macro 'SaveIR" would replace 'AAA" in each instance of the
sample code.


Gord

 

[bryan]

Works great.
Is it possible to:
1) Locate the floating toolbar on the actual toolbar?
2) Can one use a button image on this?
I've tried but, does not save. Each 'new' ws shows '1 SaveIR' as the caption.

Thanks,
Bryan

 

[Gord Dibben]

You want just one button with assigned macro added to an existing Toolbar
with the button image only?


Gord

 

[Gord Dibben]

Have a try with this. Adds a smiley face button to position 26 on the
Standard Toolbar.

Option Explicit

Sub Autpen()
Application.CommandBars("Standard").Visible = True
Call CreateButton
End Sub

'===========================================
Sub Auto_Close()
Call RemoveButton
End Sub

'===========================================
Sub RemoveButton()
On Error Resume Next
Application.CommandBars("Standard").Controls(26).Delete
On Error GoTo 0
End Sub

'===========================================
Sub CreateButton()

Call RemoveButton 'just in case it got left behind

With Application.CommandBars("Standard")
With .Controls.Add(Type:=msoControlButton, before:=26)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "AAA"
.Style = msoButtonIcon
.FaceId = 2950
.TooltipText = "AAA"
End With
End With
End Sub

'===========================================
Sub AAA()
MsgBox "aaa"
End Sub


Gord

 

[bryan]

I was looking to use the same custom button image I use on my Word templates.
Is that possible?

Thanks,
Bryan

 

[bryan]

I tried this and am getting a 'Subscript out of range' run time error on:
With .Controls.Add(Type:=msoControlButton, before:=26)

HELP!

Thanks,
Bryan

 

[Gord Dibben]

Could be you do not have 26 items on the Standard Toolbar

Mine has so that's the number I used.

Try a lower number in both instances.

before:= 1 will add smiley to left end of toolbar


Gord

 

[bryan]

Back to previous question -
Is there a way to have a customized button to be added rather than a smiley
face?

Thanks,
Bryan

 

[Gord Dibben]

Not sure how to add a customized button but John Walkenbach shows a few
hundred buttons and their FaceID numbers.

http://spreadsheetpage.com/index.php/site/tip/identifying_commandbar_images/


Gord