making a userform components attribute names and values a two dimensional array.

[kerrywayne]

I have a vba project with many many userforms and each has many components(textboxes, option buttons, command buttons, etc). I am trying to figure a way to set the component properties names and values in an array and use to set the component properties in a For next loop. See below code example:

dim UserformComponent(7) as variant ' (0-2) = TextBoxes, (3-4) = OptionButtons, (5-6) = CommandButtons.
dim Proprty(7, 13, 2) as variant '7 = Number of UserformComponents, 13 = number of Component Properties, 2: (0) = Property Name, (1) = Property Value
' Property Names = Width, Height, Left, BackColor, ForeColor, Locked. etc

Propty(0,0,0) = "BackColor"
Propty(0,0,1) = H000080FF
Propty(0,1,0) = Enabled
Propty(0,1,1) = True
NumOfProps = 13
ComponentNum = 0
UserformComponent(CompenentNum) = "Textbox1"
For PropNum = 0 to NumbOfProps
UserForm1.Controls(UserformComponent(CompNum)).Propty(0,0,0) = Propty(0,0,1) ' I also tried UserForm1.Controls(UserformComponent(CompNum)).Controls(Propty(ComponetNum,PropNum,0)) = Propty(ComponentNum,PropNum,1)
Next PropNum

I get Run Time Error 438: Object does not support this property or method with both statements

Question: Is there a way to do what I am trying to do without assigning values to each property on 13 different lines of code?

 

[dmt32]

Hi

An idea to consider is to create a table for each of your userforms listing their controls & properties. You can then read the table in to a 2D array. A common code for all tables, would read each array element to set the control properties.

You can make the table more intuitive by using data validation for specific controls like comboboxes & textboxes to list available property choices with their constant names & values

a custom number format will need to be applied so that the cell contains the numeric value of the selection.

As with all these things, careful planning & preparation is key to make a project successful but hope idea is helpful.

Dave