Entering 24 Hour Clock without entering :

J

JB Bates

I am trying to figure out how to enter military time into a spreadsheet and
not have to manually enter the colon. I am using Vista as an operating
system and Excel 2007

EX enter 1330
it appears in cell as 13:30

In a previous post I saw a response that said to do the following:

"Preformat the column as time and try this.
Right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
Application.EnableEvents = False
Target = Left(Target, 2) & ":" & Right(Target, 2)
Application.EnableEvents = True
End Sub"

I tried this and it does not work - don't know if it is a Vista issue. Any
help is GREATLY appreciated!!

Jennifer
 
J

JB Bates

AWASOME!!!! This worked!! thank you for directing me to that article. I
also changed my date fields to be entered without "/"

You have totally made my week!! thanks!
 
J

JB Bates

Is it possible to have this apply to more than one range in a spreadsheet?

I substituted x5:ac7 instead of the a1:a10 range

now i want to apply this ALSO to ranges I11:L20 AND O11:R20

THANKS
 
J

Jim Thomlinson

change your range to...

If Application.Intersect(Target, Range("x5:ac7, I11:L20, O11:R20")) Is
Nothing
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

please help.is it possible re-edit macro to convert text like1530-2200 to time format automatically? 1
Trying to run excel vba code when cell value changes from empty to entered value. 1
Out of Stack Space error 2
Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
time format 22
Time with VBA code 8
MACRO HELP 9
Excel How to run multiple Private Sub Worksheet_Change(ByVal Target As Range) 0

Top