How can I hide different sets of ActiveX checkboxes in Excel based on two variables?

Question

I'm really new at VBA, so I apologize. Hopefully I can describe well enough. I recently changed a section of my spreadsheet from an area I can completely white out with conditional formatting to an area that is text (which I can white out), but also a row of 13 Active X checkboxes. This pick shows a setup where box 13 should not be shown. example

When I white out now, obviously the checkboxes will not do the same. The main problem lies in that I need certain checkboxes shown based on the value in cell K12 and the value in Z77. If Z77="N", then all the boxes need to be hidden, no matter what K12 is. If Z77="Y" and K12="B", then checkboxes 5-13 need to hide. If Z77="Y" and K12="D", then only box 13 needs to hide. If Z77="Y" and K12="P", then checkboxes 8-12 need to hide.

And as long as I have you, what would recommend as the best way to learn VBA to be?

I couldn't find anything that suited my needs.

Answer 1

I am uncertain about the contents of your sheet. My assumption is that all the checkboxes have been created in a sequential manner. Please try it.

Sub demo()
    pat = Split("1 1 1 1 1 1 1 1 1 1 1 1 1")
    If [Z77] = "N" Then
        pat = Split("0 0 0 0 0 0 0 0 0 0 0 0 0")
    ElseIf [Z77] = "Y" Then
        If [K12] = "B" Then
            pat = Split("1 1 1 1 0 0 0 0 0 0 0 0 0")
        ElseIf [K12] = "D" Then
            pat = Split("1 1 1 1 1 1 1 1 1 1 1 1 0")
        ElseIf [K12] = "P" Then
            pat = Split("1 1 1 1 1 1 1 0 0 0 0 0 1")
        End If
    End If
    For i = 1 To 13
        ActiveSheet.OLEObjects(i).Visible = (pat(i - 1) = 1)
    Next
End Sub

---

Q: I tried making it so that Z77 needed to be Y and K11 needed to be G, but couldn't get it to work. A: It seems like "K11" in your comment is a typo. I have updated the code as follows:

• Added a new branch to check if [K12] equals "G"
• The subroutine (For...Next) will not do nothing if the values for [Z77] and [K12] do not match the expected patterns.

Sub demo2()
    Dim pat
    If [Z77] = "N" Then
        pat = Split("0 0 0 0 0 0 0 0 0 0 0 0 0")
    ElseIf [Z77] = "Y" Then
        If [K12] = "B" Then
            pat = Split("1 1 1 1 0 0 0 0 0 0 0 0 0")
        ElseIf [K12] = "D" Then
            pat = Split("1 1 1 1 1 1 1 1 1 1 1 1 0")
        ElseIf [K12] = "P" Then
            pat = Split("1 1 1 1 1 1 1 0 0 0 0 0 1")
        ElseIf [K12] = "G" Then
            pat = Split("1 1 1 1 1 1 1 1 1 1 1 1 1")
        End If
    End If
    If VBA.IsArray(pat) Then
        For i = 1 To 13
            ActiveSheet.OLEObjects(i).Visible = (pat(i - 1) = 1)
        Next
    End If
End Sub