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