Select only one from five choices

[PCTech]

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

 

[mikerickson]

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.

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]

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]

There is no initializing needed.
Is it in the worksheet's code sheet and not a normal or ThisWorkbook's code module?

 

[gardnertoo]

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:

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]

Another way:
you can to try this 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]

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.

 

[galileogali]

Very good, Boller!!

 

[PCTech]

Boller, exactly what I wanted. Can you tell me how to make it happen between rows 8 and 28 only?

 

[Boller]

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