Custom number format button

I'd like to create a toolbar button linked to a custom number format so that
clicking on the button would re-format the active cell or range to my custom
format.

Hi Ken,

Alt-F11 to enter the VBE
Insert | Module
Paste the following code:

'===========>>
Public Sub aTester()
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
End Sub
'<<===========

Alt=F11 to return to Excel

View | Toolbars | Customize | Select 'Commands' tab
Select 'Macros' in the left-hand Categories window
Drag the smiley icon ftom the commands window to your toolbar
Right-click the smiley | Assign Macro
Enter (or select) the name of your macro
OK | Close

Change the macro's number format to your required format.

Change the macro name to suit.

---
Regards,
Norman



"Ken G." <KenG@discussions.microsoft.com> wrote in message
news:D524CFE5-5DA3-4602-8476-E1A536E5259F@microsoft.com...
> I'd like to create a toolbar button linked to a custom number format so
> that
> clicking on the button would re-format the active cell or range to my
> custom
> format.

Thanks Norman - exactly what I wanted. Can the smiley button be edited or
replaced?

"Norman Jones" wrote:

> Hi Ken,
>
> Alt-F11 to enter the VBE
> Insert | Module
> Paste the following code:
>
> '===========>>
> Public Sub aTester()
> Selection.NumberFormat = "#,##0_);[Red](#,##0)"
> End Sub
> '<<===========
>
> Alt=F11 to return to Excel
>
> View | Toolbars | Customize | Select 'Commands' tab
> Select 'Macros' in the left-hand Categories window
> Drag the smiley icon ftom the commands window to your toolbar
> Right-click the smiley | Assign Macro
> Enter (or select) the name of your macro
> OK | Close
>
> Change the macro's number format to your required format.
>
> Change the macro name to suit.
>
> ---
> Regards,
> Norman
>
>
>
> "Ken G." <KenG@discussions.microsoft.com> wrote in message
> news:D524CFE5-5DA3-4602-8476-E1A536E5259F@microsoft.com...
> > I'd like to create a toolbar button linked to a custom number format so
> > that
> > clicking on the button would re-format the active cell or range to my
> > custom
> > format.
>
>
>

Hi Ken,

> Can the smiley button be edited or > replaced?

Right-click the smiley | Customize | Right-click the smiley |
'Change button image' | Select desired icon | Close

---
Regards,
Norman



"Ken G." <KenG@discussions.microsoft.com> wrote in message
news:7A28DE52-4D8A-4B01-9DEB-7401A4542F18@microsoft.com...
> Thanks Norman - exactly what I wanted. Can the smiley button be edited or
> replaced?
>
> "Norman Jones" wrote:
>
>> Hi Ken,
>>
>> Alt-F11 to enter the VBE
>> Insert | Module
>> Paste the following code:
>>
>> '===========>>
>> Public Sub aTester()
>> Selection.NumberFormat = "#,##0_);[Red](#,##0)"
>> End Sub
>> '<<===========
>>
>> Alt=F11 to return to Excel
>>
>> View | Toolbars | Customize | Select 'Commands' tab
>> Select 'Macros' in the left-hand Categories window
>> Drag the smiley icon ftom the commands window to your toolbar
>> Right-click the smiley | Assign Macro
>> Enter (or select) the name of your macro
>> OK | Close
>>
>> Change the macro's number format to your required format.
>>
>> Change the macro name to suit.
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Ken G." <KenG@discussions.microsoft.com> wrote in message
>> news:D524CFE5-5DA3-4602-8476-E1A536E5259F@microsoft.com...
>> > I'd like to create a toolbar button linked to a custom number format so
>> > that
>> > clicking on the button would re-format the active cell or range to my
>> > custom
>> > format.
>>
>>
>>

Thanks again. I now remember doing this years ago but havn't had the need to
do it since.

"Norman Jones" wrote:

> Hi Ken,
>
> > Can the smiley button be edited or > replaced?
>
> Right-click the smiley | Customize | Right-click the smiley |
> 'Change button image' | Select desired icon | Close
>
> ---
> Regards,
> Norman
>
>
>
> "Ken G." <KenG@discussions.microsoft.com> wrote in message
> news:7A28DE52-4D8A-4B01-9DEB-7401A4542F18@microsoft.com...
> > Thanks Norman - exactly what I wanted. Can the smiley button be edited or
> > replaced?
> >
> > "Norman Jones" wrote:
> >
> >> Hi Ken,
> >>
> >> Alt-F11 to enter the VBE
> >> Insert | Module
> >> Paste the following code:
> >>
> >> '===========>>
> >> Public Sub aTester()
> >> Selection.NumberFormat = "#,##0_);[Red](#,##0)"
> >> End Sub
> >> '<<===========
> >>
> >> Alt=F11 to return to Excel
> >>
> >> View | Toolbars | Customize | Select 'Commands' tab
> >> Select 'Macros' in the left-hand Categories window
> >> Drag the smiley icon ftom the commands window to your toolbar
> >> Right-click the smiley | Assign Macro
> >> Enter (or select) the name of your macro
> >> OK | Close
> >>
> >> Change the macro's number format to your required format.
> >>
> >> Change the macro name to suit.
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "Ken G." <KenG@discussions.microsoft.com> wrote in message
> >> news:D524CFE5-5DA3-4602-8476-E1A536E5259F@microsoft.com...
> >> > I'd like to create a toolbar button linked to a custom number format so
> >> > that
> >> > clicking on the button would re-format the active cell or range to my
> >> > custom
> >> > format.
> >>
> >>
> >>
>
>
>