Source Excel Template Pops Up when using a custom toolbar

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

-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.
 
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
 
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
 
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
 
Back
Top