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

 

[dellehurley]

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.

Oh yes we have aged over the past months, it must be this pandemic, but I assure you I look as youthful as ever (the lies we can tell when no photos are involved).
As for the txt always being hidden yes that is correct a fact I lost sight of in all my mucking about. When I got it to work I realised and hid it again.
Anyway I will definitely give the above a go. I have seen people use the debug print function on YouTube but have never used it myself.

Here is another quick question. Is it possible write one code like below BUT for it to cover all items named something like this eg cmbFullName1, cmbFullName2, cmbFullName3 etc?

Private Sub cmbFullName1_Enter()
Me.cmbFullName1.BackColor = RGB(255, 255, 204)
End Sub

Thanks
Dannielle