Dynamically set the property of a TextBox

[JoeCampanini]

I have a Worksheet with the names of 34 different TextBox's in column 4. In column 5 I have the names of various colors. In column 6 I have the name of a Sub, for example
tbxCat1 vbYellow TextBoxSettings1
tbxCat2 vbCyan TextBoxSettings1 <==========There are 6 different TextBoxSettings Subs
etc.
I read all of these into two arrays:

Please Login or Register  to view this content.

At some random point in the program I'll call a subroutine, for example:

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

I think the error is something to do with "Dim tbxName() As String"
Can anyone advice?

Regards

Joe

[6StringJazzer]

Welcome to the Forum JoeCampanini!

You can't use the string "vbYellow" to assign a color. vbYellow is a built-in Long constant, not a String. You need to either put numbers there, or add a Select Case statement to convert the String to its corresponding Long value.

I see a couple of programming practices that could be improved but this will be hard to nail without your file. I do not have enough time to create a file from scratch to test this.

First I strongly recommend to everyone that they declare variables. Doing so prevents a lot of bugs and runtime errors. You have many variables that are defaulting to Variant.

You are passing tbxColor(N) (a String) to tbxColour (a Variant). Then you assigning it to tbx.BackColor (a Long). You should use a consistent data type for this. You should declare array tbxColor as Long and load it with numbers, and declare tbxColour as Long.

You are using TextBoxSettings1 as both a value in a Case, and a Sub name. I can't figure out what you are trying to do there. Do you mean to do this:

Please Login or Register  to view this content.

That should clear your problem, but if not it will make it easier to diagnose. If you still have trouble can you attach your file? The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Also, please do not use the Location field for a joke. We ask for location so 1) know what your Excel internationalization is, and 2) get some idea of your time zone.