Use a String for assigning object values

[jd_pow]

Ok. I'm a excel VB n00b, so please bear with me. If I use the wrong technical terms, please correct me.

I am using Windows 7 and Excel 2007. What I'm trying to do is keep my code extensible. I am doing a small project in Excel where I need to display items on a map. I am linking these items via check boxes. You click the check box, it shows up. If you un-check it, it disappears. Simple. My dilemma is that I am trying to code this so that if new checkboxes are added or taken away, it's easy to modify the code as such. The code below is where my problem is and what I'm thinking on how to get it done. If you can think of a better way of doing this, I'm open because I have been racking my brain trying to figure this out.

For simplicity-

Sheet1.CheckBox1.Value = True 'this is what i want to happen

'Now, trying to do the same thing as above...
Dim myString1, myString2, checkBoxNumberString As String
Dim checkBoxNum As Integer


checkBoxNum = 1  'just keeping it simple here
checkBoxNumString = CStr(checkBoxNumber)
  
myString1 = "Sheet1.CheckBox" & checkBoxNumber & ".Value = True"   'option1
myString2 = "Sheet1.CheckBox" & checkBoxNumber & ".Value"          'option 2
MsgBox (myString1)  'outputs Sheet1.CheckBox1.Value = True
MsgBox (myString2)  'outputs Sheet1.CheckBox1.Value


'how do i execute myString1 so that it is indeed "True"?
myString2 = True  'does not work  :(  

Sheet1.CheckBox1.Value = True   'again, the desired execution

I hope I was clear. Again, if you can think of a different way to do this, I'm all ears (or eyes ). Thank you so much for reading and any help provided!

 

[GlennUK]

I think that you are aiming a bit high as a VBA noob ... making code extensible is a fine ambition, but don't you think you should learn to walk before you try running?

That said, this thread gives you all the information you need:

http://www.mrexcel.com/forum/showthread.php?t=562852