format time problem

[Pyrite]

Hi,

I have a column for enginners to enter the time an appointment starts. I
want this column to look like 10:00, 12:00, 14:00 and so on. Not those exact
numbers, just that format. I went to format cells and all the time formats
include seconds or dates so I went to custom formats. On here I found hh:mm
which is exactly what I need. The only problem is that if I simply enter 10
for instance meaning 10am (as many engineers will) for some reason the time
remains at 00:00 and a date is added before it, usually a date in Jan 1900.
Is there anyway of forcing it to only format to hh:mm?

 

[Bob Phillips]

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

 

[Pyrite]

Thats brilliant thanks Bob. I have had a good read and implemented this. I
even managed to make a few changes specific to the way time is likely to be
input. I amazed myself!!!

I have one further question. When using that code if a user inputted the
time as 10:00 and actually used the colon then it throws up the incorrect
time entered error. Is there anyway of surpressing that in case the more
conscientious users input the time in its full and correct format?

Thanks for all your help Bob

 

[Bob Phillips]

Just edit the value before you parse it to remove : like this

Target.Value = CStr(Replace(Target.Value, ":", ""))

then the code that does the real work

 

[Pyrite]

Bob, again thank you for your time.

I hate to ask this as I do try hard to avoid being spoon fed from this forum
as it is always better to learn. I just cannot figure out where the code
string should go. I have read through the complete script a couple of times
and tried adding it in multiple places but just cannot get it to work,
everytime i enter a : i get the incorrect time error message.

I put it before the EnableEvents = False and ended up with a loop. Anywhere
after that seems to have no effect. Do I need any extra little bits of code
either side or anything like. If you could tell me the line that the
'replace' code goes after I would much appreciate it.

Thanks again Bob

 

[Bob Phillips]

Without seeing the code it is difficult for me to be specific, but I would
expect there to be some code that checks that the correct range has been
changed, and maybe some code to check for just one cell being changed, it
would go after those, before you do anything else with target.