StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POsetting repetative properties for userform textboxes: Can this get done more easily?
text
Body
copied!<p>Here is code I wrote that locks down userform textboxes when a condition is met. It has clear inefficiencies in the form of repetition:</p> <pre><code> With txtESPPStock .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With With txtChildLife .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With With txtHealthcareFSA .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With With txtLtdPlusBuyUp .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With With txtMedicalDental .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With With txtSpouseLife .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With With txtStdPlusBuyUp .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With With txtSupplementalLife .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With With txtVision .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With With txt401kReg .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With </code></pre> <p>Since all the interior settings are the same, it seems like if I were only smart enough I could code it in many fewer lines. What I would want is to say, for each textbox, something like:</p> <pre><code>With txtESPPStock Call GetProperties End With </code></pre> <p>Or even better, get all of the properties that need the property setting to sit in an array so I can do something like:</p> <pre><code>For txtFieldNumber = 0 To 15 For PropertySettings = 0 To 3 ArrayValue(txtFieldNumber, PropertySettings) = getproperty(PropertySettings) Next PropertySettings Next txtFieldNumber </code></pre> <p>Maybe I'm close with the structure of this last one, yet I seem to lack some piece of knowledge that makes it clear to me how to do this. Can anyone help straighten me out?</p> <hr> <p>After learning you could loop through arrays in the same way as looping through ranges, I was able to slim down the code as I expected and make a tool that is reusable by other userforms without coding the settings again:</p> <pre><code>Sub SetLockedFields(ByRef arrLockedFields() As Variant) Dim varFieldToLock As Variant For Each varFieldToLock In arrLockedFields With frmPaycheckEntry.Controls(varFieldToLock) .Locked = True .BackColor = &H80000000 .value = "" .Enabled = False End With Next End Sub </code></pre> <p>To do all the work shown in my first code example above, I now just place the text fields in a variant array and pass them, like this:</p> <pre><code>Private Sub UserForm_Initialize() Dim arrLockedFields() As Variant . . . If <condition is met> Then ReDim arrLockedFields(1 To 11) arrLockedFields = Array("txtBasicLife", "txtChildLife", _ "txtESPPStock", "txtHealthcareFSA", _ "txtLtdPlusBuyUp", "txt401kReg", _ "txtMedicalDental", "txtSpouseLife", _ "txtStdPlusBuyUp", "txtSupplementalLife", _ "txtVision") End If Call SetLockedFields(arrLockedFields) End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload