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

SRMoxy
SRMoxy used Ask the Experts™
on
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.....
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
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

Author

Commented:
I can't get this to work ??  And I'll only want certain cells to be affected.  Any hints.....
Rob HensonFinance Analyst

Commented:
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
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Rob HensonFinance Analyst

Commented:
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
Rob HensonFinance Analyst

Commented:
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

Rob HensonFinance Analyst

Commented:
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

Author

Commented:
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.......
Rob HensonFinance Analyst

Commented:
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
Finance Analyst
Commented:
I am assuming that column R is Yes and Column T is No (or Vice Versa).

Another downside to the above, moving between cells with the cursor keys also triggers the event to change the value.

Would a dropdown in a single column with options Yes or No be an option. This would involve two clicks, one to enable the drop down and one to select the option.

This can be easily done using Data Validation. On the Data Menu > Data Validation.

In the window Settings tab
Allow choose List from the dropdown.
In the Source box type Yes,No
Ensure the Ignore Blank and inCell dropdown boxes are ticked.

The other two tabs in the popup allow you to change what users see when the cell is selected and what happens if a false entry is made.

Click OK to accept.

To allow simple typed entry rather than clicked entry. Assuming the one column is column R, in R10 type Yes and R11 type No. Format as white on white if so required. The single keystroke of Y or N in the cells below will then generate the Yes or No. With this you could then disable the In Cell dropdown mentioned above but the validation of the cells will still only accept Yes or No.

Another option would be to create a raft of radio control buttons or tick boxes with each Yes/No pair linked to the same cell but with Yes button set to create value True and No button set to create value False. Clicking one would then automatically change the other and your final check/counts of Yes/No would be correct.

Cheers
Rob H
Rob HensonFinance Analyst

Commented:
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
Rob HensonFinance Analyst

Commented:
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
Commented:
Thanks a bunch for all your work on this ~  I think I'll take your suggestion and stay with
the drop down box and they can just suffer through a few extra clicks.    
Appreciate the help!  
Rob HensonFinance Analyst

Commented:
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

Author

Commented:
Robhenson went above and beyond.  Really appreciate the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial