Clear Cell Format

[daniel chen]

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

 

[Bob Phillips]

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]

Hi Bob
Thank you for your advice. Appreciated.

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)

 

[Bob Phillips]

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)