How to make a table with only one cell to be selected?

G

ghnogueira

I know the title is kinda confusing, but I couldn't really think how to
explain in short words what I want.

I have these 2 column ranges (A2:A13 and B2:B13), and I want them to some
kind of selection boxes, where I can type something like an "X" to show
selection. But I want Excel to accept only ONE X per column, like if it was a
HTTP radio box, where you have like 5 different options, but can only choose
1 of them.

e.g.: when I type "x" on cell A3, and type again on cell A7, it
automatically deletes the x on cell A3, or something like that. It may not be
an "x", might be something else. But that's the idea.

Was it clear enough?
 
R

rpw

It sorta sounds like you have the choices in column B with the selection
indicator in column A.

I don't really know what you are trying to do here, but if I wanted the user
to select one (not multiple) of many possible options, I would use Excel's
"Name" feature. In cell B1 (or elsewhere where the list could be hidden from
the user) I would have a column header "Choices" with the choices listed
underneath:

B1: CHOICES
B2: Apples
B3: Oranges
B4: Lemons
B5: Limes
B6: Kiwis

Then I would place the cursor on 'choices' and click Insert/Name/Define.
When the Define Name window pops up, you can name the Name as "Choices" and
set the "Refers To" range as "=Sheet1!$B$2:$B$6". (Don't type in the
quotation marks)

Place the cursor in cell A2 and click Data/Validation. In the next popup
window on the Settings tab, select 'List' from the Allow drop-down and type
"=Choices" (without the quotation marks) in the Source field.

If these instructions are not enough for you, use Excel's help feature and
I'm sure you can find detailed, illustrated instructions for using the Name
feature.
 
V

vezerid

You can use the following VBA event procedure for this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:B13")) Is Nothing Then
v = Target.Value
col = Target.Column
Application.EnableEvents = False
Range(ActiveSheet.Cells(2, col), ActiveSheet.Cells(13,
col)).ClearContents
Target.Value = v
Application.EnableEvents = True
End If
End Sub

To install: Right click on the sheet tab. Choose View Code... This
takes you to the VBA IDE.
Paste the above code in the window. It should operate like you want
from this point on.

HTH
Kostis Vezerides
 
G

ghnogueira

Works like a charm man! REALLY REALLY thanks! Just a question: is there any
way to make it work like if I had hit enter after typing the "x"? Right now
when I type the X on the box I have to either press enter or select a
different column for the script to run. Just a "lazyness" question =)

But as I don't know VB programming I'd like to learn a bit... I was looking
your code, and I couldn't identify where it says that only the "x" can do the
selection, since I tried with other stuff and it didn't work (not that I want
to, I just tested... x works as I wanted). I also didn't get where it
specifies that I can have two X's, one in each column, but not 2 x's in the
same column, or how the x in the first column doesn't exclude the x in the
second column.

Thanks in advance!
 
G

ghnogueira

Okay, I think I had a slight idea... please tell me if I am right

Application.EnableEvents = False
Range(ActiveSheet.Cells(2, col), ActiveSheet.Cells(13, col)).ClearContents
Target.Value = v
Application.EnableEvents = True

That makes the cells on the range specified (I only can't get how it knows 2
and 13 means from A2 to B13, since you only use 1 value for the column, which
is col) forbid any changes, then be cleared, then it puts the char I typed on
the cell I typed it, and finally allow changes again
 
G

ghnogueira

Is there a way to make the selection be chosen by a mouse click instead of
typing?

Something like clicking on a desired cell makes it appear the X and
therefore select it just like if I had typed the X and hit enter.

Thanks :)
 
Top