excelvbacheckboxactivexexcel-2016

Removing thick border when activex checkbox is not checked


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.

Screenshot of checked with code #1

Screenshot of unchecked with code #1

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

Solution

  •     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