Re: toolbar macro on template

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

On Mon, 20 Apr 2009 12:59:01 -0700, bryan <bryan@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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" wrote:

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

On Mon, 20 Apr 2009 08:23:03 -0700, bryan <bryan@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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" wrote:

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

Option Explicit

Sub Auto_Open()
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


On Tue, 14 Apr 2009 13:26:21 -0700, Gord Dibben <gorddibbATshawDOTca> wrote:

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


Gord

On Tue, 14 Apr 2009 11:01:01 -0700, bryan <bryan@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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" wrote:

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


Gord

On Tue, 7 Apr 2009 11:52:11 -0700, bryan <bryan@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

So where does my macro 'SaveIR' go? Would it be AAA?
The SaveIR macro saves the work*** 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" wrote:

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

On Sun, 5 Apr 2009 11:05:01 -0700, bryan <bryan@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

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 spread*** 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









.

Quantcast