How to execute a string VBA for Excel

[GeorgeBro]

I am using VBA for Excel, and am trying to execute the following:


Dim strElement as String

strElement = "frmLoad.lblInd icator" & 1 & ".BackColor = vbGreen"
Evaluate(strEle ment)

---------

The "Evaluate() " function doesn't work, and when I tried "Eval()" it returned an error.

[Guido Geurs]

dear,

the help of Evaluate is;

Converts a Microsoft Excel name to an object or a value.

expression.Eval uate(Name)
expression Optional for Application, required for Chart, DialogSheet, and Worksheet. An expression that returns an object in the Applies To list.

Name Required String. The name of the object, using the naming convention of Microsoft Excel.

Your string = "frmLoad.lblInd icator1.BackCol or = vbGreen"

br,

[GeorgeBro]

So how would I use this in my example? I'm confused.

I don't know if "Evaluate" or "Eval" are the right function for what I am trying to do. I'm trying to execute a string as a line of code.

e.g.
I have n labels called: lblText1, lblText2, ... lblTextn
I wish to programmaticall y change the backcolor of all with a For loop.
How do I do it?

Dim strCode as String

For i = 1 to n
strCode = "frmMain.lblTex t" & i & ".BackColor = vbGreen"
EXECUTE strCode
frmMain.Repaint
Next i

[Guido Geurs]

dear,

It is not possible to construct a command in a string.
it is also not possible to work with indexed names (to use For i=1 to...)
If you want to color named cells, look for each name in the workbook that has the string "lblText" in it and color it.
this is the code:

Sub color()
   For Each n In ActiveWorkbook.Names
      If InStr(n.Name, "lblText") Then
         Range(n).Select
         Selection.Interior.color = vbGreen
      End If
   Next n
End Sub

run the macro in the attachment.
I hope this a solution for your problem.

br,

[GeorgeBro]

That is a great solution which I can use for other situations that I'm encountering, thank you!

I thought of another which also works:

For i = 1 to n
strCode = "lblIndicat or" & CStr(i)
frmStatus.Contr ols(strCode).Ba ckColor = vbGreen
Next i

[Guido Geurs]

dear,

You are right.
To color cells:

Sub color2()
Dim i As Integer
   For i = 1 To 3
      Range("lblText" & i).Select
      Selection.Interior.color = vbGreen
   Next
End Sub

br,