Clear Cell Format

Some time ago, from a post by JE McGimpsey,
I learned the following macro, which will toggle the substitution of  "."
with ":"
However it left the cell in Time Format after its initial entry.
How do I clear the Time Format, when  decimal point is back to normal state.
Please,

    Public Sub ToggleDotTime()
        Dim strmsg As String
        strmsg = "Decimal Point is NORMAL"
        With Application.AutoCorrect
            On Error Resume Next
            .DeleteReplacement (".")
            If Err Then
                .AddReplacement ".", ":"
            Application.ClearFormats = True
                strmsg = Application.Substitute(strmsg, _
                        "is NORMAL", "is substituted by "":""")
            Application.ClearFormats = True
            End If
            On Error GoTo 0
            Application.StatusBar = strmsg
        End With
    End Sub


0
danchen (87)
6/6/2004 2:01:20 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
328 Views

Similar Articles

[PageSpeed] 54

Unfortunately this macro runs on the Excel application and adds/removes an
autocorrect option that does it. Thus, to revert all back to normal, you
would need to scan every completed cell and see if it is time format and
re-set it  nasty!

I think you would be better off with a change event that traps a nominated
range and sets/re-sets, or a macro that does it for the current selection ,
rather than this macro which imposes itself on the whole app.

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"daniel chen" <danchen@worldnet.att.net> wrote in message
news:QVuwc.38953$_k3.942813@bgtnsc05-news.ops.worldnet.att.net...
> Some time ago, from a post by JE McGimpsey,
> I learned the following macro, which will toggle the substitution of  "."
> with ":"
> However it left the cell in Time Format after its initial entry.
> How do I clear the Time Format, when  decimal point is back to normal
state.
> Please,
>
>     Public Sub ToggleDotTime()
>         Dim strmsg As String
>         strmsg = "Decimal Point is NORMAL"
>         With Application.AutoCorrect
>             On Error Resume Next
>             .DeleteReplacement (".")
>             If Err Then
>                 .AddReplacement ".", ":"
>             Application.ClearFormats = True
>                 strmsg = Application.Substitute(strmsg, _
>                         "is NORMAL", "is substituted by "":""")
>             Application.ClearFormats = True
>             End If
>             On Error GoTo 0
>             Application.StatusBar = strmsg
>         End With
>     End Sub
>
>


0
bob.phillips1 (6510)
6/6/2004 9:50:27 AM
Hi Bob
Thank you for your advice. Appreciated.

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:%2312Lzu6SEHA.644@tk2msftngp13.phx.gbl...
> Unfortunately this macro runs on the Excel application and adds/removes an
> autocorrect option that does it. Thus, to revert all back to normal, you
> would need to scan every completed cell and see if it is time format and
> re-set it  nasty!
>
> I think you would be better off with a change event that traps a nominated
> range and sets/re-sets, or a macro that does it for the current selection
,
> rather than this macro which imposes itself on the whole app.
>
> -- 
>
> HTH
>
> Bob Phillips
>     ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mailing direct)
>
> "daniel chen" <danchen@worldnet.att.net> wrote in message
> news:QVuwc.38953$_k3.942813@bgtnsc05-news.ops.worldnet.att.net...
> > Some time ago, from a post by JE McGimpsey,
> > I learned the following macro, which will toggle the substitution of
"."
> > with ":"
> > However it left the cell in Time Format after its initial entry.
> > How do I clear the Time Format, when  decimal point is back to normal
> state.
> > Please,
> >
> >     Public Sub ToggleDotTime()
> >         Dim strmsg As String
> >         strmsg = "Decimal Point is NORMAL"
> >         With Application.AutoCorrect
> >             On Error Resume Next
> >             .DeleteReplacement (".")
> >             If Err Then
> >                 .AddReplacement ".", ":"
> >             Application.ClearFormats = True
> >                 strmsg = Application.Substitute(strmsg, _
> >                         "is NORMAL", "is substituted by "":""")
> >             Application.ClearFormats = True
> >             End If
> >             On Error GoTo 0
> >             Application.StatusBar = strmsg
> >         End With
> >     End Sub
> >
> >
>
>


0
danchen (87)
6/6/2004 6:48:38 PM
Do you need any help with one of the alternatives?

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"daniel chen" <danchen@worldnet.att.net> wrote in message
news:aGJwc.41317$_k3.1018770@bgtnsc05-news.ops.worldnet.att.net...
> Hi Bob
> Thank you for your advice. Appreciated.
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:%2312Lzu6SEHA.644@tk2msftngp13.phx.gbl...
> > Unfortunately this macro runs on the Excel application and adds/removes
an
> > autocorrect option that does it. Thus, to revert all back to normal, you
> > would need to scan every completed cell and see if it is time format and
> > re-set it  nasty!
> >
> > I think you would be better off with a change event that traps a
nominated
> > range and sets/re-sets, or a macro that does it for the current
selection
> ,
> > rather than this macro which imposes itself on the whole app.
> >
> > -- 
> >
> > HTH
> >
> > Bob Phillips
> >     ... looking out across Poole Harbour to the Purbecks
> > (remove nothere from the email address if mailing direct)
> >
> > "daniel chen" <danchen@worldnet.att.net> wrote in message
> > news:QVuwc.38953$_k3.942813@bgtnsc05-news.ops.worldnet.att.net...
> > > Some time ago, from a post by JE McGimpsey,
> > > I learned the following macro, which will toggle the substitution of
> "."
> > > with ":"
> > > However it left the cell in Time Format after its initial entry.
> > > How do I clear the Time Format, when  decimal point is back to normal
> > state.
> > > Please,
> > >
> > >     Public Sub ToggleDotTime()
> > >         Dim strmsg As String
> > >         strmsg = "Decimal Point is NORMAL"
> > >         With Application.AutoCorrect
> > >             On Error Resume Next
> > >             .DeleteReplacement (".")
> > >             If Err Then
> > >                 .AddReplacement ".", ":"
> > >             Application.ClearFormats = True
> > >                 strmsg = Application.Substitute(strmsg, _
> > >                         "is NORMAL", "is substituted by "":""")
> > >             Application.ClearFormats = True
> > >             End If
> > >             On Error GoTo 0
> > >             Application.StatusBar = strmsg
> > >         End With
> > >     End Sub
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
6/6/2004 9:21:00 PM
Reply:

Similar Artilces:

Marked cells more visible
When marking cells in 2007, is it possible to change the color to a more visible one? I see Chip says that the RowLiner download is an upgrade to Hilite. -- Jim "Prixt0n" <leifhy@gmail.com> wrote in message news:ba78be5e-0f2a-4c4a-ba7d-051d5a9d5c5f@v28g2000hsv.googlegroups.com... | When marking cells in 2007, is it possible to change the color to a | more visible one? This is where someone from MS acknowledges the issue: http://blogs.msdn.com/excel/archive/2008/04/22/improving-sheet-selection.aspx -- Jim "Prixt0n" <leifhy@gmail.com> wrote in message n...

Counting a single value in a cell?
I want to count how often a single value (the dash symbol) occurs in a single cell than rank it. Showing two examples, AUS-IAH/PTY-IAH/AUS is in cell A1 and AUS-ORD-LHR/WAW-LHR-ORD/AUS is cell A2. Cell A1 has 2 dashes and A2 has 4 dashes. Is there a way to do this? Thank you. =LEN(B10)-LEN(SUBSTITUTE(B10,"-","")) for data in B10 -- Gary''s Student - gsnu200789 "Greg" wrote: > I want to count how often a single value (the dash symbol) occurs in a > single cell than rank it. Showing two examples, AUS-IAH/PTY-IAH/AUS is in > cell A...

Insert a reference table and have different size cells
I have a weekly job report I fill out where I record what jobs each employee has been working on for that day. I would like to add another table to the worksheet where I would be able to see a list of jobs to perform. Then I could record where some one worked and what he did. The problem is I need to make the cells in the second table smaller. Any one know how I can do this? ...

how to seperate data from 1 cell into multiple cells?
How can I take a cell that contains a name and title and seperate it into 3 cells? e.g. 1 cell with John Smith, M.D. into 3 cells? Thanks Lloyd Select the cell, use Data/Text to Columns, click Delimited, then select Space and Finish. "Lloyd" <info@atlanticvoice> wrote in message news:<8ebd01c3ea12$04e08600$a001280a@phx.gbl>... > How can I take a cell that contains a name and title and > seperate it into 3 cells? > > e.g. 1 cell with John Smith, M.D. into 3 cells? > > Thanks > > Lloyd I gave this to my class... on the menubar, Data: Tex...

Cell entry used in formula
I'm using a common lookup formula like the following: =index(B1:B5,match(E1,A1:A5,false),1) Is it possible to write the above formula to reference entries in cell as cell numbers? For example: If cell J1 contains the text "B3" , can a function b written in place of E1 (in the formula) to reference J1 and use B3 (o whatever cell number in in J1)? Hope this makes sense. Thanks Bobb -- Message posted from http://www.ExcelForum.com I'm guessing you might want something like this: =INDEX(B1:B5,MATCH(INDIRECT(J1),A1:A5,FALSE),1) Your example is confusing because you used ...

Excel Cell Width
How do I adjust the width of a cell WITHOUT adjust the entire column? You can't Regards, Peo Sjoblom "JeffW" wrote: > How do I adjust the width of a cell WITHOUT adjust the entire column? Jeff, the only way would be to use format center across selection, or merge cells to put two or more cells in the row together -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "JeffW" <JeffW@discussions.microsoft.co...

Changing the font style, fore colour and back colour of an individual cell in a VB6 listview
Hi All Again, another I've always left rather than sorting is the ability to style (bold, underline, italic), colour the text and colour the background of the text in a vb6 listview CELL. Are any of these permitations possible? Am I better using an MS Flexgrid and hidden textbox (for the edit bit)? Thank you. "Mojo" <please@dont.spam.com> wrote in message news:e%23LFCQ5RLHA.3792@TK2MSFTNGP06.phx.gbl... > Hi All > > Again, another I've always left rather than sorting is the ability to > style > (bold, underline, italic), colo...

MLA format in bibliography function
MLA formatting changed in 2009, is there a way to update the way bibliographies are formatted without having to manually change each instance? For example, it is no longer recommended that titles are to be underlined; they should be italicized. Also the publication medium should be included and urls are not required, the website name is. There is no patch out yet. If you have a decent XSLT programming knowledge, you could fix the thing yourself as it is 'open source'. Yves -- BibWord : Microsoft Word Citation and Bibliography styles http://bibword.codeplex.com ...

Ghosting Out Cells
I have a spreadsheet which will be replicated and given to a number of different people to fill in different parts. I would like to Ghost out, or make semi-transparent, parts of the spreadsheet (depending on which part the individual needs to fill in). I know you can shade cells....but is there anyway to alter the transparency of the colours/gridlines/fonts.... Any help would be great. (Im using Excel 2000) Cheers, Phil I've figured this out.. If you draw a box over the area you want to make transparent... then format the box and make it semi-transparent. "PW11111" ...

How do I save a Power Point Handout in a PDF format?
I want to save my presentation handout (3 slides per page) as a pdf file. I am able to have WORD convert it and save, however, it makes the slides too small. I am not aware how to allocate 3 slides per page in one single pdf sheet because there is no option for Powerpoint to export the handout as a PDF file. The best thing you can do is when clicking "Save As" you can select the file type as PDF. Hope this info helps. Please let me know how it goes, -- Vicente Tulliano "dr.dawnc" wrote: > I want to save my presentation handout (3 slides ...

Excel Cell Formatting question
I am using MS Excel 2003 SP1. I would like to have negative numbers display as red and in brackets ) Any help would be greatly appreciated. Thanks To -- Tom Bradstree ----------------------------------------------------------------------- Tom Bradstreet's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1578 View this thread: http://www.excelforum.com/showthread.php?threadid=27296 Select the range you want to format and go to the Format menu, selec Cells. Then select the Number tab and select your number of decima places and the format for negative numbers ...

How do I fill down keeping one cell calculation from changing in .
If I have a calculation I want to fill down which has multiple calculations. How do I fill it down keeping one of the calculations constant. Meaning one of the calculations always uses the same cell for a calculation, it doesn't change as the cells go down. eg =SUM(A2*F13-J13) I wand to keep the A2 all the way down but want the others to change as it is filled down. Thanks, WC Look up About cell and range references in the HELP files. And try this: =SUM($A$2*F13-J13) -- HTH, RD ============================================== Please keep all...

How do you turn off pivot table auto format in Excel?
I want to know how to return to the original/default pivot table format after I have already applied an auto format report style. In the list of AutoFormats, select PivotTable Classic, which is at the bottom of the AutoFormat list. srudd wrote: > I want to know how to return to the original/default pivot table format after > I have already applied an auto format report style. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html I have tried this and it does not get me back to the original format. I want to return to the format with the da...

When sending email in html format .pdf attachments can.t be opened
I am a Realtor in Florida and send a lot of emails using Outlook 2007. I want to continue using html format and send Adobe pdf attachments but the recipients tell me they receive the pdf attachments as winmail.dat and can not open the attachment. I do not want to use plain text for outgoing mail because I have photos etc. in my signature and custom stationery. This has been an issue for more than a year now. Has a fix been found for it? I have downloaded all of the MS updates (I think). In that case you are probably sending in Rich Text Format and not in the HTML format....

Imported Mail to Outlook 2003: HTML formatting does not display
I have just importe messages from outlook express which were imported from eudora 6.0. The messages that were i nthe euors "out" folder which is roughtly equal to the "Sent items" folder in outlook. Do not display correctly. Rather than seeing the formated text displayed, you see a plain text message with the html code... How I correct? ...

cell function
Is there a way to have a cell in a spreadsheet be represented as a symbol? example: if i want cell A1 to show a checkmark or an x when the cell is checked, how would i do that (if it can be done)? greatly appreciated Not sure I totally get how you're envisioning it to work. I'm guessing you want to click on the cell at which point it will put an x in the cell then take it off. 2 ways to do it. 1) (I wouldn't do this) use forms, and put a checkbox over the cell. I've found the checkboxes are always tough to manage, expecially if people are inserting rows, resizi...

protect borders on a data entry cell
I have an Excell 2007 spreadsheet with borders around selected cells, the spreadsheet is protected so my formulas cant get corrupted, but with some cells that are unlocked for user data entry. But when you copy and paste in these user entry cells the borders are getting copied and pasted also destroying the look of the spreadsheet. so how do you protect the borders from being changed and still allow copy and paste in the user entry cells? Thanks in advance Terry You could politely ask users to copy>paste VALUES -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin....

Formula Needed for Corresponding Blank Cell
Hi All, Well that didn't work: =IF($B$7:$B$689<>"",IF($W$7:$X$689="","SOMETHING IS MISSING",""),"") And neither did this: =IF($B$7:$B$689<>"",IF(OR($W$7:$W$689=""),OR($X$7:$X$689=""),OR($Y$7:$Y $689="")),"SOMETHING IS MISSING",""),"") No laughing. I'm trying. And I did use Ctrl-Shift-Enter. It's supposed to catch blank cells in columns W, X, or Y if column B in the same row isn't blank. Column B is a date and W, X and Y are ...

Nothing happens when I click on file, view, format etc.
Working away on a newsletter, putting a border around text box when -- publisher announcements appear. Something like back to front or front to back and control M6? The upshot is that I can't do anything else to the newsletter (except look at what I've completed so far). When I try to type -- the arrange box appears. However, it doesn't click through when I click on it. Hoping someone will comprehend what I'm saying -- that is, nothing clicks through when I click on it. I click on "edit," Nothing. I click on "format," "tools," "help" -- ...

cell +1 with one button?
HI! I need to know how to add '1' to a cell using only one button. Meaning value WAS 3 and becomes 4. Is there a way to do this? It is for live stats in a sports game, s� speed is of the essense. That is why it has to be a "1-button operation." Thanks in advance! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Attach this macro to the button: Public Sub Add1() With ActiveCell If IsEmpty(.Value) Or IsNumeric(.Value) Then _...

pasting in merged cells #2
I have created a form in excel 2003 where I have a column for names and there are six rows merged in each block that contains the name due to space for data in other parts of the form. I am wanting to paste a list of names in the name column containing the merged cells so that there will be one name per merged cell in the column. How can that be done? I think merged cells give more problems than they are worth. When a cell is merged there is only 1 cell. In your case I would not merge the cells but format them with a borde around them and cell colour to look like they are merged -- M...

Finding the cell in which an object lies
Hi again I'm trying to make a page break after a chart. But I can't do this i seems, the page break can only be inserted into a cell/row. Any idea how to determine the cell that the corner of an object is in I want to take my object, find what call it is above, and break base on that cell. Cheers -- F*S ----------------------------------------------------------------------- F*SH's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3635 View this thread: http://www.excelforum.com/showthread.php?threadid=56251 the chartobject has a .topleftcell and a .bo...

How do I change the color the cell becomes when doing a FIND
When I do a FIND or FIND / REPLACE, when Excel finds the value I am looking for, it highlights the cell, but the highlighted cell is in white, which is the standard sheet background. How can I change a setting so anytime I try to do a find, when the cell is found, it shows this cell in a totally different color say purple or red or yellow etc.. I find sometimes trying to identify which cell it has selected in a full screen of data can be tough. I jst want to be able to locate the selected cell easier. Thanks for your help Two known and common options to search ALL WB sheets....

Formula to reference another cell in a worksheet
Column G is filled with numbers which represent Rows in my worksheet. I want Column H to equal the contents of Column A Row ? which is referenced in Column G. Example: G1 is 1043, I want H1 to be equal to A1043. What formula can I use to fill column F to do this automatically. Thank you! Heather =indirect("A" & G1) -- HTH... Jim Thomlinson "HeatherJ" wrote: > Column G is filled with numbers which represent Rows in my worksheet. I want > Column H to equal the contents of Column A Row ? which is referenced in > Column G. >...

Copying vertical and horizontal ranges of cells
Will excel allow me to copy a range of vertical cells on one workboo page to a horizontal range on another page? If so, how? Thanks -- Message posted from http://www.ExcelForum.com Hi Checkman............, yes, look up TRANSPOSE in HELP, when you paste there is a transpose option on the dialog. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Checkman >" <<Checkman.17uzi0@excelforum-nospam.com> wrote i...