I need a concise way to write code for similar scenarios with multiple textboxes in Userform

[dellehurley]

Hi
I need a shortcut as the code I am writing is very long and bulky. I'm sure there is a shortcut but I cannot find the answer online.
I have a database with a userform, a number is places in the box circled in red, this represents the number of entries there are for this particular file. I want to close the fields which do not apply.
Currently I write the code like this

If Me.txtRecs.Value = 1 Then
        Me.txtRIN1.Enabled = True
        Me.txtSurname1.Enabled = True
        Me.txtFirstName1.Enabled = True
        Me.txtYOB1.Enabled = True
        Me.cmdAdd1.Enabled = True
        Me.txt1.Value = "a"
        Me.txtRIN2.Enabled = False
        Me.txtSurname2.Enabled = False
        Me.txtFirstName2.Enabled = False
        Me.txtYOB2.Enabled = False
        Me.txtRIN3.Enabled = False
        Me.txtSurname3.Enabled = False
        Me.txtFirstName3.Enabled = False
        Me.txtYOB3.Enabled = False
        Me.txtRIN4.Enabled = False
        Me.txtSurname4.Enabled = False
        Me.txtFirstName4.Enabled = False
        Me.txtYOB4.Enabled = False 'etc

       If Me.txrRecs.Value=2 then  'etc

this continues until I have up to each set of boxes listed then I move on to if Me.texRecs.Value =2
I know there must be a shortcut so I don't have to write out each line for each scenario.
Any advice?
Thanks
Dannielle

 

[Dan_W]

Hi. This should be doable, but I have a few questions/points for clarification:
1. It seems that you're either enabling/disabling sets of textboxes based on whether the set's respective number is entered into the textbox on the top left, but then what is Me.txt1.Value = "a" for?
2. HOw many sets are there? I can see about 7 in the picture.
Thanks.

 

[Dan_W]

Hi, I've done some guessing, and subject to your answer to my questions above, the following code should theoretically work.

Basically, it will:

1. cycle through each of the controls (textbox, command button, etc) on your userform;
2. it will check to see if the name of the control matches a set patten of names - those are "txtRIN", "txtSurname", "txtFirstName", "txtYOB", "cmdAdd".
3. If the control matches the pattern and the number that you input into the txtRecs box, then the control is enabled.
4. IF the control matches the pattern but not the number, then that control is disabled.
5. Everything else is ignored.

I still don't know what the txt1, txt2, txt3 etc are for, and because I haven't listed them in the pattern array, they'll be ignored. If they need to be included, let me know.

There are two procedures - the ProcessTextboxes and EnableTextbox. They can both go in your userform if you like, or you can put the ProcessTextboxes sub in your userform and the EnableTextbox sub in a standard module. I wasn't sure how you trigger the process - whether you press a button, or if it changes automatically when you put a number in the textbox. Either way, you just need to call the ProcessTextboxes subroutine. I hope that makes sense. Let me know if you have any trouble with it. Fingers crossed.

Sub ProcessTextboxes()
    ' This subroutine should go in the userform
    Dim TargetNumber As Long
    
    TargetNumber = Me.txtRecs.value
    EnableTextbox Me, TargetNumber

End Sub

Sub EnableTextbox(Frm As Object, TargetNumber As Long)
    
    Dim ControlArray    As Variant
    Dim Ctrl            As Object
    Dim counter         As Long
    
    ' Create an array for the textbox names
    ControlArray = Array("txtRIN", "txtSurname", "txtFirstName", "txtYOB", "cmdAdd")
   
    ' Cycle through each of the controls on the form
    For Each Ctrl In Frm.Controls
    
        ' For each control, cycle through each of the control names in the array
        For counter = LBound(ControlArray) To UBound(ControlArray)
            
            ' If the relevant control name matches the current control name in the array PLUS the target number then enable the control
            If LCase(Ctrl.Name) = LCase(ControlArray(counter) & TargetNumber) Then
                Ctrl.Enabled = True
            
            ' If it's not a match, but the control name pattern is similar to that in the array, then disable the control
            ElseIf LCase(Ctrl.Name) Like LCase(ControlArray(counter) & "?") Then
                Ctrl.Enabled = False
            End If          ' Thus ignoring all other controls, leaving them enabled
        Next
    Next
        
End Sub

 

[Dan_W]

Sorry - one further question - you've got cmdAdd1, but I don't see any others listed (e.g., cmdAdd2) - is that correct? there's only the one? Because this code will disable cmdAdd1 if the user selects 2, or 3 or so on.

 

[dellehurley]

Thank you that is what I was looking for. I actual code was sort of irrelevant. I just found that I was writing the long repetitive amount of code and knew that there must have been a shortcut or less cumbersome way.

Hi, I've done some guessing, and subject to your answer to my questions above, the following code should theoretically work.

Basically, it will:

1. cycle through each of the controls (textbox, command button, etc) on your userform;
2. it will check to see if the name of the control matches a set patten of names - those are "txtRIN", "txtSurname", "txtFirstName", "txtYOB", "cmdAdd".
3. If the control matches the pattern and the number that you input into the txtRecs box, then the control is enabled.
4. IF the control matches the pattern but not the number, then that control is disabled.
5. Everything else is ignored.

I still don't know what the txt1, txt2, txt3 etc are for, and because I haven't listed them in the pattern array, they'll be ignored. If they need to be included, let me know.

There are two procedures - the ProcessTextboxes and EnableTextbox. They can both go in your userform if you like, or you can put the ProcessTextboxes sub in your userform and the EnableTextbox sub in a standard module. I wasn't sure how you trigger the process - whether you press a button, or if it changes automatically when you put a number in the textbox. Either way, you just need to call the ProcessTextboxes subroutine. I hope that makes sense. Let me know if you have any trouble with it. Fingers crossed.

Sub ProcessTextboxes()
    ' This subroutine should go in the userform
    Dim TargetNumber As Long
   
    TargetNumber = Me.txtRecs.value
    EnableTextbox Me, TargetNumber

End Sub

Sub EnableTextbox(Frm As Object, TargetNumber As Long)
   
    Dim ControlArray    As Variant
    Dim Ctrl            As Object
    Dim counter         As Long
   
    ' Create an array for the textbox names
    ControlArray = Array("txtRIN", "txtSurname", "txtFirstName", "txtYOB", "cmdAdd")
  
    ' Cycle through each of the controls on the form
    For Each Ctrl In Frm.Controls
   
        ' For each control, cycle through each of the control names in the array
        For counter = LBound(ControlArray) To UBound(ControlArray)
           
            ' If the relevant control name matches the current control name in the array PLUS the target number then enable the control
            If LCase(Ctrl.Name) = LCase(ControlArray(counter) & TargetNumber) Then
                Ctrl.Enabled = True
           
            ' If it's not a match, but the control name pattern is similar to that in the array, then disable the control
            ElseIf LCase(Ctrl.Name) Like LCase(ControlArray(counter) & "?") Then
                Ctrl.Enabled = False
            End If          ' Thus ignoring all other controls, leaving them enabled
        Next
    Next
       
End Sub

 

[dellehurley]

Sorry - one further question - you've got cmdAdd1, but I don't see any others listed (e.g., cmdAdd2) - is that correct? there's only the one? Because this code will disable cmdAdd1 if the user selects 2, or 3 or so on.

No there is cmdAdd1 to cmdAdd10 so you code is correct. This is an issue which I have throughout this code, so your help has shown me how I should do this from now on, I really appreciate your help.

 

[Dan_W]

That's great, and thank you for the explanation. This method really only works because of how you named your controls - which was helpful and makes the coding easier.

 

[dellehurley]

No there is cmdAdd1 to cmdAdd10 so you code is correct. This is an issue which I have throughout this code, so your help has shown me how I should do this from now on, I really appreciate your help.

Hi Dan I know that it has been a while since you answered my original post. I'm trying to modify the code so that all the ControlArray with TargetNumber <= remain and the others disable and hide.

The code below only keeps the ControlArray with TargetNumber = visible. 1 option image

Sub Enable_Disable(Frm As Object, TargetNumber As Long)
Dim ControlArray As Variant
Dim Ctrltxt, CtrlRIN, CtrlName As Object
Dim counter As Long

With frmNewEntry
    ControlArray = Array("cmbFullName", "txtRIN", "txt", "cmdAdd", "lblNm", "lblRIN")
       
        For Each ctrl In Frm.Controls
            For counter = LBound(ControlArray) To UBound(ControlArray)
                If LCase(ctrl.Name) = LCase(ControlArray(counter) & TargetNumber) Then
                    ctrl.Enabled = True
                    ctrl.Visible = True             
                ElseIf LCase(ctrl.Name) Like LCase(ControlArray(counter) & "?") Then
                    ctrl.Enabled = False
                    ctrl.Visible = False
                End If 
            Next
        Next
    End With
End Sub

These are some of the work arounds I tried. (Only the top part of the userform is show in the images below)

1. Change IF code to

   If LCase(ctrl.Name) <= LCase(ControlArray(counter) & TargetNumber) Then

   leave ELSEIF code as is. see result 2 option image.
2. Change IF code to the same as 1 plus change ELSEIF to

   ElseIf LCase(ctrl.Name) > LCase(ControlArray(counter) & "?") Then

   see result 3 option image.
3. Change IF code to the same as 1 plus change ELSEIF to

   ElseIf LCase(ctrl.Name) > LCase(ControlArray(counter) & TargetNumber) Or LCase(ctrl.Name) Like LCase(ControlArray(counter) & "?") Then

   see result 3 option image.
4. Change IF code to the same as 2 plus change ELSEIF to

   ElseIf LCase(ctrl.Name) > LCase(ControlArray(counter) & TargetNumber) And LCase(ctrl.Name) Like LCase(ControlArray(counter) & "?") Then

   see result 3 option image.
5. Change IF code to the following and leave ELSEIF as original

   If counter >= TargetNumber And LCase(Ctrl.Name) = LCase(ControlArray(counter) & TargetNumber) Then

   see result 4 option image.

Any ideas how I can get this to work? (I hope this makes sense)

 

[dellehurley]

Hi Dan I know that it has been a while since you answered my original post. I'm trying to modify the code so that all the ControlArray with TargetNumber <= remain and the others disable and hide.

The code below only keeps the ControlArray with TargetNumber = visible. 1 option image

Sub Enable_Disable(Frm As Object, TargetNumber As Long)
Dim ControlArray As Variant
Dim Ctrltxt, CtrlRIN, CtrlName As Object
Dim counter As Long

With frmNewEntry
    ControlArray = Array("cmbFullName", "txtRIN", "txt", "cmdAdd", "lblNm", "lblRIN")
      
        For Each ctrl In Frm.Controls
            For counter = LBound(ControlArray) To UBound(ControlArray)
                If LCase(ctrl.Name) = LCase(ControlArray(counter) & TargetNumber) Then
                    ctrl.Enabled = True
                    ctrl.Visible = True            
                ElseIf LCase(ctrl.Name) Like LCase(ControlArray(counter) & "?") Then
                    ctrl.Enabled = False
                    ctrl.Visible = False
                End If
            Next
        Next
    End With
End Sub

These are some of the work arounds I tried. (Only the top part of the userform is show in the images below)

1. Change IF code to

   If LCase(ctrl.Name) <= LCase(ControlArray(counter) & TargetNumber) Then

   leave ELSEIF code as is. see result 2 option image.
2. Change IF code to the same as 1 plus change ELSEIF to

   ElseIf LCase(ctrl.Name) > LCase(ControlArray(counter) & "?") Then

   see result 3 option image.
3. Change IF code to the same as 1 plus change ELSEIF to

   ElseIf LCase(ctrl.Name) > LCase(ControlArray(counter) & TargetNumber) Or LCase(ctrl.Name) Like LCase(ControlArray(counter) & "?") Then

   see result 3 option image.
4. Change IF code to the same as 2 plus change ELSEIF to

   ElseIf LCase(ctrl.Name) > LCase(ControlArray(counter) & TargetNumber) And LCase(ctrl.Name) Like LCase(ControlArray(counter) & "?") Then

   see result 3 option image.
5. Change IF code to the following and leave ELSEIF as original

   If counter >= TargetNumber And LCase(Ctrl.Name) = LCase(ControlArray(counter) & TargetNumber) Then

   see result 4 option image.

Any ideas how I can get this to work? (I hope this makes sense)

I changed tack and go it to work. This is what I ended up using.

Dim i As Long
Dim Ctrl As Object
Dim ControlArray

    With frmNewEntry
        For i = 1 To 20
            If Me.txtRecs.Value < i Then
                Me.Controls("cmbfullname" & i).visible = False
                Me.Controls("txtRIN" & i).visible = False
                Me.Controls("txt" & i).visible = False
                Me.Controls("cmdAdd" & i).visible = False
                Me.Controls("lblNm" & i).visible = False
                Me.Controls("lblRIN" & i).visible = False
            Else
                Me.Controls("cmbfullname" & i).visible = True
                Me.Controls("txtRIN" & i).visible = True
                Me.Controls("txt" & i).visible = False
                Me.Controls("cmdAdd" & i).visible = True
                Me.Controls("lblNm" & i).visible = True
                Me.Controls("lblRIN" & i).visible = True
            End If

 

[Dan_W]

Hi - it's good to hear from you - I do actually remember helping out - I even had a flashback when I saw the notification! Those were the days... back when I was a young whippersnapper!

Anyway, my personal rule in life is that: if it's working, back away from it slowly so as not to disrupt anything, and then when you're a good distance clear of it, turn around and run like a bat-outta-hell and hopefully, if something goes wrong, it'll be someone else's problem.

Be that as it may, is there anything you'd like help with? Looking at the code you opted to go with, I note that the controls starting txt will never actually be visible. Is that the intention, or am I misreading it? I see that you've devised a number of logic puzzles to keep me on my toes - nice try, but I'm as dense as ever. I tend to find when it comes to trying to divine WTH VBA is doing with logic gates like these is to simply Debug.Print out the results for each loop. That way I can keep track of what's going on, and see if my logic and VBA's logic are in accord (it rarely is).

So in your initial code above, I would probably write something like:

                If LCase(ctrl.Name) = LCase(ControlArray(counter) & TargetNumber) Then
                    Debug.Print counter, " If [" & LCase(ctrl.Name) & "] = [" & LCase(ControlArray(counter) & TargetNumber) "] Then = TRUE"
                    ctrl.Enabled = True
                    ctrl.Visible = True             
                ElseIf LCase(ctrl.Name) Like LCase(ControlArray(counter) & "?") Then
                     Debug.Print counter, "ElseIf [" & LCase(ctrl.Name) & "] Like [" & LCase(ControlArray(counter) & "?") & "] Then = TRUE"

This way I can see what variable values and logic permutations triggered a TRUE result, and shows me were in the loop it happened. Does that make sense? Give me a shout if there is anything I can do to help.