How to concatenate a string to return checkbox name

[Dman333]

Hi All,

I’ve been struggling with how to return the name of checkboxes using a For Next statement so I can push data to cells if the box is checked. There are 20 buttons on the form.

First I tried

Dim DBoard As Worksheet
Set DBoard = ThisWorkbook.Sheets("Woodside Dashboard")

' Planning Area Checkboxes

For i = 4 To 22
If Me.("CBoxRow" & i).Value = True Then DBoard.Cells(i, 4) = "Yes" Else DBoard.Cells(i, 4) = "No"
Next i


Then I tried a variation trying to use the Set command

Set ctl = Me.("CBoxRow" & i).Value


These give me a “Expected: identifier or bracketed expression ERROR) at the “(“ after the “Me.” Above.

How can I write a line of code that will allow me to concatenate the Checkbox name so I can run the If Then Loop?

Thanks in advance for any help - Cheers

 

[Dryver14]

Is CBoxRow a variable, if so it does not want to be in quotes

 

[John_w]

The Me keyword can only be used in a class module - your own class, userform or a built-in class, such as a Sheet module.

If your code is in a normal module then use the DBoard worksheet object instead of Me:

Public Sub Test()

    Dim DBoard As Worksheet
    Dim i As Long
    Dim cb As CheckBox
    
    Set DBoard = ThisWorkbook.Worksheets("Woodside Dashboard")
    
    For i = 4 To 22
        Set cb = DBoard.Shapes("CBoxRow" & i).OLEFormat.Object
        '1 = checkbox is ticked
        If cb.Value = 1 Then DBoard.Cells(i, 4) = "Yes" Else DBoard.Cells(i, 4) = "No"
    Next

End Sub

 

[Dman333]

Thanks for responding. No -CBoxRow is part of the chackbox name and the last part of the name are the numbers 4-22. I'm mirroring code that I use to change the label of each checkbox so it constantly refers to the same row and want to get the True/False for each checkbox and push it to another cell in the same row as Yes/No.

The value being pushed Yes/No drives the sheet formulas. Does that make sense?

I'm just starting to expand my VBA use and think I have the wrong sequence of syntax from object down.

Any more thoughts? Than ks for the help - much appreciated.

 

[Dman333]

This code is part of the form object under Forms. It is not in a module under the Modules folder of the Workbook. Do this help?

Thanks for spending the time - any help is much appreciated!

Cheers