Excel "validation" question

[Scott24x7]

All,
I have a spreadsheet that I use that is largely a check list. For a number of reasons, I don't want to use the Excel "Check box" object. (Mostly because it annoys me...)
I thought though I might be able to "emulate" the behavior by sizing cells to a specific size, and then click on them. The idea is, if the cell is empty, it places an X character in it. If the cell already has an X character in it, and it gets clicked, it would make the Cell Empty again.
Usually I like this kind of thing "In Cell", but I can accpet a Macro solution to this one if it is the only way to do it.
Thanks in advance.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Fenrirshowl]

I'd guess macro, so this would be the wrong forum.

The following may work

The following may do what you are after, if you do a case for each cell you want to work as a checkbox...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = ActiveCell.Address
Select Case x
    Case "$E$1"
        If ActiveCell = "X" Then
            ActiveCell = ""
        Else
            Range(x) = "X"
        End If
End Select
End Sub

 

[Fenrirshowl]

I'd guess macro, so this would be the wrong forum.

But...the following may do what you are after (if you do a case for each cell you want to work as a checkbox)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
x = ActiveCell.Address
Select Case x
    Case "$E$1"
        If ActiveCell = "X" Then
            ActiveCell = ""
        Else
            Range(x) = "X"
        End If
End Select
End Sub

Make sure you put it in the sheets code rather than a general module (but I guess you knew that).

 

[Scott24x7]

Yeah, that doesn't really work for me... need something more dynamic. I'm okay with a function that does something like:

=CheckMe()

That when the cell is clicked, (Regardless of where I'm calling the CheckMe() function) It checks the value, and then makes it either X or " ".


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[GlennUK]

I'd also recommend a macro, although I'd choose the double-click as being the trigger. This example toggles a cell as TRUE/FALSE, if the column is column D ( i.e. column 4 ):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column = 4 Then
    If Target = "" Then
        Target = True
    ElseIf Not Target Then
        Target = True
    Else
        Target = False
    End If
  End If
End Sub

Cheers, Glenn.

Beauty is in the eye of the beerholder.