Page 1 of 2 12 LastLast
Results 1 to 10 of 12
Like Tree1Likes

Toggle X On and Off

This is a discussion on Toggle X On and Off within the Excel Questions forums, part of the Question Forums category; Hello Everyone, Not an extremely needed tool but I feel that there will be some that will find this useful. ...

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Posts
    175

    Default Toggle X On and Off

    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 by miketurn; Mar 14th, 2017 at 12:14 AM.

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    15,131

    Default Re: Toggle X On and Off

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular
    Join Date
    Oct 2012
    Posts
    4,197

    Default Re: Toggle X On and Off

    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

  4. #4
    Board Regular
    Join Date
    Dec 2016
    Posts
    175

    Default Re: Toggle X On and Off

    @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

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    15,131

    Default Re: Toggle X On and Off

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,022

    Default Re: Toggle X On and Off

    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
    Michael M likes this.

  7. #7
    Board Regular
    Join Date
    Dec 2016
    Posts
    175

    Default Re: Toggle X On and Off

    @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.

  8. #8
    Board Regular
    Join Date
    Dec 2016
    Posts
    175

    Default Re: Toggle X On and Off

    @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 by miketurn; Mar 20th, 2017 at 08:49 PM.

  9. #9
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,022

    Default Re: Toggle X On and Off

    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.

  10. #10
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    620

    Default Re: Toggle X On and Off

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com