Format Time As You Type - MS Excel

[bnc123]

I have formatted certain cells of my spreadsheet to indicate time in the format hh:mm using the custom cell formattihg option.

With this method, everytime I enter a time say 19:30, I have to insert a colon using the shift key and all.

Is there a way by which I can simply type in the four digits 1930 and the end result will show up as 19:30 in the relevant cell?

I am a layperson and know nothing about VBA (Visual Basic). So if somebody is going to suggest a VBA code, please use kids' gloves.

I will also paste this posting in the MS Office forum.

Thanks.

 

[Loomah]

Hi
Rather than reinventing the wheel, have a look at this to see if it's what you are after

http://www.cpearson.com/excel/DateTimeEntry.htm

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bnc123]

Thanks Loomah,

But it only works on a brand new workbook (or worksheet). I tried to work it in one of my existing workbooks which already has about 25 worksheets and it does not work.

Anything I should know?

Hope I am not taking up too much of your time, but I simply don't know how VB works.

 

[GlennUK]

Please explain exactly, in what way does it not work?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Loomah]

I've just tried this and it worked fine. The first time!

I then entered times into cells that already had them and it gave strange results. But when I entered times a third, fourth, fifth etc time it was ok.

That's just bizarre and the sort of thing that happens to me!

One thing I wonder, pending an answer to Glenn's question, is that in your workbook with 25 worksheets is the code attached to the right sheet in each case?

Right click the sheet tab and choose view code to make sure you have the right module.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

bnc,

If you're doing this in a workbook that has 25 sheets, the function must be in EVERY SHEET's CODE SHEET -- ALL 25!

Alternatively, it could go in the ThisWorkbook Workbook_SheetChange event. HOWEVER, it would take a code tweek. But the upside is, ONLY ONE PROCEDURE rather than 25.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]