Simulating Checkboxes In The Cell Itself

[gaming_mouse]

My search turned up an old thread describing exactly what I want, but the solution was emailed to the poster rather than posted:

http://www.mrexcel.com/board2/viewtopic.php?t=176712&highlight=checkbox+create

Basically, I want to be able to click on a cell and have it become "checked" and "unchecked" with an "X" or some other mark, but I don't want to use a form checkbox, as those add file size and seem cludgy. Can anyone tell me how to do this?

Thanks,
gm

 

[anthonya2369]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    If Target.Value = "X" Then
        Target.Value = ""
    Else
        Target.Value = "X"
    End If
End If
End Sub

The Column = 1 is for what column will be looking for the X. Click a cell in the column you specify after pasting this code to the sheet you want it and see what happens.

 

[gaming_mouse]

The Column = 1 is for what column will be looking for the X. Click a cell in the column you specify after pasting this code to the sheet you want it and see what happens.

thanks, anthony.

one question. when you have a column number hardcoded into VBA code like that, will it change if you insert another column, or do you have to manually adjust your code in that case?

Thanks,
gm

 

[Fausto]

Hello

Another option would be to insert this into the Thisworkbook section of your project and edit if necessary.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Target.Address = Sheets("Sheet1").Range("A1").Address Then
    Cancel = True
    If Target.Value = "X" Then
        Target.Value = ""
    Else
        Target.Value = "X"
    End If
End If
End Sub

This way, the X will only change if the specified cell is double clicked, furthermore, the macro will only run when the sheet is double clicked therefore not running the macro everytime a selection change is made.

Hope this helps, and it is what you're looking for.

 

[Fausto]

Hi again

Here is a better option:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Set intsect = Application.Intersect(Target, Range("A1:A100"))
If intsect Is Nothing Then
    Exit Sub
Else
    Cancel = True
    If Target.Value = "X" Then
        Target.Value = ""
    Else
        Target.Value = "X"
    End If
End If
End Sub

Effectively checks to see if the double clicked cell is in the range A1:A100, if so, runs the macro.

Hope this helps

 

[RAM]

but I don't want to use a form checkbox, as those add file size and seem cludgy.

Try: View - Toolbar and choose Control Toolbox and insert the Check Box. Can't tell you, though, if they're less in size. I wouldn't really believe it.

RAM

Edit: My bad. I misunderstood the question and withdraw my comment.

 

[gaming_mouse]

Hi again

Here is a better option:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Set intsect = Application.Intersect(Target, Range("A1:A100"))
If intsect Is Nothing Then
    Exit Sub
Else
    Cancel = True
    If Target.Value = "X" Then
        Target.Value = ""
    Else
        Target.Value = "X"
    End If
End If
End Sub

Effectively checks to see if the double clicked cell is in the range A1:A100, if so, runs the macro.

Hope this helps

Fausto,

Thanks for that solution. Can you explain what is happening with "Cancel"?

Thanks,
gm

 

[gaming_mouse]

but I don't want to use a form checkbox, as those add file size and seem cludgy.

Try: View - Toolbar and choose Control Toolbox and insert the Check Box. Can't tell you, though, if they're less in size. I wouldn't really believe it.

RAM

No, I want a true in-cell checkbox. thanks for the thought, tho.

 

[RAM]

Edit: My bad. I misunderstood the question and withdraw my comment.

 

[Fausto]

Hello gaming_mouse

Effectively the Cancel = True is telling Excel not to open the cell for editing. If you comment out that line or delete it all together you will see that if you double click the cells in the range A1:A100, the cell will still be open for editing after double clicking. With my solution, the X is placed in the cell without having to press enter everytime you double click.

Reading this reply, I can see that it is not very clear. I suggest you try it for yourself to see what I mean.

Hope this answers your question.