Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change does not work
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change does not work
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" <pcompNOSPAMatCanada.com wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change does not work
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" wrote in message ... 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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change does not work
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) "ned" <pcompNOSPAMatCanada.com wrote in message ... 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" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change help | Excel Discussion (Misc queries) | |||
Getting around Worksheet_Change() | Excel Worksheet Functions | |||
Worksheet_Change | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |