Hi Basil,

One way, use a helper cell.
with a Time in A1
in B1, =TEXT(A1,"hh:mm")
link B1 to your textbox

I think the only alternative would be to remove the link to your textbox and
use code in an event to reformat and copy the Time as a string to your
textbox.

Regards,
Peter T


"Basil" wrote in message
...
Hiya,

I have a form with loads of textboxes on it that are linked (control
source)
to a row on the spreadsheet. There is also a date time picker on this
form.
The form is to enter AND to track timings of an aircraft turnaround on any
particular day. The default day for the date time picker is the current
day.

I have 2 queries:

1. If the user changes the date on the date time picker, the linked cells
will automatically be changed to equal the timings on the selected date.
However, for some reason (and I assume it is because the cells are
linked),
it will not allow me to enter dates in a text format into these cells
(won't
even accept a TEXT(xxx,"hh:mm") formula - it just overwrites it with a
time).
Thus, the textboxes on the form will always read an actual time, yet it
will
show it as a decimal.

I have tried to include code to change the format of all the textboxes
when
the datepicker is changed, but it only seems to work on enter/exit of the
textboxes (i.e. only of any use when they are entering data).

Here is my code:

Private Sub dtpdate_Change()

Dim i As Integer

Worksheets("Timings").Range("D1") = dtpdate
Worksheets("Timings").Range("B10:Y10").Copy
Worksheets("Timings").Range("B4").PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

i = 1
Do Until i = 25
Me("txttime" & i) = Format(Me("txttime" & i).Value, "hh:mm")
'Doesn't work!
i = i + 1
Loop

End Sub

How can I get these damned textboxes to either show the times as in the
time
format, or get the cells behind the textboxes to bloody accept times in a
text format?

2. I've considered using multiple date/time pickers (formatted to time)
instead of the textboxes, but have a really bug that it is annoying to
enter
data into these - you cannot simply enter 2315 as it will not move from
the
hours to the minutes on its own - it will simply overwrite the 23 with the
15. The only way I found around it is to either use the mouse or arrow key
to
move to the minutes.
Is there a way to make data (time) entry easier on the picker?

Thanks for any help on this - it's been killing me for 2 days!!

Basil