Removing thick border when activex checkbox is not checked

Question

I can not figure out how to remove the border when the activex check box is not checked. I have been messing with the code but either get errors or unwanted results.

This is for work so I can not upload the sheet but I can answer more questions and upload more screenshots if needed. I am still very new to using VBA and ActiveX controls and trying to learn more; so I would appreciate the help!

This is where i am at on the code right now;

Code#1

Private Sub CheckBox3_Click()
    If CheckBox3.Value = True Then
        Me.CommandButton3.Visible = True
        Me.CommandButton4.Visible = True
        Me.CommandButton5.Visible = True
        Me.CommandButton6.Visible = True
        Me.CommandButton7.Visible = True
        Me.CommandButton8.Visible = True
        Me.CommandButton9.Visible = True
        Range("B4:B14", "C4:C14").BorderAround _
          LineStyle:=xlContinuous, _
          Weight:=xlThick, _
          Color:=RGB(0, 0, 0)
  Else: CheckBox3.Value = False
        Me.CommandButton3.Visible = False
        Me.CommandButton4.Visible = False
        Me.CommandButton5.Visible = False
        Me.CommandButton6.Visible = False
        Me.CommandButton7.Visible = False
        Me.CommandButton8.Visible = False
        Me.CommandButton9.Visible = False
    End If
    Application.EnableEvents = True
End Sub

I have tried a few different codes but they have failed;

Code#2

Private Sub CheckBox3_Click()
    If CheckBox3.Value = True Then
        Me.CommandButton3.Visible = True
        Me.CommandButton4.Visible = True
        Me.CommandButton5.Visible = True
        Me.CommandButton6.Visible = True
        Me.CommandButton7.Visible = True
        Me.CommandButton8.Visible = True
        Me.CommandButton9.Visible = True
        Range("B4:B14", "C4:C14").BorderAround _
          LineStyle:=xlContinuous, _
          Weight:=xlThick, _
          Color:=RGB(0, 0, 0)
  Else: CheckBox3.Value = False
        Me.CommandButton3.Visible = False
        Me.CommandButton4.Visible = False
        Me.CommandButton5.Visible = False
        Me.CommandButton6.Visible = False
        Me.CommandButton7.Visible = False
        Me.CommandButton8.Visible = False
        Me.CommandButton9.Visible = False
        With .Range("H4:I7")
                .Interior.ColorIndex = xlNone
                .Font.Color = vbWhite
                .Borders.LineStyle = xlNone
                .EntireRow.Hidden = False ' ?
    End If
    Application.EnableEvents = True
End Sub

This one works, just changing the color of the border, but it screws up the border on the bottom of row 3.

Code#3

Private Sub CheckBox3_Click()
    If CheckBox3.Value = True Then
        Me.CommandButton3.Visible = True
        Me.CommandButton4.Visible = True
        Me.CommandButton5.Visible = True
        Me.CommandButton6.Visible = True
        Me.CommandButton7.Visible = True
        Me.CommandButton8.Visible = True
        Me.CommandButton9.Visible = True
        Range("B4:B14", "C4:C14").BorderAround _
          LineStyle:=xlContinuous, _
          Weight:=xlThick, _
          Color:=RGB(0, 0, 0)
  Else: CheckBox3.Value = False
        Me.CommandButton3.Visible = False
        Me.CommandButton4.Visible = False
        Me.CommandButton5.Visible = False
        Me.CommandButton6.Visible = False
        Me.CommandButton7.Visible = False
        Me.CommandButton8.Visible = False
        Me.CommandButton9.Visible = False
         Range("B4:B14", "C4:C14").BorderAround _
          LineStyle:=xlContinuous, _
          Weight:=xlThick, _
          Color:=RGB(250, 250, 250)
    End If
    Application.EnableEvents = True
End Sub

Answer 1

• Remove the border of B4:C14
• Restore the top border of B4:C4

    Range("B4:C14").Borders.LineStyle = xlNone
    With Range("B4:C4").Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = RGB(0, 0, 0)
        .Weight = Range("B3").Borders(xlEdgeTop).Weight
        ' .Weight = xlThick ' or xlMedium, modify as needed
    End With

Answer 2

Alternatively using iteration

    Dim i As Long
    For i = 3 To 9
       Me.OLEObjects("CommandButton" & i).Visible = Me.CheckBox3.Value
    Next

Answer 3

To set a border, you can (should) specify 3 parameters: LineStyle, Weight, and Color. To reset a a border, set the LineStyle to xlNone, but don't modify Weight or Color after setting the LineStyle, else you will get a (thin) border.

We have 6+2 Borders in Excel:
Left, Top, Right, Bottom, Diagonal Up, Diagonal Down.
If we have more that one row/column, you can additionally set Inside Horizontal and Inside Vertical.

Each of this Border can and needs to be set individually. In VBA, you use the Borders-property of a Range, eg activeCell.Borders(xlLeft). There is one shortcut: When you want to draw a complete border around a range, you can use the method BorderAround.

Now one additional thing you need to know is that 2 adjacent cells share the same border. Range("C3").Borders(xlBottom) is the same as Range("C4").Borders(xlTop). This is the reason your bottom border of B3:C3 disappears when you use BorderAround on Range("B4:C14").

The solution @taller proviced redraw this bottom border. I suggest a method to draw only the left, bottom and right border and leave the bottom border of B3:C3 untouched. I also compacted your code:

Private Sub CheckBox3_Click()
    Dim show As Boolean
    show = Me.CheckBox3.Value
    ' Show/hide buttons
    Me.CommandButton3.Visible = show
    Me.CommandButton4.Visible = show
    Me.CommandButton5.Visible = show
    Me.CommandButton6.Visible = show
    Me.CommandButton7.Visible = show
    Me.CommandButton8.Visible = show
    Me.CommandButton9.Visible = show

    ' Set border 
    Dim r As Range
    Set r = Range(Me.CommandButton3.TopLeftCell, Me.CommandButton9.BottomRightCell)        
    setBorder r.Borders(xlEdgeLeft), show
    setBorder r.Borders(xlEdgeBottom), show
    setBorder r.Borders(xlEdgeRight), show
End Sub

Sub setBorder(b As Border, show As Boolean)
    With b
        .Weight = xlThick
        .ColorIndex = 0
        ' Important: Do this as last command.
        .linestyle = IIf(show, xlContinuous, xlNone)
    End With
End Sub