style button

[BorisS]

I need a button installed on my standard toolbar, which will have behind it a
custom format/style (just as comma/percent/dollar do). Any help?

 

[Dave Peterson]

Lots of people have a workbook named personal.xls that's stored in their XLStart
folder.

This file is usually hidden from you when you're working in excel, but all of
the macros stored in that workbook's project will be available to you.

So you could create a macro that would apply the custom numberformat you want
and save it in your personal.xls workbook.

Then you could modify the toolbar
Tools|Customize|commands tab|Macros category
Drag the custom button icon to the standard toolbar where you want it.

As long as that tools|customize dialog is visible, you can change stuff
associated with that new icon--including the macro that it calls--and the face
of the icon.

Change the face (and Name) to what you want.
And assign the macro in your personal.xls workbook to this icon.

This may be what your macro could look like:

Option Explicit
Sub SpecialFormat()
On Error Resume Next
Selection.NumberFormat = "$#,##0.00"
If Err.Number <> 0 Then
Beep
Err.Clear
End If

 

[BorisS]

Dave, thanks much. Any chance you could briefly translate the macro into
what it's accomplishing? Sort of the 'teach the man to fish' syndrome in me,
who wants to know what the steps are, for future use.

Appreciate the guidance on the general steps to take.

 

[Dave Peterson]

Option Explicit
Sub SpecialFormat()
On Error Resume Next
Selection.NumberFormat = "$#,##0.00"
If Err.Number <> 0 Then
Beep
Err.Clear
End If
On Error GoTo 0
End Sub

It's not really doing too much. The line that tries to do all the work is:
Selection.NumberFormat = "$#,##0.00"

That's just changing the number format.

The other stuff is just to make it so that if something goes wrong, you don't
get any messages. If the worksheet is protected, then you may not be able to
change the format. So instead of having the macro blow up, it just beeps at
you.

Dave, thanks much. Any chance you could briefly translate the macro into
what it's accomplishing? Sort of the 'teach the man to fish' syndrome in me,
who wants to know what the steps are, for future use.

Appreciate the guidance on the general steps to take.