Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I toggle the value of a cell by clicking directly on the cell?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I toggle the value of a cell by clicking directly on the cell?
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 In article , steve-o wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I toggle the value of a cell by clicking directly on the cell?
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 "steve-o" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I toggle the value of a cell by clicking directly on the cell?
Thank you both. Your advice was very helpful and answered my question.
"steve-o" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
resetting last cell | Excel Discussion (Misc queries) | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions |