select shapes based on checkboxes at the click of a button

[mukuldhingra]

Hi,

I am a newbie. I have a worksheet with 5 box shapes (Box 1, Box 2 ....) and 5 checkboxes (Check1, Check2, ....). What I want now is based on the check boxes I have selected the corresponding boxes should get selected on the click of a button.

The code I have written is:

Sub CSelect()

Dim C As Integer
Dim B As Long

For C = 1 To 5
For istateloop = 1 To 5
 

If ActiveSheet.OLEObjects("Check" & C).Object.Value = True Then
ActiveSheet.Shapes.Range(Array("Box " & B)).Select
End If
Next

Next C

End Sub

When I run this code only the 5th box gets selected.

Please let me know where I am going wrong

 

[Andrew Poulsom]

Welcome to MrExcel.

Why do you have two loops there? And what is the purpose of the variable B, to which nothing has been assigned?

If you select a single object previous selections are unselected. You need to create an array of all the objects to be selected.

 

[mukuldhingra]

Hi Andrew,

Apologies... that 'B' is actually istateloop which i forgot to sanitize while posting this query... what I want to do is .. i have multip[le shapes on my worksheet and have similar number of checkboxes... i need to write a code, where user selects the 1 or multiple checkboxes and once he clicks OK button after selected checkboxes.. relevant shapes should get selected..

 

[mukuldhingra]

hereis the snapshot of the excel worksheet

https://drive.google.com/file/d/0BwE-At7Nfb3HWnpuWWVEX2NZV00/view?usp=sharing

 

[Andrew Poulsom]

See if you can adapt this example:

Sub Test()
    Dim i As Long
    Dim j As Long
    Dim List() As String
    With ActiveSheet
        For i = 1 To 5
            If .OLEObjects("CheckBox" & i).Object.Value = True Then
                j = j + 1
                ReDim Preserve List(1 To j)
                List(j) = "Rectangle " & i
            End If
        Next i
        .Shapes.Range(List).Select
    End With
End Sub

 

[mukuldhingra]

perfect!! Works like Charm.. Thanks a Ton Andrew