Format text box and move across date time picker

[Basil]

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

 

[Peter T]

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]

Thanks for replying Peter,

Using the first suggestion would mean that I can't link to cell B1 - because
then I would not be able to use the form for data entry. I tried creating an
event that would overwrite what is in the linked cell with a formula
converting the time to a string - but this is where Excel goes weird and for
some reason always dismisses the formula and simply pastes in a time if, and
only if, the result of the TEXT() formula looks like a time. I know it sounds
weird, but trust me, it is the case.

The second suggestion involves quite a huge amount of code for each textbox
because of the different ways I want the user to be able to use the form, so
I am really hoping to find another way.

Are there any suggestions on the second query that I raised regarding using
multiple date time pickers for users top enter the time?

(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?

I would ideally want the user to tab into the time picker and be able to
type '2315' and then tab out with the time picker interpreting and storing
this as 23:15.

Can it be done?

Thanks,

Basil

 

[Peter T]

Hi Basil,

I don't really follow a everything, but looking a couple of your points -

Using the first suggestion would mean that I can't link to cell B1 - because
then I would not be able to use the form for data entry.

By a form do you mean a Userform. If so, in the form initialize event can
you not convert the string in B1, that looks like time, into a time value.

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.

I'm totally missing that! What sort of date/time picker are you using?

Some simple string <> time stuff:

Sub test()
Dim s As String
Dim dt As Date
[b1].Formula = "=TEXT(A1,""hh:mm"")"
s = "07:15"
[A1] = CDate(s)
dt = CDate([b1].Value)
MsgBox dt & vbCr & CSng(dt) ' 07:15:00 0.3020833

s = CStr(915)
If InStr(s, ":") = 0 Then
s = Format(Left$(s, 4), "00:00")
End If
dt = CDate(s)
' or simply
dt = s
MsgBox dt

'or maybe
s = 2315
dt = CDate(Left$(s, 2) & ":" & Right$(s, 2))
MsgBox dt

End Sub

I'm probably missing the problems you face, and not carefully digesting your
post. If you want to send a stripped down version of what you have with
pointers to the issues, I'll see if something quick & simple comes to mind.
No guarantee I don't reply with simply - yep, you've got a big task to sort
out!

Regards,
Peter T

pmbthornton gmail com