excel dim cell text value as controls

Question

I am having trouble with my macro. I have a button and a few check boxes on a form, i am trying to write a code that when i click the button it will check the range("A1").text, after take that text (which in this case is "checkbox1") and i want to say something like this:

questionnaire = Range("A1").Text
questionnaire.Value = Range("A2").Value

since i have many checkboxes as mentioned earlier, i want that when i write a certain controls name in "A1", when i click the button, that control will take the value in "A2". the problem is i do not know what i should DIM questionnaire as. please help

Thank you

Answer 1

Try this

Private Sub CommandButton1_Click()
    CB = Sheets(3).Range("A1").Value
    CBVal = Sheets(3).Range("A2").Value

    For Each contr In UserForm1.Controls
        If TypeName(contr) = "CheckBox" And contr.Name = CB Then
            contr.Value = CBVal
        End If
    Next
End Sub

Make sure you type in the correct Name in A1.