Survey with radio-button-like range behavior

  • Thread starter Thread starter Laurent
  • Start date Start date
L

Laurent

I am developping a simple survey with Excel. For each question in column A,
user has to answer on a scale from 1 to 5. For visual and usability
purpose, I prefer not to use form and use 5 columns (B to F) to receive the
answer. If any character is entered in column B, than the algorithm count
the answer as being "1", if a character is entered in E, then the answer is
"4", etc.

What I want is automatic checking than only one cell in the five column is
marked at all time, Moreover, when the user decide to change his answer,
the previous marked cell in my five column range is automatically deleted
to allow only one cell to be marked at all time.

In short like radio-buttons choice in forms, but not using forms in this
case.

Ex:

A B C D E F
Question 1 x

If user decide to cross E instead, the result is automatically

A B C D E F
Question 1 x

without having to delete B.


Any suggestions ?

Thanks
 
Laurent,

You can use the worksheet's change event.

Copy the code below, right-click on the sheet tab, select "View Code"
and paste the code into the window that appears.

Change the B2:F20 to reflect your actual range, and it should work as
you require.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As Variant

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B2:F20")) Is Nothing Then
Temp = Target.Value
Application.EnableEvents = False
Cells(Target.Row, 2).Resize(1, 5).ClearContents
Target.Value = Temp
Application.EnableEvents = True
End If
End Sub
 
in
microsoft.public.excel.worksheet.functions
Laurent,

You can use the worksheet's change event.

Copy the code below, right-click on the sheet tab, select "View Code"
and paste the code into the window that appears.

Change the B2:F20 to reflect your actual range, and it should work as
you require.

HTH,
Bernie
MS Excel MVP
Click to expand...

Thanks Bernie. I'll try that.

I am familiar with VBA coding on Word, but not with Excel. I should explore
it more :-)

laurent
 
Back
Top