ComboBox Time Value/Command button click update

Attach workbook has a user form.

I have it close to functioning the way I need it to.

What I can't figure out is how to format the ComboBox to display the selected time (in minutes) and not a decimal value - if I select 00:01:00 it should stay 00:01:00.

Second, I need the times (labels where time calculation is displayed) to update every time the command button is clicked from the values in the ComboBoxs.

Also, it enters debug if the ComboBox times are selected first before clicking the command button, how can I get around this?

Any help is appreciated!

Thank you.

Hi hwtcrl,

Welcome to the joys of Excel time formatting. See the attached updated workbook. I changed the following:
a. I changed your cells in Column 'A' to text format by putting an apostrophe in front of each value. Although Excel displays the values as text, it really stores the values as a real number 0 <= x < = 1 as a fraction of a day. e.g. 0.25 is 6AM.

b. I changed the UserForm code to add the times and then convert them to display in PrettyPrint format:

Label3.Caption = Format(CDate(TN + T1), "hh:mm:ss")

Complete UserForm code follows:

Private Sub ComboBox1_Change()

Dim TN As Date, T1 As Date, TS As Date
T1 = CDate(ComboBox1.Value)
TN = CDate(Label2.Caption)
Label3.Caption = Format(CDate(TN + T1), "hh:mm:ss")
End Sub
Private Sub ComboBox2_Change()

Dim TN As Date, T1 As Date, TS As Date
T1 = CDate(ComboBox2.Value)
TN = CDate(Label2.Caption)
Label4.Caption = Format(CDate(TN + T1), "hh:mm:ss")

End Sub
Private Sub ComboBox3_Change()

Dim TN As Date, T1 As Date, TS As Date
T1 = CDate(ComboBox3.Value)
TN = CDate(Label2.Caption)
Label5.Caption = Format(CDate(TN + T1), "hh:mm:ss")


End Sub
Private Sub CommandButton1_Click()

Label2.Caption = Format(Now(), "hh:mm:ss")

End Sub

Lewis

Originally Posted by LJMetzger

Hi hwtcrl,

Welcome to the joys of Excel time formatting. See the attached updated workbook. I changed the following:
a. I changed your cells in Column 'A' to text format by putting an apostrophe in front of each value. Although Excel displays the values as text, it really stores the values as a real number 0 <= x < = 1 as a fraction of a day. e.g. 0.25 is 6AM.

b. I changed the UserForm code to add the times and then convert them to display in PrettyPrint format:

Lewis

Originally Posted by mehmetcik

try this version

Lewis/mehmetcik

Thank you for the corrections, however I would like to be able to click the command button multiple times and each time update, not just current time, no matter when the user clicks the button the times they have selected in the combobox will update

Originally Posted by mehmetcik

Ok try this:

Let me restate the purpose of each entity.

The command button should display time now() next to it (where it says <--Time Now) whenever the user clicks the button.

Each combobox should display the user selected time in minutes, if they choose 5 in the first, 7 in the second, 10 in the third it should stay 5,7,10 and not change.

The Time1-3 to the right of each combobox should display the computed time (time now value + combobox minute selection value) based upon the user combobox selections.

No matter when the user clicks the command button the time now updates, as well as the computed times to the right of each combobox.

The combobox values should never change unless the user changes them.

Hi hwtcrl,

Try the following code which is included in the updated attached file. Please note that the 'TimeNow' value only changes when 'CommandButton1' is Activated.

UserForm1 Code Module:

Option Explicit

Private Sub ComboBox1_Change()

  Call UpdateLabels3Thru5

End Sub

Private Sub ComboBox2_Change()

  Call UpdateLabels3Thru5

End Sub

Private Sub ComboBox3_Change()

  Call UpdateLabels3Thru5

End Sub

Private Sub CommandButton1_Click()

  Label2.Caption = Format(Now(), "hh:mm:ss")
  Call UpdateLabels3Thru5

End Sub

Private Sub UpdateLabels3Thru5()

  Dim myTimeOffset As Date
  Dim myTimeNow As Date
  Dim myCalculatedTime As Date

  Dim sValue As String
  
  'Get the value in the Label
  'Process only if the Time is valid
  'Otherwise Exit
  sValue = Label2.Caption
  If IsDate(sValue) Then
    myTimeNow = CDate(sValue)
  Else
    Exit Sub
  End If
  
  
  ''''''''''''''''''''''''''''''''''''''
  'ComboBox1 Processing
  ''''''''''''''''''''''''''''''''''''''
  
  'Get the Value in the ComboBox (strip out leading and trailing spaces)
  'Process the Value ONLY if the value in the ComboBox is NOT BLANK
  sValue = Trim(ComboBox1.Value)
  If Len(sValue) > 0 Then
    myTimeOffset = CDate(sValue)
    myCalculatedTime = CDate(myTimeNow + myTimeOffset)
    Label3.Caption = Format(myCalculatedTime, "hh:mm:ss")
  End If
  
  ''''''''''''''''''''''''''''''''''''''
  'ComboBox2 Processing
  ''''''''''''''''''''''''''''''''''''''
  
  'Get the Value in the ComboBox (strip out leading and trailing spaces)
  'Process the Value ONLY if the value in the ComboBox is NOT BLANK
  sValue = Trim(ComboBox2.Value)
  If Len(sValue) > 0 Then
    myTimeOffset = CDate(sValue)
    myCalculatedTime = CDate(myTimeNow + myTimeOffset)
    Label4.Caption = Format(myCalculatedTime, "hh:mm:ss")
  End If
  
  ''''''''''''''''''''''''''''''''''''''
  'ComboBox3 Processing
  ''''''''''''''''''''''''''''''''''''''
  
  'Get the Value in the ComboBox (strip out leading and trailing spaces)
  'Process the Value ONLY if the value in the ComboBox is NOT BLANK
  sValue = Trim(ComboBox3.Value)
  If Len(sValue) > 0 Then
    myTimeOffset = CDate(sValue)
    myCalculatedTime = CDate(myTimeNow + myTimeOffset)
    Label5.Caption = Format(myCalculatedTime, "hh:mm:ss")
  End If
  
End Sub

Lewis

Originally Posted by LJMetzger

Hi hwtcrl,

Try the following code which is included in the updated attached file. Please note that the 'TimeNow' value only changes when 'CommandButton1' is Activated.

UserForm1 Code Module:


Lewis

And Lewis does it! Thank you so much for the help everything works like a charm!