I am nearly finished working on my latest project, a data entry GUI in
VBA (2003), and have run into difficulties dealing with time values.

Brief project background: It is a simple data entry form which takes
various bits of flight data and enters them onto a spreadsheet. The
key entries I'm having trouble with are 'Time' values. Two fields are
automatically populated with the appropriate 'Departure' and 'Arrival'
times based on a 'ListIndex' run during initialization, which allows
values for the 'Flight Numbers'. An 'If...Then' statement refers back
to the 'ListIndex':

private sub fltNum_change
......
If fltNum.ListIndex = 3 then 'Flight Number
fltFrom.value="nan" 'Departure airport
fltTo.value="suv" 'Arrival airport
fltATD.value="5:10" 'Departure time
fltATA.value="5:40" 'Arrival time
end if
......
end sub

To allow the user to incrementally change the values, there are spin-
buttons with the following code that formats a time value:

private sub spinbutton3_SpinDown()
fltATD.text=format(timevalue(fltATD.text) - timevalue("00:01"),
"h:mm")
end sub

private sub spinbutton3_SpinUp()
fltATD.text=format(timevalue(fltATD.text) + timevalue("00:01"),
"h:mm")
end sub

This is where I lose the path :(

My next two (and final) goals elude me:
1) Display a value (value 'A') which is '3 minutes' less than the
'departure time' in a textbox with spin-button control, as well as a
value (value 'B') '3 minutes' more than the 'arrival time' in yet
another textbox with spin-button control.

2) Show a value which is value 'B' minus value 'A' in a textbox on the
userform in decimal-time format. I have the 4 values currently output
to a spreadsheet, and am able to easily subtract 'B' from 'A' and show
in a cell formatted with '=(E1-INT(E1))*24'. The correct decimal time
is shown, but I cant manage to show the value on my userform (and
subsequently allow the data to be entered onto the proper
spreadsheet).

Help please! :) If I can sort these two hurdles out, my project is
done.
Thnx
Clarke

I'm a little confused, because you've already supplied most of the answer for goal 1.

In any case, this is probably the code you want.

TextBoxA.Text = Format(TimeValue(fltATD.Text) - TimeValue("00:03"), "h:mm")
TextBoxB.Text = Format(TimeValue(fltATA.Text) + TimeValue("00:03"), "h:mm")
TextBoxC.Text = Format(TimeValue(TextBoxA.Text) - TimeValue(TextBoxB.Text), "h:mm")

I see a problem developing when the departure/arrival times are within 3 minutes of midnight.
You might want to check for these special cases.

Cheers,
Rob


C4X wrote:
I am nearly finished working on my latest project, a data entry GUI in
VBA (2003), and have run into difficulties dealing with time values.

Brief project background: It is a simple data entry form which takes
various bits of flight data and enters them onto a spreadsheet. The
key entries I'm having trouble with are 'Time' values. Two fields are
automatically populated with the appropriate 'Departure' and 'Arrival'
times based on a 'ListIndex' run during initialization, which allows
values for the 'Flight Numbers'. An 'If...Then' statement refers back
to the 'ListIndex':

private sub fltNum_change
.....
If fltNum.ListIndex = 3 then 'Flight Number
fltFrom.value="nan" 'Departure airport
fltTo.value="suv" 'Arrival airport
fltATD.value="5:10" 'Departure time
fltATA.value="5:40" 'Arrival time
end if
.....
end sub

To allow the user to incrementally change the values, there are spin-
buttons with the following code that formats a time value:

private sub spinbutton3_SpinDown()
fltATD.text=format(timevalue(fltATD.text) - timevalue("00:01"),
"h:mm")
end sub

private sub spinbutton3_SpinUp()
fltATD.text=format(timevalue(fltATD.text) + timevalue("00:01"),
"h:mm")
end sub

This is where I lose the path :(

My next two (and final) goals elude me:
1) Display a value (value 'A') which is '3 minutes' less than the
'departure time' in a textbox with spin-button control, as well as a
value (value 'B') '3 minutes' more than the 'arrival time' in yet
another textbox with spin-button control.

2) Show a value which is value 'B' minus value 'A' in a textbox on the
userform in decimal-time format. I have the 4 values currently output
to a spreadsheet, and am able to easily subtract 'B' from 'A' and show
in a cell formatted with '=(E1-INT(E1))*24'. The correct decimal time
is shown, but I cant manage to show the value on my userform (and
subsequently allow the data to be entered onto the proper
spreadsheet).

Help please! :) If I can sort these two hurdles out, my project is
done.
Thnx
Clarke

On Dec 7, 6:39*pm, Rob van Gelder
wrote:
I'm a little confused, because you've already supplied most of the answer for goal 1.

In any case, this is probably the code you want.

* * *TextBoxA.Text = Format(TimeValue(fltATD.Text) - TimeValue("00:03"), "h:mm")
* * *TextBoxB.Text = Format(TimeValue(fltATA.Text) + TimeValue("00:03"), "h:mm")
* * *TextBoxC.Text = Format(TimeValue(TextBoxA.Text) - TimeValue(TextBoxB.Text), "h:mm")

I see a problem developing when the departure/arrival times are within 3 minutes of midnight.
You might want to check for these special cases.

Cheers,
Rob

C4X wrote:
I am nearly finished working on my latest project, a data entry GUI in
VBA (2003), and have run into difficulties dealing with time values.

Brief project background: It is a simple data entry form which takes
various bits of flight data and enters them onto a spreadsheet. The
key entries I'm having trouble with are 'Time' values. Two fields are
automatically populated with the appropriate 'Departure' and 'Arrival'
times based on a 'ListIndex' run during initialization, which allows
values for the 'Flight Numbers'. An 'If...Then' statement refers back
to the 'ListIndex':

private sub fltNum_change
.....
If fltNum.ListIndex = 3 then * * 'Flight Number
* *fltFrom.value="nan" * * * * * * 'Departure airport
* *fltTo.value="suv" * * * * * * * * 'Arrival airport
* *fltATD.value="5:10" * * * * * * 'Departure time
* *fltATA.value="5:40" * * * * * * 'Arrival time
end if
.....
end sub

To allow the user to incrementally change the values, there are spin-
buttons with the following code that formats a time value:

private sub spinbutton3_SpinDown()
* *fltATD.text=format(timevalue(fltATD.text) - timevalue("00:01"),
"h:mm")
end sub

private sub spinbutton3_SpinUp()
* *fltATD.text=format(timevalue(fltATD.text) + timevalue("00:01"),
"h:mm")
end sub

This is where I lose the path :(

My next two (and final) goals elude me:
1) Display a value (value 'A') which is '3 minutes' less than the
'departure time' in a textbox with spin-button control, as well as a
value (value 'B') '3 minutes' more than the 'arrival time' in yet
another textbox with spin-button control.

2) Show a value which is value 'B' minus value 'A' in a textbox on the
userform in decimal-time format. I have the 4 values currently output
to a spreadsheet, and am able to easily subtract 'B' from 'A' and show
in a cell formatted with '=(E1-INT(E1))*24'. The correct decimal time
is shown, but I cant manage to show the value on my userform (and
subsequently allow the data to be entered onto the proper
spreadsheet).

Help please! :) If I can sort these two hurdles out, my project is
done.
Thnx
Clarke

Okay, thnx Rob. I have been staring at this off and on for a while
now. It gets blurry :) Should have worked that out. Anyways, we have
no flights around midnight, should be no prob there. Any thoughts on
how to format the total flight time as a decimal time (i.e: h.mm)? I
haven't come across it and the 'cell formula' I posted above is
unusable it seems . . .thnx.

On Dec 7, 11:09*pm, C4X wrote:
On Dec 7, 6:39*pm, Rob van Gelder
wrote:



I'm a little confused, because you've already supplied most of the answer for goal 1.

In any case, this is probably the code you want.

* * *TextBoxA.Text = Format(TimeValue(fltATD.Text) - TimeValue("00:03"), "h:mm")
* * *TextBoxB.Text = Format(TimeValue(fltATA.Text) + TimeValue("00:03"), "h:mm")
* * *TextBoxC.Text = Format(TimeValue(TextBoxA.Text) - TimeValue(TextBoxB.Text), "h:mm")

I see a problem developing when the departure/arrival times are within 3 minutes of midnight.
You might want to check for these special cases.

Cheers,
Rob

C4X wrote:
I am nearly finished working on my latest project, a data entry GUI in
VBA (2003), and have run into difficulties dealing with time values.

Brief project background: It is a simple data entry form which takes
various bits of flight data and enters them onto a spreadsheet. The
key entries I'm having trouble with are 'Time' values. Two fields are
automatically populated with the appropriate 'Departure' and 'Arrival'
times based on a 'ListIndex' run during initialization, which allows
values for the 'Flight Numbers'. An 'If...Then' statement refers back
to the 'ListIndex':

private sub fltNum_change
.....
If fltNum.ListIndex = 3 then * * 'Flight Number
* *fltFrom.value="nan" * * * * * * 'Departure airport
* *fltTo.value="suv" * * * * * * * * 'Arrival airport
* *fltATD.value="5:10" * * * * * * 'Departure time
* *fltATA.value="5:40" * * * * * * 'Arrival time
end if
.....
end sub

To allow the user to incrementally change the values, there are spin-
buttons with the following code that formats a time value:

private sub spinbutton3_SpinDown()
* *fltATD.text=format(timevalue(fltATD.text) - timevalue("00:01"),
"h:mm")
end sub

private sub spinbutton3_SpinUp()
* *fltATD.text=format(timevalue(fltATD.text) + timevalue("00:01"),
"h:mm")
end sub

This is where I lose the path :(

My next two (and final) goals elude me:
1) Display a value (value 'A') which is '3 minutes' less than the
'departure time' in a textbox with spin-button control, as well as a
value (value 'B') '3 minutes' more than the 'arrival time' in yet
another textbox with spin-button control.

2) Show a value which is value 'B' minus value 'A' in a textbox on the
userform in decimal-time format. I have the 4 values currently output
to a spreadsheet, and am able to easily subtract 'B' from 'A' and show
in a cell formatted with '=(E1-INT(E1))*24'. The correct decimal time
is shown, but I cant manage to show the value on my userform (and
subsequently allow the data to be entered onto the proper
spreadsheet).

Help please! :) If I can sort these two hurdles out, my project is
done.
Thnx
Clarke

Okay, thnx Rob. I have been staring at this off and on for a while
now. It gets blurry :) Should have worked that out. Anyways, we have
no flights around midnight, should be no prob there. Any thoughts on
how to format the total flight time as a decimal time (i.e: h.mm)? I
haven't come across it and the 'cell formula' I posted above is
unusable it seems . . .thnx.

Disregard, I missed it. Thnx again!