Source Excel Template Pops Up when using a custom toolbar

[Murray]

-I opened a new spreadsheet
-I created two macros and set them to all workbooks
---Sub ConvertToUpperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
End Sub

Sub ConvertToLowerCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = LCase(Rng.Value)
End If
Next Rng
End Sub
-I created a custom Toolbar and added two custom buttons, one for the
uppercase, one for the lowercase macro
-I attached the toolbar to the workbook using Tools/Customize/Toolbars/Attach
-I saved the workbook as a template, changecase.xlt, in
C:\Documents and Settings\me\Application Data\Microsoft\Templates
-I opened a new workbook and the custom toolbar was there
-I typed in some text and selected it and clicked the change to upper case
button
-the case changed, but the file changecase.xlt opened

What have I done incorrectly? I, of course, do no want the template to open.

 

[Gord Dibben]

Murray

How did you open a new workbook?

The workbook you get with File>New>Blank Workbook is not based on the
changecase Template.

Try opening a new workbook through File>New...Templates>On my Computer.

Browse to your changecase template and open it.

It will open as changecase1.xls and you macros will be available.

I would save the workbook as an add-in and load it through Tools>Add-ins.

Any macros you place in that add-in will be available for any open workbook.


Gord Dibben Excel MVP

 

[Murray]

Thank you Gord,

Your reply was spot on. I was not opening the Template correctly. I followed
your suggestion and saved the .xls file as an add-in. We use group policy to
redirect user's documents and settings to their H drive. With the add-in
solution, I can simply send instructions to users on how to add this custom
toolbar as an add-in. The .xla file is copied to:

C:\Documents and Settings\%username%\Application Data\Microsoft which of
course is the user's H drive, so they have write permission.


Thanks again,

Murray

 

[Gord Dibben]

Thanks for the feedback Murray

Glad you got it sorted.

If you wanted to get really fancy you could create the Toolbar on the fly when
the Add-in loads.

Then no need for attaching the Toolbar.



Gord