Unable To Trigger Control Change Events After Control Initially Changed

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I've run into a snag in my Excel VBA project.

In my userform (userform1), I am using a combination of 4 combo boxes (uf1cb_hrstart, uf1cb_minstart, uf1cb_hrend, and uf1cb_minend) to allow the user to select hr and minute values for both start and end times.

Upon userform1 initialization these 4 boxes are empty and with the exception of uf1cb_hrstart, are disabled disabling the user from entering any information before the start hour is entered.

When the user selects the start hour (0-23) from the drop down, uf1cb_minstart is enabled and the minute value (uf1cb_minstart) is populated with "00" with an control change event. The changing of the start minute value the enables the remaining two comboboxes for the end hour and end minute values. The end hour and minutes (uf1cb_hrend, uf1cb_minend) are automatically populated to (start hours +8), and "00" respectively. All is great.

However, I run into a problem when the user changes the start time by selecting a different start hour once initially set. I am unable to trigger the code uf1cb_minstart change code. The only way it will execute, is if the user changes changes the value in the uf1cb_minstart control. I'm assuming I can't trigger the change event with code?

Rich (BB code):
Private Sub uf1cb_hrstart_Change()
    'Stop
    uf1cb_minstart.Enabled = True
    uf1cb_minstart.Value = "00"     'this line will not trigger uf1cb_minstart after subsequent changes to uf1cb_hrstart. Only triggers uf1cb_minstart code after the hour is first set[
End Sub

Private Sub uf1cb_minstart_Change()
    If Not mbEvents Then Exit Sub
    Dim temp_end As Long
    
    uf1cb_hrend.Enabled = True
    temp_end = uf1cb_hrstart + 8
    If temp_end > 24 Then
        n_date = n_date + 1
        uf1_ndate = Format(n_date, "dd-mm-yy")
        uf1cb_hrend = 8 - (24 - uf1cb_hrstart.Value)
    Else
        uf1cb_hrend = uf1cb_hrstart + 8
    End If
    mbEvents = False
    uf1cb_minend = uf1cb_minstart.Value
    mbEvents = True
    uf1cbx2_eqt.SetFocus
End Sub
Private Sub uf1cb_hrend_Change()
    If Not mbEvents Then Exit Sub
    uf1cb_hrend.BackColor = RGB(244, 247, 252)
    uf1cb_minend.Enabled = True
    uf1cb_minend.ForeColor = black
End Sub

Private Sub uf1cb_minend_Change()
    
    If Not mbEvents Then Exit Sub
    'Stop
    start_time = n_date + ((uf1cb_hrstart + (uf1cb_minstart / 60)) / 24)
    'MsgBox start_time
    'MsgBox Format(start_time, "mmm-dd-yyyy hh:mm")
    'If UserForm1.uf1cb_hrend.Value = 24 Then n_date = n_date + 1
    end_time = n_date + ((uf1cb_hrend + (uf1cb_minend / 60)) / 24)
    'MsgBox end_time
    'MsgBox Format(end_time, "mmm-dd-yyyy hh:mm")
    
    If end_time < start_time Then
        
        ui1 = MsgBox("Does this shift extend into the next day (" & Format(n_date + 1, "dd-mmm-yy"), vbYesNo + vbExclamation, "INQUIRY")
        If vbYesNo = vbYes Then
            uf1_ndate = n_date + 1
        Else
            mbEvents = False
            errmsg1 = "Error: Shift End has to be later than shift start."
            errtitle = "ERROR : INVALID SHIFT TIME"
            uf1cb_hrend.Value = ""
            uf1cb_hrend.BackColor = RGB(255, 163, 163)
            uf1cb_minend.Value = ""
            uf1cb_minend.BackColor = RGB(255, 163, 163)
            uf1cb_minend.Enabled = False
            uf1cb_hrend.SetFocus
            mbEvents = True
            err_box.Show
        End If
    ElseIf end_time = start_time Then
        mbEvents = False
        errmsg1 = "Error: Shift End is the same as shift start."
        errtitle = "ERROR : INVALID SHIFT TIME"
        uf1cb_hrend.Value = ""
        uf1cb_hrend.BackColor = RGB(255, 163, 163) 'error red
        uf1cb_minend.Value = ""
        uf1cb_minend.BackColor = RGB(255, 163, 163)
        uf1cb_minend.Enabled = False
        uf1cb_hrend.SetFocus
        mbEvents = True
        err_box.Show
    Else
        uf1cb_hrend.BackColor = RGB(244, 247, 252)
        uf1cb_minend.BackColor = RGB(244, 247, 252)
        uf1cbx2_eqt.SetFocus
    End If
End Sub


Is anyone able to provide a solution for what I am looking to do? Many thanks in advance.


PS ... you might also see my failed attempts of error catching if the user enters an end time before the start time. I've discovered that this doesn't work for shifts that extend into the early hours of the next day. If anyone wants to take a loon at this too, I will be eventually posting it as it's issue once I get the initial one figured out.
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top