Custom number format button

K

Ken G.

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.
 
N

Norman Jones

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.
 
K

Ken G.

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

Norman Jones said:
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. said:
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.
Click to expand...
Click to expand...
 
N

Norman Jones

Hi Ken,
Can the smiley button be edited or > replaced?
Click to expand...

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

---
Regards,
Norman



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

Norman Jones said:
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. said:
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.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken G.

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

Norman Jones said:
Hi Ken,
Can the smiley button be edited or > replaced?
Click to expand...

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

---
Regards,
Norman



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

Norman Jones said:
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



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.
Click to expand...
Click to expand...
Click to expand...
 
Top