Linking multiple cells

[Slywalker]

I'm a total noob at vba, so forgive me if this is a simple fix.

Last time I were here, I got some nice person to help me with this:

Sub ToggleGrey()
    Dim ChkBox As Excel.CheckBox
        Set ChkBox = ActiveSheet.CheckBoxes(Application.Caller)
        With Range(ChkBox.LinkedCell).Offset(0, 1).Resize(1, 7).Interior
            If ChkBox.Value = xlOn Then
                 .ColorIndex = 16
            Else
                 .ColorIndex = xlColorIndexNone
            End If
        End With
End Sub

Just a 'simple' sub to grey out the line of cells that have been toggled by checkbox.

I would like to change this to toggle multiple lines with 1 checkbox. Say, if my checkbox is linked to A32:A35. My original assumption were that I could fiddle with the Offset, but I can't figure out how, without being forced to make a new sub for each specific amount of lines, I would like to effect.

Please don't just give me the correct answer. I'd love to be able to understand why as well.

Thanks in advance

 

[Vbagreenhorn1]

i think the solution is to change the resize(1,7) to resize(4,7)

4 = 4 rows
7 = 7 columns

 

[Slywalker]

Yea, I fiddled around with that for a bit. As far as I can tell, that would require me to make multiple subs, if next checkbox is 5 rows. I was hoping I could tie the length to the linked cells directly.

Thanks for the input tho

 

[Vbagreenhorn1]

Linking the checkbox to A32:A35 doesnt help, but when you enter the number of wanted lines in the alternative text of the checkbox this wil help:

Sub ToggleGrey()
    Dim ChkBox As Excel.CheckBox
        Set ChkBox = ActiveSheet.CheckBoxes(Application.Caller)
        X = ChkBox.ShapeRange.AlternativeText
        With Range(ChkBox.LinkedCell).Offset(0, 1).Resize(X, 7).Interior
            If ChkBox.Value = xlOn Then
                 .ColorIndex = 16
           Else
                 .ColorIndex = xlColorIndexNone
            End If
        End With
End Sub

 

[Slywalker]

Oh yes! This is exactly what I were looking for! Thanks a bunch

Small side note, before I go changing the other 30 checkboxex alternative text to 1. Is there a way I can say "If nothing in alternate text, set X to 1"?

 

[Vbagreenhorn1]

Sub ToggleGrey()
    Dim ChkBox As Excel.CheckBox
        Set ChkBox = ActiveSheet.CheckBoxes(Application.Caller)
        If ChkBox.ShapeRange.AlternativeText = "" Then
            X = 1
        Else
            X = ChkBox.ShapeRange.AlternativeText
        End If
        With Range(ChkBox.LinkedCell).Offset(0, 1).Resize(X, 7).Interior
            If ChkBox.Value = xlOn Then
                 .ColorIndex = 16
               X = ChkBox.ShapeRange.AlternativeText
            Else
                 .ColorIndex = xlColorIndexNone
            End If
        End With
End Sub

 

[Slywalker]

*facepalm*
Now I feel like an idiot. Don't know why I expected some voodoo magic, instead of an If argument.

Thank you so much for all the help. I really appreciate it