CheckBox name from string

[Tomeczek]

I have a spreadsheet with lines generated by macro. Each line has a CheckBox in col. "A" named "CheckBoxNN", where "NN" is a number (CheckBox1, CheckBox2,... CheckBox88, and so on). The number of lines varies.

Is there a way of reading the value of each check box in the loop? Like bCheckValue = CheckBoxNN.value. I'm thinking of making a check box name out of string ("CheckBox" and the number, which would be a number of the counter in "for" loop.)

Any suggestions? Thanks!

 

[SkipVought]

hi,

What kind of control? Form or ActiveX?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Tomeczek]

... ActiveX.

 

[SkipVought]

Sub test()
    Dim sp As Shape
    For Each sp In Sheet1.Shapes
        If sp.Type = msoOLEControlObject Then
            Debug.Print sp.Name, sp.OLEFormat.Object.Object.Value, sp.TopLeftCell.Address
        End If
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Tomeczek]

Thank you, Skip.

Using this sub I can, indeed, get the names (strings) of all of my check boxes. But these I know from the beginning. My problem is: how to read the value of the check box knowing only the name of it as a string? How to get CheckBox23.Value knowing only it's name in form of the string ("CheckBox23")?

 

[PHV]

Did you try this ?
MsgBox Sheet1.Shapes("CheckBox23").OLEFormat.Object.Object.Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Function WhatChkVal(sNAME As String)
    
   WhatChkVal = Sheet1.Shapes(sNAME).OLEFormat.Object.Object.Value
    
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Tomeczek]

It works, thanks!

(Thank you for unbelievable quick response! You've been great help, Skip.)