How do I call a dynamically created String in a VBA Sub

  • Thread starter Thread starter izimmer
  • Start date Start date
I

izimmer

Hi,

I have 50 Check Boxes and I'm trying to create a Sub that accepts an
Integer parameter (1-50) and then changes the value of the check box to
true.

This is the code that I'm trying to do

Sub chngVal(x)
Dim thestring As String
thestring = "CheckBox" & x & ".Value = True"
thestring
End Sub

So if I called chngVal(25), it would call CheckBox25.Value = True.

This code doesn't work but is there a way to create a String or some
form of Object and then call the line?

Thanks!

Ilan
 
Hi Martin,

Thanks for your help.

Unfortunately the code didn't seem to work.

I'm getting an error with the Controls part saying "Compile Error -
Method or Data member not Found"

Is there any other way in which to call the string? This would now also
be helpful as for some stages I need to make all 50 Check Boxes not
visible and so would like to use a while or for statement instead of
hand coding all 50 statements of CheckBoxxx.Visible = False.

Thanks for your help again.

Ilan
 
Good chance that with 50 checkboxes, these are on a worksheet rather than a
userform. In that case, you would use

Sub chngVal(x as Long)
Worksheets("Sheet1").OleObjects("CheckBox" & x).Object.Value = True
end sub

Assumes checkboxes from the control toolbox toolbar located on a worksheet
named Sheet1 with names like CheckBox1 to CheckBox50
 
Thanks Tom.

You're a superstar. It worked.

Tom said:
Good chance that with 50 checkboxes, these are on a worksheet rather than a
userform. In that case, you would use

Sub chngVal(x as Long)
Worksheets("Sheet1").OleObjects("CheckBox" & x).Object.Value = True
end sub

Assumes checkboxes from the control toolbox toolbar located on a worksheet
named Sheet1 with names like CheckBox1 to CheckBox50
Click to expand...
 
Back
Top