Toggle X On and Off

miketurn

Active Member
Joined
Dec 8, 2016
Messages
268
Hello Everyone,

Not an extremely needed tool but I feel that there will be some that will find this useful.

I was able to record the following two macros below (seems like so much coding for something so simple).
The first one adds an X (using diagonal Borders lines) to selected cells and the second one removes this X from selected cells.

I was wondering if someone would be able to combine these two into one macro using "IF" syntax.
So if there isn't an X this macro will add it to the selected cell(s) and if an X does exist in the selected cell(s) remove it.

I tried adding the "if" and the "else" myself but yeah I don't know what I am doing :)


Adds X
Code:
Sub AddX() 
    With Selection.Borders(xlDiagonalDown) 
        .LineStyle = xlContinuous 
        .Weight = xlMedium 
        .ColorIndex = xlAutomatic 
    End With 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone 
    Selection.Borders(xlEdgeTop).LineStyle = xlNone 
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone 
    Selection.Borders(xlEdgeRight).LineStyle = xlNone 
    With Selection.Borders(xlDiagonalDown) 
        .LineStyle = xlContinuous 
        .Weight = xlMedium 
        .ColorIndex = xlAutomatic 
    End With 
    With Selection.Borders(xlDiagonalUp) 
        .LineStyle = xlContinuous 
        .Weight = xlMedium 
        .ColorIndex = xlAutomatic 
    End With 
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone 
    Selection.Borders(xlEdgeTop).LineStyle = xlNone 
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone 
    Selection.Borders(xlEdgeRight).LineStyle = xlNone 
End Sub


Removes X
Code:
Sub RemoveX() 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    With Selection.Borders(xlDiagonalUp) 
        .LineStyle = xlContinuous 
        .Weight = xlMedium 
        .ColorIndex = xlAutomatic 
    End With 
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone 
    Selection.Borders(xlEdgeTop).LineStyle = xlNone 
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone 
    Selection.Borders(xlEdgeRight).LineStyle = xlNone 
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone 
    Selection.Borders(xlEdgeTop).LineStyle = xlNone 
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone 
    Selection.Borders(xlEdgeRight).LineStyle = xlNone 
End Sub
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Mike
Don't have Excel at the moment, but both your codes could be shortened thus...

Code:
Sub AddX()
    Selection.Borders.LineStyle = xlNone
    With Selection.Borders(xlDiagonalDown)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlDiagonalUp)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
End Sub


Code:
Sub RemoveX()
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
End Sub
 
Upvote 0
Try this, where you will have the X in an "out of the way cell" instead of A1.
So, if selection has X, then copy the blank, elseif the selection is blank, then copy the X cell.

Howard

Code:
Sub MY_X_NO_Xx()

If Selection.Borders(xlDiagonalUp).LineStyle = xlNone Then
   Range("A1").Copy Selection
 ElseIf ActiveCell.Borders(xlDiagonalUp).LineStyle <> xlNone Then
   Range("A2").Copy Selection
End If

End Sub
 
Upvote 0
@Michael M
Thank You for providing the shortened coding, I have not tried them out yet but it is good to see they could be shortened a bit.

@L Howard
Thank You for providing a macro, but unfortunately I was not able to get this to work the way I was thinking.
I might not be using it correctly, but I think I understand what you were going for, where it places an actual letter "X" from a selected cell and keeps on copying that cell. A little different than what I was looking for, I also couldn't get it to remove the "X" when selecting a cell that already contains one.
I thank you again for your help, but I was hoping to be able to have the "X" be made out of the two diagonal border lines, this also allows you to X out cells that contain data without losing the data.

If either of you or anyone else has any ideas of how to combine the two macros into one please let me know.
Thank You
 
Upvote 0
Try this,,previously posted by shg !!

Code:
Sub AddX()
Dim i As Integer
If Selection.Borders(xlDiagonalDown).LineStyle = xlNone Then
        For i = xlDiagonalDown To xlDiagonalUp
            With Selection.Borders(i)
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
        Next i
Else
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
selection.Borders(xlDiagonalUp).LineStyle = xlNone
End If
End Sub
 
Upvote 0
You could take a different approach.
First create a Style (called Crossed).
Then use this macro to toggle it on and off.
Code:
Sub ToggleStyle()
    With Selection
        If .Cells(1, 1).Style = "Crossed" Then
            .Style = "Normal"
        Else
            .Style = "Crossed"
        End If
    End With
End Sub

You could either make the style by hand or use this macro. (Run it once.)
Code:
Sub MakeStyleCrossed()
    With ActiveWorkbook.Styles.Add("Crossed")
        .IncludeNumber = False
        .IncludeFont = False
        .IncludeAlignment = False
        .IncludeBorder = True
        .IncludePatterns = False
        .IncludeProtection = False
        .Borders(xlLeft).LineStyle = xlNone
        .Borders(xlRight).LineStyle = xlNone
        .Borders(xlTop).LineStyle = xlNone
        .Borders(xlBottom).LineStyle = xlNone
        With .Borders(xlDiagonalDown)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlDiagonalUp)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
        End With
    End With
End Sub
 
Upvote 0
@Michael B
Thank You for providing this and if I understand your post above, also Thank You to @SHG for creating this.

I tested this macro out and for my purposes it works good enough for me, but I did notice a couple of things that maybe other future users of it should be aware of.

1.) (This one I guess is kind of common sense and probably cannot be fixed) If you use this macro to "X" out multiple cells and then decide you want to add a border around this group of cells, they will all be removed because the X is made out of border diagonal lines. You have to make sure you add the cell outline borders before you add any "X"s and then they will work properly.

2.) The border line weight can get a little weird looking when applied to these diagonal lines.
xlThin = can be a little too thin and un-noticable and xlMedium = can get a little pixelated if cell widths are wide.
There is quite a difference between these two widths, but unfortunately there is not a value in between them.

3.) (No one would probably use this, but just something I noticed) If you select a group of cells (some containing "X"s and some blank) and try to invert the selection where the "X"s become blanks and the blanks become "X"s that will not work.
It will make them all "X"s or all blanks depending on what the top cell of the selection is.

Again, the tools works fine for my purposes, I just figured I would share some things I came across while testing, just in case future users of this macro are unaware.

Thank You again @Michael B for posting this for me.
 
Upvote 0
@Mikerickson
Thank You very much for providing this, I would not have thought of this.
Is there a specfic benefit for doing it one way or the another?
I will have to try this out with the items I mentioned above and see if any of these react differently.

Thank You again for your help
 
Last edited:
Upvote 0
One benefit is that you can change the definition of style Crossed (from native excel) when you decide to and not have to fuss with the code.
 
Upvote 0
Or .. if you are ok with using an " X " .... and a simple double-click to add or delete :

Code:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick( _
            ByVal Target As Range, Cancel As Boolean)
    Dim rInt As Range
    Dim rCell As Range


    Set rInt = Intersect(Target, Range("A:Z"))
    If Not rInt Is Nothing Then
        For Each rCell In rInt
            If rCell.Value = "X" Then
                rCell.Value = ""
            Else
                rCell.Value = "X"
                rCell.HorizontalAlignment = xlCenter
            End If
        Next
    End If
    Set rInt = Nothing
    Set rCell = Nothing
    Cancel = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top