Excel - 2000 or 97 conversion - macro problems

[ajw150]

Thanks for your reply. I have attached a jpg which should show the issue
a little more clearly. The "Risk Type" box drops down, as part of a data
validation, and provides a choice of 3 options. When the user makes a
choice an alternative but corresponsing selection appears in the row
below. The change is naturally really important but for some reason
does not work in excel 97. If its any use, here is the vb code too.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFromRng As Range
Dim myToRng As Range


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

On Error GoTo errHandler:
Application.EnableEvents = False

Select Case LCase(Target.Value)
Case Is = "single/low": Set myFromRng = Me.Range("n1819")
Case Is = "multiple": Set myFromRng = Me.Range("n2122")
Case Is = "welfare/administration": Set myFromRng =
Me.Range("n2425")
Case Else
Set myFromRng = Nothing
End Select

Set myToRng = Me.Range("b15:d16")

If myFromRng Is Nothing Then
Target.ClearContents 'remove the change to A1!
myToRng.ClearContents
MsgBox "Wrong response"
Else
myFromRng.Copy _
Destination:=myToRng
End If

errHandler:
Application.EnableEvents = True

End Sub



Thanks

Andrew

 

[Dave Peterson]

Ah, I didn't put the worksheet_change event together with xl97.

From Debra Dalgleish's site:
http://www.contextures.com/xlDataVal08.html

In Excel 97, selecting an item from a Data Validation dropdown list does
not trigger a Change event, unless the list items have been typed in the
Data Validation dialog box.

So you could either type those values into the Data|validation box or you could
use a button (placed adjacent to the cell) to start your macro. (Debra has a
sample workbook available as a link on that page.)

(or even use a combobox/dropdown)