Data entry for fixed cell ranges

[webels]

Hi
I have an Excel worksheet with fixed data in cell ranges. Say in
C2:C9 and B2:B8 the data is fixed. If I click on any of the cells in
this range C2:C9 and I would like “Penicillin = Sensitive” entered
into cell A11 and If I click on any of the cells in this range C2:C9
and I would like “Augmentin = Sensitive” entered into cell B12. There
are a whole lot of other ranges to work with too but if someone could
steer me in a direction I would be able to work the rest out myself

Any help is much appreciated.

Thanks
Eddie

 

[Per Jessen]

Hi

This is event code and has to be pasted into the code sheet for you
worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B2:B8")) Is Nothing Then
Range("A11") = "Penicilin = Sensitive"
ElseIf Not Intersect(Target, Range("C2:C8")) Is Nothing Then
Range("B12") = "Augmentine = Sensitive"
End If
End Sub

Regards,
Per

"webels" <[email protected]> skrev i meddelelsen
Hi
I have an Excel worksheet with fixed data in cell ranges. Say in
C2:C9 and B2:B8 the data is fixed. If I click on any of the cells in
this range C2:C9 and I would like “Penicillin = Sensitive” entered
into cell A11 and If I click on any of the cells in this range C2:C9
and I would like “Augmentin = Sensitive” entered into cell B12. There
are a whole lot of other ranges to work with too but if someone could
steer me in a direction I would be able to work the rest out myself

Any help is much appreciated.

Thanks
Eddie

 

[joel]

You could use in the Data menu Validation - List. You can put in th
List a range of cells or a list of differrent strings. This will creat
a drop down list for your selection.

Remember that you want to be able to remove an item if put data int
the wrong cell

 

[webels]

Hi

This is event code and has to be pasted into the code sheet for you
worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B2:B8")) Is Nothing Then
    Range("A11") = "Penicilin = Sensitive"
ElseIf Not Intersect(Target, Range("C2:C8")) Is Nothing Then
    Range("B12") = "Augmentine = Sensitive"
End If
End Sub

Regards,
Per

"webels" <[email protected]> skrev i meddelelsenHi
I have an Excel worksheet with fixed data in cell ranges.  Say in
C2:C9 and B2:B8 the data is fixed. If I click on any of the cells in
this range C2:C9 and I would like “Penicillin = Sensitive” entered
into cell A11 and If I click on any of the cells in this range C2:C9
and I would like “Augmentin = Sensitive” entered into cell B12. There
are a whole lot of other ranges to work with too but if someone could
steer me in a direction I would be able to work the rest out myself

Any help is much appreciated.

Thanks
Eddie

Thanks Per great code works perfectly, thanks Joel also for your
suggestion.

 

[webels]

Thanks Per great code works perfectly, thanks Joel also for your
suggestion.- Hide quoted text -

- Show quoted text -

Hi again
What I set up as described above by Per is working perfectly, I was
just wondering if it would be possible to add the Value in the cell
clicked to the current out to look something like this "Penicillin =
Sensitive (0.0064)". 0.0064 being the value in the cell clicked.

Thanks again
Eddie

 

[Per Jessen]

Hi Eddie

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B2:B8")) Is Nothing Then
Range("A11") = "Penicilin = Sensitive (" & Target.Value & ")"
ElseIf Not Intersect(Target, Range("C2:C8")) Is Nothing Then
Range("B12") = "Augmentine = Sensitive (" & Target.Value & ")"
End If
End Sub

Regards,
Per

 

[webels]

Hi Eddie

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B2:B8")) Is Nothing Then
    Range("A11") = "Penicilin = Sensitive (" & Target.Value & ")"
ElseIf Not Intersect(Target, Range("C2:C8")) Is Nothing Then
    Range("B12") = "Augmentine = Sensitive (" & Target.Value & ")"
End If
End Sub

Regards,
Per





- Show quoted text -

Thats exactly what I need Per - thank you for your very helpful and
prompt assistance