Link to home
Start Free TrialLog in
Avatar of SRMoxy
SRMoxyFlag for United States of America

asked on

MS excel how to have a cell be a check box where "click" puts an "x" in cell, or "click" again to clear it?

This has to be simple, but mine doesn't work!  Just want to "click" a cell to put an "x' -- and, "click" again to clear the cell?   Please help.....
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Put this code in the code for the specific sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If ActiveCell.Value = "" Then ActiveCell.Value = "X"

    If ActiveCell.Value = "X" Then ActiveCell.Value = ""

End Sub

Open in new window


This will apply to all cells on that sheet. I guess you will need to have it bound to a certain range.

Cheers
Rob H
Avatar of SRMoxy

ASKER

I can't get this to work ??  And I'll only want certain cells to be affected.  Any hints.....
Mine worked when I first wrote it but then stopped working after I had moved onto something else. Strange???

To affect only certain cells, you will have to set the target range. Numerous ways to do that:

Column and Row number of activecell within a set range,
Define Range area or specific cells.

Cheers
Rob H
Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If ActiveCell.Column = 2 And ActiveCell.Row > 2 And ActiveCell.Row < 10 Then
    
    If ActiveCell.Value = "" Then
    ActiveCell.Value = "X"
    Exit Sub
    Else
    End If

    If ActiveCell.Value = "X" Then
    ActiveCell.Value = ""
    Exit Sub
    Else
    End If
    Else
    Exit Sub
    End If

End Sub

Open in new window


This will only affect cells B3:B9

Cheers
Rob H
I think the reason why it wasn't working in the original code was because there was no break in the routine.

It was looking at the active cell and assessing whether blank and populating but then the next line was assessing whether populating and making blank - result = no change.

To see the working method clearer in the code, put a line feed in line 16 and it doesn't actually need the Exit Sub in line 17 because the next statement is End Sub anyway.

Cheers
Rob H

So amended code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If ActiveCell.Column = 2 And ActiveCell.Row > 2 And ActiveCell.Row < 10 Then
    
    If ActiveCell.Value = "" Then
    ActiveCell.Value = "X"
    Exit Sub
    Else
    End If

    If ActiveCell.Value = "X" Then
    ActiveCell.Value = ""
    Exit Sub
    Else
    End If
    
    Else
    End If

End Sub

Open in new window

Avatar of SRMoxy

ASKER

Sorry to be a pain in the butt !  (I increased point value)   I can't get it right ~  
My Workbook is "Sample Checklist.xls"  and Sheet is "Insp Checklist"
(There are Yes/No questions down the list and I'd like them to just click an "X" to answer
beside either the "Yes" cell or "No" cell)
The cells I want to "x"  are  R12  &   T12  ..... thru  R32 & T32   (nothing in between to interfere
if we want to do a "range" )     I can't figure out what I'm doing wrong, so you may have to
spell it out.   Thanks for your time.......
Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If ActiveCell.Column = 18 Or ActiveCell.Column = 20 And ActiveCell.Row > 11 And ActiveCell.Row < 33 Then
    
    If ActiveCell.Value = "" Then
    ActiveCell.Value = "X"
    Exit Sub
    End If

    If ActiveCell.Value = "X" Then
    ActiveCell.Value = ""
    Exit Sub
    End If
    
    Else
    End If

End Sub

Open in new window


The next step would be to check that the user hasn't entered in both!!!

Cheers
Rob H
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The beast has evolved, new code to now swap between columns to avoid duplicate entries:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If ActiveCell.Column = 18 And ActiveCell.Row > 11 And ActiveCell.Row < 33 Then Flag = True
    
        If Flag = True Then
            AC = ActiveCell.Value
            PCC = ActiveCell.Column + 2
            PCR = ActiveCell.Row
                        
            If ActiveCell.Value = "" Then
                ActiveCell.Value = "X"
                Cells(PCR, PCC) = ""
                Exit Sub
            End If

            If ActiveCell.Value = "X" Then
                ActiveCell.Value = ""
                Cells(PCR, PCC) = "X"
                Exit Sub
            End If
    
        End If
            
    If ActiveCell.Column = 20 And ActiveCell.Row > 11 And ActiveCell.Row < 33 Then Flag = True

        If Flag = True Then
            AC = ActiveCell.Value
            PCC = ActiveCell.Column - 2
            PCR = ActiveCell.Row
                        
            If ActiveCell.Value = "" Then
                ActiveCell.Value = "X"
                Cells(PCR, PCC) = ""
                Exit Sub
            End If

            If ActiveCell.Value = "X" Then
                ActiveCell.Value = ""
                Cells(PCR, PCC) = "X"
                Exit Sub
            End If
    
        End If

End Sub

Open in new window


However, still works on cursor movement as well; tried with double click which gets over the cursor key movement but leaves the cell in edit mode, ie doesn't confirm entry.

Tried with control buttons and I kept going round in circles linking pairs of buttons to the same cell but then when I created a new button, it linked to the previous cell so changing that value and on.

Cheers
Rob H
Previously suggested having Data Validation in the cell so that user can choose yes or No from a list. Initially said this would be 2 clicks but it would actually be 3, one to select cell, one to select dropdown and one to choose option.

I have got that down to 2 if this is an option. Basically, using the same technique as above recognising cell selection the dropdown list appears.

This would overcome the cursor key movements because just moving into the cell would only show the dropdown, it wouldn't create the entry.  Further use of cursor keys (Up or Down) and Enter would then choose and create the entry.

See attached with options.  Yellow cells in Column U have Data Validation set, columns R & T will change between X and blank on selection. The two input options are not linked but I have set them to same results.

Cheers
Rob H
x-on-click.xls
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
For the data validation to be activated on selection, the only line in the macro that you need is the last line.

Adjust the "ActiveCell.Column = 21" to reflect the number of the column used.

Cheers
Rob H
Avatar of SRMoxy

ASKER

Robhenson went above and beyond.  Really appreciate the help.