Selection.Borders Help

[jkd_1]

Hi - to preface I am very new to VBA so hopefully this makes sense. I am trying to write a code that allows for a keystroke to toggle a cell border on and off. For example, by pressing ctrl + alt + K would add a top border. Repeating that keystroke would remove it. I am able to write the application.Onkey portion. I am struggling with the Selection.borders portion of the code. Happy to provide more detail if need be, hopefully this makes sense!

 

[Joe4]

Welcome to the Board!

Try something like this code:

Sub ToggleTopBorder()
    
    Dim cell As Range
    
    For Each cell In Selection
        If cell.Borders(xlEdgeTop).LineStyle = xlNone Then
            cell.Borders(xlEdgeTop).LineStyle = xlContinuous
        Else
            cell.Borders(xlEdgeTop).LineStyle = xlNone
        End If
    Next cell
    
End Sub

and then you can assign a keyboard shortcut to this code to run it (you cannot do CTL+ALT+K, but you can do CTL+SHIFT+K).

Does that work for you?

 

[Rick Rothstein]

Sub ToggleTopBorder()
  
    Dim cell As Range
  
    For Each cell In Selection
        If cell.Borders(xlEdgeTop).LineStyle = xlNone Then
            cell.Borders(xlEdgeTop).LineStyle = xlContinuous
        Else
            cell.Borders(xlEdgeTop).LineStyle = xlNone
        End If
    Next cell
  
End Sub

Just noting the red highlighted lines of code can be replace by this single line of code...

cell.Borders(xlEdgeTop).LineStyle = xlContinuous + xlNone - cell.Borders(xlEdgeTop).LineStyle

Note: The above assumes the top borders will only ever be continuous or none to begin with.

 

[jkd_1]

Welcome to the Board!

Try something like this code:

Sub ToggleTopBorder()
   
    Dim cell As Range
   
    For Each cell In Selection
        If cell.Borders(xlEdgeTop).LineStyle = xlNone Then
            cell.Borders(xlEdgeTop).LineStyle = xlContinuous
        Else
            cell.Borders(xlEdgeTop).LineStyle = xlNone
        End If
    Next cell
   
End Sub

and then you can assign a keyboard shortcut to this code to run it (you cannot do CTL+ALT+K, but you can do CTL+SHIFT+K).

Does that work for you?

Apologies for the delay here, this project got put on the backburner a bit as other projects took priority. This works almost perfectly, however, I have one issue. I have slightly modified to code have the Dim be "selection" instead of a "cell" (so when a selection of multiple rows is highlighted it doesn't apply top borders to every row, just the top of the selection) and that seemed to work just fine, but when I try to use this, it only works for every other row in the sheet. It seems as though it is recognizing every other row as the bottom of the previous row. Ideally, I would like to be able to add a top border to any cell or range that is selected. Hopefully, this makes sense. Happy to explain further if helpful.

 

[Joe4]

Try this version:

Sub ToggleTopBorder()
    
    With Selection
        If .Borders(xlEdgeTop).LineStyle = xlNone Then
            .Borders(xlEdgeTop).LineStyle = xlContinuous
        Else
            .Borders(xlEdgeTop).LineStyle = xlNone
        End If
    End With
    
End Sub

 

[jkd_1]

Try this version:

Sub ToggleTopBorder()
  
    With Selection
        If .Borders(xlEdgeTop).LineStyle = xlNone Then
            .Borders(xlEdgeTop).LineStyle = xlContinuous
        Else
            .Borders(xlEdgeTop).LineStyle = xlNone
        End If
    End With
  
End Sub

That's perfect. Exactly what I was looking for thanks

 

[Joe4]

You are welcome.
Glad it worked for you!

 

[Joe4]

Your code can be reduced to a one-liner...

No doubt...

But I find it easier to maintain code I can easily understand.