Can I toggle the value of a cell by clicking directly on the cell?

[Guest]

Hi :

I have about 700 items on a list. I want to create an excel sheet in which
our customers would go through the 700 items on the list and click off which
ones of these items they want. The choices are binary -either they want the
item, or they don't want the item.

Normally for this type of thing I would create a few checkboxes through the
checkbox control and link each checkbox to a cell. However, becuase the
number of items is so large, I don't think I would have the time or patience
to create 700 checkboxes and then associate them with cells.

So the next thing I did was create a data validation of a list where users
could go through each item, click on a cell, then see a drop down menu within
the cell with two options "X" and "" (Blank). However, my users told me they
don't want to click two times to make a selection (once to get to the cell,
and twice to select a choice within the drop down menu)

My question is- is there a way to replicate the feel of a checkbox within an
excel cell where one click on the cell sets the cell to a certain value , say
"X", and another click on the cell sets it to a different value, say "" .

Any help or guidance you could give me is greatly appreciated.

Thanks in advance,


steve-0

 

[JE McGimpsey]

One way:

I'd recommend using a _BeforeDoubleClick event macro (so the user has to
doubleclick the cell). You could use a _SelectionChange macro, but then
the toggling would occur if the user used the arrow keys, Tab, Enter,
etc.

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code). This assumes that the list is in column A and the
cells to click are in Column B...

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With Target
If Not Intersect(.Cells, Range("B1:B" & Range("A" & _
Rows.Count).End(xlUp).Row)) Is Nothing Then
.Value = IIf(.Value = "", "X", "")
Cancel = True
End If
End With
End Sub

 

[Trevor Shuttleworth]

You could try a selection change macro:

' Sheetx Class Module ... out this code behind the sheet you are working
with

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Columns("B:B"), Target) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then
Target.Value = "X"
Else
Target.Value = ""
End If

End Sub

With this routine, if you select a single cell in column B, it will toggle
between "X and blank.

Can be a bit of a pain if you select the wrong cell ... but just select a
cell outside the column and go back

Regards

Trevor

 

[Guest]

Thank you both. Your advice was very helpful and answered my question.