Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Change attributes of controls inside VBA userforms in easier way

Sometimes we need to use set of similar controls when developing applications with userforms. For an example we may need to use set of labels, dropdowns or textboxes etc inside a vba userform. If we have similar set of controls like that, then there are situations we may need to change the values< or visibility of the controls according to the various logics and conditions.

Let's look at the following example.



Here we have 4 labels and 4 combo boxes. Sometimes we need to control the attributes of these controls or change the values of controls. If we give random names for each control then we need to control their attributes one by one or we need write one line per each control to change their values. But if we have similar set of controls and we name them simila manner, then we can change the attributes or values of the controls using a For Next loop. So for above example we can name lables like lblCycle1, lblCycle2, lblCycle3 and lblCycle4. And we can name combo boxes as cboScore1, cboScore2, cboScore3 and cboScore4. Then we can change the attributes as follows.

Dim i as Integer
Dim NumberOfCycles as Integer

For i = 1 To NumberOfCycles
    UserForm2.Controls("lblCycle" & i).Visible = True
    UserForm2.Controls("cboScore" & i).Visible = True
Next i

In above example, variable "NumberOfCycles" equal to how many pairs of labels and combo boxes we need to show in userform2.

Contact Form

Name

Email *

Message *

Popular Posts