Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,570
- Office Version
- 365
- 2016
- Platform
- Windows
I hope someone is able to help me isolate a cause for a particular aspect of my Excel VBA project not working.
I have a userform, for which is a frame hosting 2 checkboxes (chk_fl_nr, chk_fl_eligible); 3 textboxs (tb_fl_hours, tb_fl_stontime, tb_fl_stofftime) and 4 comboboxes (cb_fl_oncrew, cb_fl_offcrew, cb_stonq, cb_stoffq).
During the userform's initialization, these controls are populated based on data from my database, and their properties set according to these values.
If tb_fl_hours = 0 then all the fields are disabled and locked, chk_fl_nr is checked ("Not required") and chk_fl_eligible is unchecked.
If tb_fl_hours > 0 then all the fields are enabled and unlocked, chk_fl_nr is unchecked ("Required") and chk_fl_eligible is checked.
During initialization, the first record in the database is called and these fields are populated and set based on that record's data. In testing, if the first record's tb_fl_hours value is > 0, everything behaves as normal. All the comboboxes have operable dropdown options, the checkboxes function and information can be entered into the textboxes.
The user can press a {NEXT} button on the userform which populates the userform data, including these controls, according to that record's information. The user can step one by one through all the records in the database displaying each record's information on the userform. The user is then encouraged to make and submit any changes to the visible record.
The problem I am experiencing is, when the user advances to the next record, and tb_fl_hours once again is greater than zero, none of the controls are unlocked. This is different behaviour than when the first record is displayed during initialization.
Below is the code I have that, once the user advances to the next record, populates and adjust the properties of the controls accordingly. The section specific to these fields is in purple. You can see where I have tried tomake attempts to unlock and enable theses controls. When I step through this code, I receive no errors,but I don't get theresults either.
My apologies in advance if I may have unintentionally left out any relevant code. If you feel I have,please ask. Unfortunately, I can't post the whole project, it's rather big, nor can I post the project because of confidential data and references to network drives etc. that would only cause grief.
All my other controls are working as they should, and have similar characteristics where they can be in either a state of locked or unlocked based on the data.
I have a userform, for which is a frame hosting 2 checkboxes (chk_fl_nr, chk_fl_eligible); 3 textboxs (tb_fl_hours, tb_fl_stontime, tb_fl_stofftime) and 4 comboboxes (cb_fl_oncrew, cb_fl_offcrew, cb_stonq, cb_stoffq).
During the userform's initialization, these controls are populated based on data from my database, and their properties set according to these values.
If tb_fl_hours = 0 then all the fields are disabled and locked, chk_fl_nr is checked ("Not required") and chk_fl_eligible is unchecked.
If tb_fl_hours > 0 then all the fields are enabled and unlocked, chk_fl_nr is unchecked ("Required") and chk_fl_eligible is checked.
During initialization, the first record in the database is called and these fields are populated and set based on that record's data. In testing, if the first record's tb_fl_hours value is > 0, everything behaves as normal. All the comboboxes have operable dropdown options, the checkboxes function and information can be entered into the textboxes.
The user can press a {NEXT} button on the userform which populates the userform data, including these controls, according to that record's information. The user can step one by one through all the records in the database displaying each record's information on the userform. The user is then encouraged to make and submit any changes to the visible record.
The problem I am experiencing is, when the user advances to the next record, and tb_fl_hours once again is greater than zero, none of the controls are unlocked. This is different behaviour than when the first record is displayed during initialization.
Below is the code I have that, once the user advances to the next record, populates and adjust the properties of the controls accordingly. The section specific to these fields is in purple. You can see where I have tried tomake attempts to unlock and enable theses controls. When I step through this code, I receive no errors,but I don't get theresults either.
My apologies in advance if I may have unintentionally left out any relevant code. If you feel I have,please ask. Unfortunately, I can't post the whole project, it's rather big, nor can I post the project because of confidential data and references to network drives etc. that would only cause grief.
Rich (BB code):
'RID CHANGE (previous, next, listbox)
Private Sub tb_rid_Change()
Dim row_num As Long, trn_ui1 As Integer
mbEvents = True
Dim flag As Double
Dim rtype As String
Dim lnumber1 As Long, lnumber2 As Long, lNumber3 As Long, lNumber4 As Long, lNumber5 As Long, lNumber6 As Long
If Not mbEvents Then Exit Sub
Application.ScreenUpdating = False
If ws_data.AutoFilterMode Then ws_data.AutoFilterMode = False
lrid = Me.tb_rid.Value
Me.lseq1.Value = WorksheetFunction.Match(lrid, ws_data.Range("$A:$A"), 0) - 1
ws_vh.Range("B38") = lrid
row_num = Application.WorksheetFunction.Match(lrid, ws_data.Range("A:A"), 0)
flag = WorksheetFunction.VLookup(lrid, ws_data.Range("A:V"), 22, False)
If flag > 0 Then 'if submit flag = 1 (>0) then record already submitted
Me.tb_rid.BackColor = RGB(0, 192, 0)
Else
Me.tb_rid.BackColor = RGB(255, 255, 255) 'default white
End If
With Me
.tb_comment_1.Value = ""
.tb_comment_2.Value = ""
.tb_comment_3.Value = ""
.tb_comment_4.Value = ""
'.tb_rnum.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 3, False)
'rn_1 = .tb_rnum.Value 'rental number
.tb_rnum.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 3, False)
rn_1 = .tb_rnum.Value
.tb_rnum.Locked = True ' can't be changed
.tb_ammnum.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 4, False) 'ammendment number
.tb_ammnum.Locked = True ' can't be changed
.tb_xtrafee.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 18, False) 'extra fee comment
.tb_xtrafee.Locked = True ' can't be changed
.tb_class.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 13, False) 'facility class
If tb_class.Value = "C" Then 'if it's a class C facility with associated fees
.c_flag.Visible = True ' CLASS C display flag
ws_data.Range("U" & row_num) = 0 ' lights duration = 0
ws_data.Range("Y" & row_num) = 0 ' lights (adhusted) duration = 0
Else 'A or B class facility
.c_flag.Visible = False ' do not display flag
End If
.tb_class.Locked = True ' can't be changed
.tb_customer.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 7, False) 'customer name
.tb_customer.Locked = False 'editable
rtype = Application.WorksheetFunction.VLookup(lrid, rvl5, 5, False) 'type of rental
.tb_rtype.Value = rtype ' userform value = variable
.tb_rtype.Locked = False ' editable
If .tb_rtype = "DT" Then 'if it's a diamond tournament
trn_ui1 = ws_data.Range("DO" & row_num)
ws_vh.Range("B50") = trn_ui1
With Me.tournament
.Visible = True ' display tournament services button
.ForeColor = RGB(106, 151, 161)
.BackColor = RGB(0, 61, 153)
.Caption = "TOURNAMENT [" & trn_ui1 & "]"
End With
Else 'if not
.tournament.Visible = False ' hode display button
End If
.tb_custtele1.Value = Application.WorksheetFunction.VLookup(rn_1, rvl6, 12, False) 'customer telephone number
.tb_event.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 6, False) 'event name
.tb_fac1.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 8, False) 'facility
.tb_fac1.Locked = True ' can't be changed
.tb_unita.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 11, False) 'facility unit (type)
.tb_unita.Locked = True ' can't be changed
.tb_unitb.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 12, False) 'facility unit (number)
.tb_unitb.Locked = True ' can't be changed
.tb_prostart.Value = format(Application.WorksheetFunction.VLookup(lrid, rvl5, 14, False), "h:mm AM/PM") 'program start
'ws_staff.Range("N10") = TimeValue(.tb_prostart.Value) 'replace VAR_HOLD.N10
.tb_prostart.Locked = True ' can't change
.tb_proend.Value = format(Application.WorksheetFunction.VLookup(lrid, rvl5, 15, False), "h:mm AM/PM") 'program end
'ws_staff.Range("N12") = TimeValue(.tb_proend.Value) 'replace VAR_HOLD.N12
.tb_proend.Locked = True ' can't change
wstime1 = Application.WorksheetFunction.VLookup(lrid, rvl5, 43, False) 'grm_time
wstime2 = Application.WorksheetFunction.VLookup(lrid, rvl5, 46, False) 'prep_time
wstime3 = Application.WorksheetFunction.VLookup(lrid, rvl5, 23, False) 'sig_time
wstime4 = Application.WorksheetFunction.VLookup(lrid, rvl5, 49, False) 'close_time
wstime5 = Application.WorksheetFunction.VLookup(lrid, rvl5, 26, False) 'light_on_time
wstime6 = Application.WorksheetFunction.VLookup(lrid, rvl5, 28, False) 'light_off_time
lnumber1 = GetFirstNumeric(wstime1)
lnumber2 = GetFirstNumeric(wstime2)
lNumber3 = GetFirstNumeric(wstime3)
lNumber4 = GetFirstNumeric(wstime4)
lNumber5 = GetFirstNumeric(wstime5)
lNumber6 = GetFirstNumeric(wstime6)
Stop
If Left(rtype, 1) = "D" Then
Me.Frame103.Visible = True
Me.Frame106.Visible = False
Me.Frame105.Visible = False
Me.Frame104.Visible = False
Me.cb_basedist.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 31, False) '{AE} Bdist
Me.cb_battersbox.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 35, False) '{AI} Bbox
Me.cb_coachsbox.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 41, False) '{AO} Cbox
Me.cb_commitline.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 39, False) '{AM} Cline
Me.cb_foul.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 33, False) '{AG} Fline
Me.cb_pitchcircle.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 37, False) '{AK} Pcircle
Me.cb_pitchdist.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 32, False) '{AF} Pdist
Me.cb_runline.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 36, False) '{AJ} Rline
Me.cb_safebase.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 34, False) '{AH} Sbase
Me.cb_safeline.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 40, False) '{AN} Sline
Me.cb_strikemat.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 38, False) '{AL} Smat
Me.tb_comment_1.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 61, False) '{BI} Comment
Me.tb_other1_1.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 58, False) '{BF} Other1
Me.tb_other2_1.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 59, False) '{BG} Other1
'Grooming is not required or unavailable
If Application.WorksheetFunction.VLookup(lrid, rvl5, 43, False) = "NR" Then
Me.chk_ig_nr.Value = True
Me.chk_ig_na.Value = False
Me.cb_ig_date.Value = ""
Me.cb_ig_date.Locked = True
Me.cb_ig_stq.Value = ""
Me.tb_ig_sttime.Value = format(Application.WorksheetFunction.VLookup(lrid, rvl5, 43, False), "h:mm AM/PM")
Me.cb_ig_crew.Value = ""
Me.cb_ig_crew.Locked = True
ElseIf Application.WorksheetFunction.VLookup(lrid, rvl5, 43, False) = "NA" Then
Me.chk_ig_nr.Value = False
Me.chk_ig_na.Value = True
Me.cb_ig_date.Value = ""
Me.cb_ig_date.Locked = True
Me.cb_ig_stq.Value = ""
Me.tb_ig_sttime.Value = format(Application.WorksheetFunction.VLookup(lrid, rvl5, 43, False), "h:mm AM/PM")
Me.cb_ig_crew.Value = ""
Me.cb_ig_crew.Locked = True
Else
Me.chk_ig_nr.Value = False
Me.chk_ig_na.Value = False
Me.cb_ig_date.Value = format(Application.WorksheetFunction.VLookup(lrid, rvl5, 42, False), "d-mmm")
Me.cb_ig_date.Locked = False
Me.cb_ig_stq.Value = Trim(Left(wstime1, lnumber1 - 1))
Me.tb_ig_sttime.Value = Mid(wstime1, lnumber1)
Me.tb_ig_sttime.Locked = False
Me.cb_ig_crew.Value = Left(Application.WorksheetFunction.VLookup(lrid, rvl5, 44, False), 3)
Me.cb_ig_crew.Locked = False
End If
'Infield setup is not required or unavailable
If Application.WorksheetFunction.VLookup(lrid, rvl5, 46, False) = "NR" Or Application.WorksheetFunction.VLookup(lrid, rvl5, 46, False) = "NA" Then
Me.chk_ps_nr.Value = True
Me.cb_ps_crew.Value = ""
Me.cb_ps_date.Value = ""
Me.cb_ps_stq.Value = ""
Me.cb_ps_crew.Locked = False
Me.cb_ps_date.Locked = False
Me.cb_ps_stq.Locked = False
Me.tb_ps_sttime.Value = format(Application.WorksheetFunction.VLookup(lrid, rvl5, 46, False), "h:mm AM/PM")
Me.tb_ps_sttime.Locked = False
Me.chk_ps_initial.Value = False
Me.chk_ps_change.Value = False
Me.chk_ps_reline.Value = False
Me.chk_ps_initial.Locked = True
Me.chk_ps_change.Locked = True
Me.chk_ps_reline.Locked = True
Else
Me.chk_ps_nr.Value = False
Me.cb_ps_date.Value = format(Application.WorksheetFunction.VLookup(lrid, rvl5, 45, False), "d-mmm")
Me.cb_ps_stq.Value = Trim(Left(wstime2, lnumber2 - 1))
Me.tb_ps_sttime.Value = Mid(wstime2, lnumber2)
Me.cb_ps_crew.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 47, False)
If Application.WorksheetFunction.VLookup(lrid, rvl5, 30, False) = "REL" Then
Me.chk_ps_initial.Value = False
Me.chk_ps_change.Value = False
Me.chk_ps_reline.Value = True
Me.chk_ps_initial.Locked = True
Me.chk_ps_change.Locked = True
ElseIf Application.WorksheetFunction.VLookup(lrid, rvl5, 30, False) = "CHG" Then
Me.chk_ps_initial.Value = False
Me.chk_ps_change.Value = True
Me.chk_ps_reline.Value = False
Me.chk_ps_initial.Locked = True
Me.chk_ps_reline.Locked = True
Else
Me.chk_ps_initial.Value = True
Me.chk_ps_change.Value = False
Me.chk_ps_reline.Value = False
Me.chk_ps_change.Locked = True
Me.chk_ps_reline.Locked = True
End If
Me.chk_ps_initial.Locked = True
Me.chk_ps_change.Locked = True
Me.chk_ps_reline.Locked = True
End If
'Closing is not required or unavailable
If Application.WorksheetFunction.VLookup(lrid, rvl5, 49, False) = "NR" Or Application.WorksheetFunction.VLookup(lrid, rvl5, 49, False) = "NA" Then
Me.chk_pc_nr.Value = True
Me.cb_pc_crew.Value = ""
Me.cb_pc_date.Value = ""
Me.cb_pc_stq.Value = ""
Me.cb_pc_crew.Locked = True
Me.cb_pc_date.Locked = True
Me.cb_pc_stq.Locked = True
Me.tb_pc_sttime.Value = format(Application.WorksheetFunction.VLookup(lrid, rvl5, 49, False), "h:mm AM/PM")
Me.tb_pc_sttime.Enabled = False
Else
Me.chk_pc_nr.Value = False
Me.cb_pc_date.Value = format(Application.WorksheetFunction.VLookup(lrid, rvl5, 48, False), "d-mmm")
Me.cb_pc_stq.Value = Trim(Left(wstime4, lNumber4 - 1))
Me.cb_pc_stq.Enabled = True
Me.tb_pc_sttime.Value = Mid(wstime4, lNumber4)
Me.cb_pc_crew.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 50, False)
Me.tb_pc_sttime.Enabled = True
Me.cb_pc_crew.Locked = False
Me.cb_pc_date.Locked = False
End If
'Lighting is not required
'Mask lighting frame (frame111) if facility has no lights
If Application.WorksheetFunction.VLookup(lrid, rvl5, 26, False) = "NA" Then 'facility has no lights, mask frame, adjust program closing (frame109)
Me.Frame111.Visible = False
With Me.Frame109
.Top = 20
.Left = 558
End With
Else
With Me.Frame111
.Top = 6
.Left = 558
.Visible = True
End With
With Me.Frame109 'program closing
.Top = 138
.Left = 558
End With
End If
If Application.WorksheetFunction.VLookup(lrid, rvl5, 25, False) = 0 Then 'if hours column is 0 then no lights
Me.chk_fl_nr.Value = True
Me.cb_fl_oncrew.Value = ""
Me.cb_fl_offcrew.Value = ""
Me.tb_fl_hours.Value = "0.0"
Me.chk_fl_eligible.Value = False
Me.cb_stonq.Value = ""
Me.cb_stoffq.Value = ""
If Application.WorksheetFunction.VLookup(lrid, rvl5, 26, False) = "NR" Then
Me.tb_fl_stontime.Value = "NR"
Me.tb_fl_stontime.Enabled = False
Me.tb_fl_stofftime.Value = "NR"
Me.tb_fl_stofftime.Enabled = False
Me.chk_fl_nr.Locked = False
Me.chk_fl_nr.Enabled = True
Me.chk_fl_eligible.Locked = True
Me.chk_fl_eligible.Enabled = False
Me.cb_fl_oncrew.Locked = True
Me.cb_fl_offcrew.Locked = True
Me.cb_fl_oncrew.Enabled = False
Me.cb_fl_offcrew.Enabled = False
Me.tb_fl_hours.Locked = True
Me.tb_fl_hours.Enabled = False
Me.cb_stonq.Locked = True
Me.cb_stonq.Enabled = False
Me.cb_stoffq.Locked = True
Me.cb_stoffq.Enabled = False
Me.tb_fl_stontime.Locked = True
Me.tb_fl_stofftime.Locked = True
Me.Frame111.Enabled = False
Else
Me.tb_fl_stontime.Value = "NA"
Me.tb_fl_stontime.Enabled = False
Me.tb_fl_stofftime.Value = "NA"
Me.tb_fl_stofftime.Enabled = False
Me.chk_fl_nr.Enabled = False
Me.Frame111.Enabled = False
Me.cb_stonq.Enabled = False
Me.cb_stoffq.Enabled = False
Me.tb_fl_stontime.Enabled = False
Me.tb_fl_stofftime.Enabled = False
Me.cb_fl_oncrew.Enabled = False
Me.cb_fl_offcrew.Enabled = False
Me.tb_fl_hours.Enabled = False
Me.chk_fl_eligible.Enabled = False
End If
Else 'hours columns holds value >0
Me.chk_fl_nr.Value = False
Me.chk_fl_nr.Locked = False
Me.chk_fl_nr.Enabled = True
Me.cb_fl_oncrew.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 27, False)
Me.cb_fl_offcrew.Value = Application.WorksheetFunction.VLookup(lrid, rvl5, 29, False)
Me.cb_fl_oncrew.Locked = False
Me.cb_fl_offcrew.Locked = False
Me.cb_fl_oncrew.Enabled = True
Me.cb_fl_offcrew.Enabled = True
Me.tb_fl_hours.Value = format(Application.WorksheetFunction.VLookup(lrid, rvl5, 25, False), "0.0")
Me.tb_fl_hours.Enabled = True
Me.tb_fl_hours.Locked = False
Me.chk_fl_eligible.Value = True
Me.chk_fl_eligible.Locked = True
Me.chk_fl_eligible.Enabled = True
Me.cb_stonq.Value = ""
Me.tb_fl_stontime.Value = format(wstime5, "h:mm AM/PM")
Me.tb_fl_stontime.Enabled = True
Me.cb_stoffq.Value = ""
Me.tb_fl_stofftime.Value = format(wstime6, "h:mm AM/PM")
Me.tb_fl_stofftime.Enabled = True
Me.cb_stonq.Enabled = True
Me.cb_stoffq.Enabled = True
End If
End if
Endif
End With
ws_vh.Range("B28") = CDate(Me.tb_prostart.Value)
ws_vh.Range("B29") = CDate(Me.tb_proend)
'mbEvents = False
Application.ScreenUpdating = True
End SubAll my other controls are working as they should, and have similar characteristics where they can be in either a state of locked or unlocked based on the data.
Last edited:
