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. 
0
KenG (52)
11/17/2005 3:23:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
397 Views

Similar Articles

[PageSpeed] 24

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. 


0
normanjones (1047)
11/17/2005 4:41:24 AM
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. 
> 
> 
> 
0
KenG (52)
11/17/2005 5:28:07 AM
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.
>>
>>
>> 


0
normanjones (1047)
11/17/2005 5:35:48 AM
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.
> >>
> >>
> >> 
> 
> 
> 
0
KenG (52)
11/17/2005 5:59:07 AM
Reply:

Similar Artilces:

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

Excel, how do I change the column headings from letters to number
I have a spreadsheet that has numbered columns as opposed to the standard letters. How can I change this back to letters? Go to the Tools menu, choose Options, then the General tab. There, uncheck the R1C1 reference style setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "lazybee" <lazybee@discussions.microsoft.com> wrote in message news:030962A3-A111-4780-93C0-1D28003F1F20@microsoft.com... >I have a spreadsheet that has numbered columns as opposed to the >standard > letters. How can I change this ...

3.0 Customization
Is it possible in 3.0 to have one set of screens appear for one group of users and another set for another group. For instance, could our service people only see the service screens while our sales people only see the sales screens? I know I can restrict access to different areas, but we want to have a totally different look and feel for each group... Sorry - I dont believe this can be done "Matt Harvey" <rifleman@gmail.com> wrote in message news:OR2vU$3GGHA.740@TK2MSFTNGP12.phx.gbl... > Is it possible in 3.0 to have one set of screens appear for one group of >...

too many "inherited" custome headers and footers
It seems that somehow I have "inherited" custom headers and footers from someone else's workbook. They have filled up almost all the allocated spaces in the drop-down selection list and pre-empt me from adding more than two custom headers/footers of my own. I need to add at least four new custom headers to the list. Is there some way to delete all the "inherited" custom headers and footers? Thanks, JR ...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

"x" button enables deletion.... can we put a focus trigger there?
Hi, on three forms, I put focus triggers on the "delete" button because I need to validate some additional data before allowing the user to delete. However, if the user clicks on the corner "x" button to close the form and gets the dialogue box saying they have changes, do they want to "Save", "Delete" or "Cancel", they can delete the record anyways. Is there any way to put a focus trigger on the button of the dialogue box, or on the "x" button? Thanks in advance. I assume you are working with Dexterity. To work with an add...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Formatting #13
Hi How can i have codes in this form 00.00.0000.00, & i wanted to sum to the values below like next code, 00.00.0000(+1).00 I'm tired to format but always sum in the last 2 digits 00.00.0000.00(+1), what can i do Someone can help me Thanks How did you put 00.00.0000.00 in the cell? Did you type 0 and then give it a custom format? If yes, try changing your custom format to: 00\.00\.\0000.00 Then add 1, but make sure that the resulting cell also has this custom format. This is really a funny formatted number with 2 decimal places now. Carla wrote: > > Hi, > How can i ...

Customizing Excel's Border Defaults
I'm working with Excel 2000 and use Borders frequently. The preset default border is a thin line, yet I prefer and always change it to the thick line. How can I change the Excel default to the thick line rather than the thin line? I know it's a small problem, but it's driving me crazy changing it all the time. In WORD, I know how to easily "add style default to template", but can't figure it out in Excel. Any help is greatly appreciated! ...

Can you create custom activities? MSCRM 3.0
Hi, Is there a way to create a new custom activity instead of customising an existing one? I have created a custom entity called 'Chat' utilising an IFRAME. All works well but this entity really should be an activity considering it's properties. In fact I've just been introduced to MS-CRM 3.0 and don't really understand what the difference is between an entity and activity. Would anyone shed the light for me? BTW, I think 3.0 looks great. Gotta admit it's improved. Cheers. Ty In my experience, you cannot create custom activities. In fact, I have been dire...

conditional formatting in excel #3
how do you add a phrase to a field if the filed is blankl, also, can you have a notifiction sent to you when a date on a spreadsheet has expired? > how do you add a phrase to a field if the filed is blankl, What "phrase" do you mean? A Comment? A value? also, can you have > a notifiction sent to you when a date on a spreadsheet has expired? Maybe you can apply an open event (date to be tested being say in F1): Private Sub Workbook_Open() If Range("F1") < Date Then MsgBox "Date expired" End If End Sub Regards, Stefi ...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

Next button problems
I have a next button in a form that I have criteria for. I have a date field that has to be entered before going to another record. When I click the next button I get the pop up that says click update to continue but the form displays the next record instead of staying on the current record that needs updating. Here is the code that I am using. ____________________________________________________________________________________ Private Sub Command39_Click() If IsNull([Post Called Customer]) Then MsgBox "You must click the UPDATE button to continue!!!" Cancel = True End If On Erro...

Conitional Formatting
Hello. I have two fields in a subform, "User" and "IT Announcement" I would like to do conditional formatting to this effect: On ther "User" field: If "IT Announcement" = Yes then make the "User" field turn blue (I would choose the color blue from the conditional formatting selection. How would you write this? Thanks. Iram/mcp On Tue, 23 Oct 2007 14:59:01 -0700, Iram wrote: > Hello. > I have two fields in a subform, "User" and "IT Announcement" > I would like to do conditional formatting to this effect: > On...

Excel number formatting #2
I receive spreadsheets with separate columns of numbers and text. The problem is that the numbers column is not in number or general format (when sorting behaves like text). Is there a way to turn those columns into numbers (except stepping into each one separately)? When I just highlight the number in the cell and hit enter, the cell automatically becomes numeric (I'm looking for a more global solution). Thanks, A You can do this: 1. Type 1 (the number 1) into a blank cell. Highlight this, select Edit, Copy. Now highlight entire column(s) that you want changed to numeric, and sel...

Cannot Email from Outlook 2003..no "send" button present
I cannot send letters or attachments of any kind..I tried reinstalling Outlook 2003 didn't solve the problem..Help You have to close your new message and then the send/receive button appears on your inbox screen. Click that and then it will send. ...

Object error when trying to print custom reports
I am getting an [object error] message when trying to print custom reports or modified versions of canned reports. They display just fine within CRM, we just can't print them. I have already tried deleting the ActiveX to force a reload and manually reinstalling it using http://crmserver/viewer/activexviewer/en/npviewer.exe with no luck. This is occuring on systems regardless of whether Crystal Designer is loaded. Any ideas? Open the reports in Crystal that you are having problems with and make sure that the "Save data with reports" option is not checked. (In Crystal...

Strange behaviour: show/hide formatting symbols reveals old change
In Word 2007, I'm getting some strange behaviour in a document that was authored by someone else. Track Changes is switched off, all changes have been accepted, and everything looks as it should in whichever view I happen to choose (Print Layout, Draft, whatever). But when I click to show formatting symbols (in whatever view) a whole lot of old changes - deletions AND insertions, ostensibly all accepted, and from before the document got to me - appear in the document, making it quite tricky to work with. These old changes are impervious to anything I try to do with them E...

formatting of charts changes when copying from excel 2000 to 200.
When I copy a chart from Excel 2000 and paste it into Excel 2003, some of the formatting is lost. In particular, scale and axis formatting. Is this a programming issue or can it be corrected easily. Thanks Hi, First one would answer why would you copy charts from 2000 to 2003, why not make them in 2003? Second and more important - how are you copying them - there are maybe 20 possible methods of copying a chart from one program to another. Please tell us exactly which steps you use to do the copying. Also, exactly what formatting are you loosing, what do you get instead? When ...

Excel chart linked in Word gets formatted when updateing.
Hi, I have a bit of an annoying issue with excel and word 2007, I have a Chart in excel, and the text on the horizontal axis is rotated 270 degrees,This Chart is linked in a Word document and when i do an update of the linked chart the text gets all messed up. Both of the files are saved in 97-2003 format. ...

DataSet & XML format
Hello, gurus I have a problem about format XML I use ms data access building block to get a data set from stored procedure. the dataset's save xml give me data with the elements. Is there any better way to format what I want ,says some attributes & some elements ?? Is it easy ?? or I must using string.format each datarow in the dataset to construct the xml or via xslt to transform ?? any idea ?? Regards, .Net Newbie *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! hi Query ur database to retrieve onl...

Paying customers commissions
The next release should provide the ability to pay customers a commission without having to create a vendor card for them in payables management. My company currently has thousands of customers that we pay a commission to and it gets quite frustrating considering all the redundant data we have to store since our customers have to be setup as vendors also. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the b...

Format Cells #2
Two questions for you all.. 1) How do I format a cell if the value is over or equal to 0.001 as 00Kg 001g? 2) if the value of the cell is zero how do i write "nil" in the text? I have looked at format cells, but can not work out how to do this. Thanks Andy I'm not sure you can do it within the cell, but in an adjacent cell try this =IF(A1=0,"nil",INT(A1)&"Kg "&(A1-INT(A1))&"g") assuming A1 holds your raw data. Ian "Andy" <anonymous@discussions.microsoft.com> wrote in message news:278e01c5095f$7b2d8110$a50128...

Copy/Past Conditional Format
Good morning board ... :) Presently, I have approx 15 rows by 100 columns loaded with Conditional formatting ... working ok so far. Now I need to extend this same pattern down ... maybe skipping a couple of rows between each pattern. Issue ... Conditional format formulas are not changing when I do copy/paste due to $ locking the cell positions. And if I take the $ out ... then Conditional format appears to fail where they already exist. I am certain this can't be unique, but I do not know the fix or work around ... Therefore, I am coming to the many Magicians of this board ... ...

conditional formatting #5
Trying to highlight lowest cell in columns...if 2 cells in same column contain same low number I dont want any highlighted.Not sure how to set up formatting statement in conditional formatting. Any help out there? File example is attache Attachment filename: skins.xls Download attachment: http://www.excelforum.com/attachment.php?postid=51726 -- Message posted from http://www.ExcelForum.com Hi if your values are in A1:A10 try the following: - select cells A1:A10 - goto the conditional format dialog and enter the following formula =(A1=MIN($A$1:$A$10))*(COU...