Unable to Unlock And Enable Controls

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. 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.
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 Sub

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.
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
is it possible for you to post image of user form and the issue.
 
Upvote 0
My apologies Mukeshy for taking so long to acknowledge your support. Maybe the image will help solve this big hurdle.
I will post an image as soon as I have an opportunity to prepare one. Thank you for your patience!!
 
Upvote 0
I hope someone can help me resolve this hurdle ... I've been plugging away since my original post stepping through each line of code and nothing obvious is coming across.
Here is an image of my user form. The area highlighted in yellow is where the issue is. All the controls are locked and they shouldn't be.
If I check "Not required" then everything defaults ... "Eligible for" is unchecked, 0.0 hrs and times are replaced with "NR". Crews are both empty. Everything but "Not required" is locked.
Unchecking "Not required" unlocks all the controls, checks "Eligible for" and allows theuser to use the comboboxes and enter text (time) in the textboxes.

But as I cycle through the records (using the next or previous buttons), the controls are always locked.

 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Select Case Problem
Replies
3
Views
552
Fluff
Fluff
A
  • Question Question
Problem With Scrolling Data Through A Static Display Range
Replies
5
Views
1K
Ark68
A
A
  • Question Question
ByRef argument type mismatch error
Replies
4
Views
766
hiker95
H
A
  • Question Question
Can A Control Have More Than 1 Event Trigger?
Replies
0
Views
486
Ark68
A
K
  • Question Question
Unable to use current page prop of pivotfeild class
Replies
0
Views
530
karthuappu
K

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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