Format text box and move across date time picker

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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


"Basil" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >