Select only one from five choices

PCTech

Board Regular
Joined
Mar 24, 2005
Messages
215
I'm trying to find a way to make the user select only one of five columns in a row. What I’m trying to get is a ranking of 1 to 5. The user will click a cell and it will fill in or the user puts an X in it. In row 8 I want to select A through E but only one of them. If the user changes their mind and clicks another cell, the first cell clears. I hope you can understand what I'm trying to do.

Thanks
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,318
This routine will allow the user to make only one entry in columns A-E.
Put this in the code module for the sheet in question.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim temp As Variant
If Target.Count = 1 Then
    If Target.Column < 9 Then
        temp = Target
        Application.EnableEvents = False
        Target.EntireRow.Range("a1:e1").ClearContents
        Target = temp
        Application.EnableEvents = True
    End If
End If
End Sub
 

PCTech

Board Regular
Joined
Mar 24, 2005
Messages
215
It is not working. I can put anyting in A, B, C, D, or E at the same time.
It there something I need to enable or disable?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,318
There is no initializing needed.
Is it in the worksheet's code sheet and not a normal or ThisWorkbook's code module?
 

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
You can create five OptionButtons and give all five the same GroupName in the properties menu. This links all five together and allows only one at a time to be selected. If the user changes his mind and selects a second button, the first one becomes de-selected. You can then code each OptionButton to place a unique value in the cell of your choice, or do whatever else you want. An example I use:
Code:
Private Sub OptionButton1_Click()
Cells(5, 8).Value = "MECH"
End Sub

Private Sub OptionButton2_Click()
Cells(5, 8).Value = "I&E"
End Sub

Private Sub OptionButton3_Click()
Cells(5, 8).Value = "MGMNT"
End Sub

Private Sub OptionButton4_Click()
Cells(5, 8).Value = "CONT"
End Sub

Private Sub OptionButton5_Click()
Cells(5, 8).Value = "ALL"
End Sub
This changes the text string in cell 5,8 depending on which of the OptionButtons was last clicked.
 

galileogali

Well-known Member
Joined
Oct 14, 2005
Messages
748
Another way:
you can to try this code:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Range("rngSelect"), Target) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Dim cel As Range
Application.ScreenUpdating = False

For Each cel In Range("rngselect")
If cel.Column <> Target.Column Then cel.ClearContents
Next


End Sub

GALILEOGALI
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [A:E]) Is Nothing Then
    If Target.Cells.Count <> 1 Then
        MsgBox "Only one cell at a time can be selected in columns A to E"
        Application.EnableEvents = False
        Cells(Target.Row, 6).Select
        Application.EnableEvents = True
    Else
        Cells(Target.Row, 1).Resize(, 5).ClearContents
        Target = "X"
    End If
End If
End Sub

A possible problem with this code is that if you drag/drop some value(s) into A:E, the value(s) dragged/dropped in A:E will remain.

Post again if this is likely to be a problem.
 

PCTech

Board Regular
Joined
Mar 24, 2005
Messages
215
Boller, exactly what I wanted. Can you tell me how to make it happen between rows 8 and 28 only?
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [A8:E28]) Is Nothing Then
    If Target.Cells.Count <> 1 Then
        MsgBox "Only one cell at a time can be selected in A8:E28"
        Application.EnableEvents = False
        Cells(Target.Row, 6).Select
        Application.EnableEvents = True
    Else
        Cells(Target.Row, 1).Resize(, 5).ClearContents
        Target = "X"
    End If
End If
End Sub

Please bear in mind that this code does not handle :
- dragging/dropping
- inserting/deleting columns
- inserting/deleting cells
 

Forum statistics

Threads
1,181,420
Messages
5,929,801
Members
436,696
Latest member
Mr Rice

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top