Worksheet_Change does not work

[ned]

Hello;

I can not figure out why the following code will not work.

The range "LamTable" has data validation set to allow only items from a
list, or a blank. I want to change the cell to a blank when the user
selects "<none>" from the list. I also want to change the value in the
corresponding row of the range "LamMultipliers" to 0.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo theEnd
Application.EnableEvents = False
If Not (Application.Intersect(Target, Me.[LamTable]) Is Nothing) Then
If Target.Value = "<none>" Then
ActiveSheet.Cells(1, 1) = "Hello" 'error occurs here
Me.Cells(Target.Row, Target.Column).Formula = ""
Me.Cells(Target.Row, Me.[LamMultipliers].Column) = 0#
End If
End If
theEnd:
Application.EnableEvents = True
End Sub

I have spent about 4 hours trying different methods to accomplish the same
thing and none of them work.

The code gets the correct cells - I checked using the debugger. An error
1004 is raised whenever the code tries to modify any cell on the sheet. The
sheet is not protected.

--
Thanks in advance,

Stephen

____________________________________________________________________________
____
S. Fitzgerald, P.Eng, MASc.
Practical Composites, Inc.
Halifax, NS
Canada

 

[Bob Phillips]

Stephen,

I just plugged the code into my worksheet and it worked fine for me. XL2000,
XP Pro.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[ned]

Bob;

Thanks for the reply. It works for me until I add the Data Validation on
the Range "LamTable". After that it fails when I try to change any cell on
the sheet. Did you apply data validation to limit cells in LamTable to a
list?

Thanks,

Stephen

 

[Bob Phillips]

Stephen,

Yes I did. I had a 5 cell LamTable, and I applied a DV list of a,b,c,<none>.

DV does not trigger a change event in XL97. Which version do you have?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)