PDA

View Full Version : Solved: worksheet value change affecting 2 cells



khalid79m
11-15-2009, 02:12 PM
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
'Page 3

If ActiveCell.Address = ThisWorkbook.Sheets("Intervention_3.0").Range("C95").Address Then
Run "Int3_Update_Page3"
Exit Sub
End If

If ActiveCell.Address = ThisWorkbook.Sheets("Intervention_3.0").Range
("H95").Address Then
Range("M95:N95").ClearContents
Exit Sub
End If

If ActiveCell.Address = ThisWorkbook.Sheets("Intervention_3.0").Range("M95").Address Then
Run "Int3_Update_Page3"
Exit Sub
End If
Application.ScreenUpdating = True

End Sub


this above runs the Int3 Update Page3 if C95 or M95 changes, if H95 changes then M95:N95's contents are clearer. Only one problem on h95 changeing the contents of m95:n95 are cleared but since m95 has changed it runs the update. Can I stop it from doing that

lucas
11-15-2009, 02:28 PM
Khalid, I can't get your first IF statement to work.

Can you attach an example workbook?

macropod
11-15-2009, 02:50 PM
Hi khalid79m,

Try:
Private Sub Worksheet_Change(ByVal Target As Range)
'Page 3
With Application
.ScreenUpdating = False
.EnableEvents = False
With ThisWorkbook.Sheets("Intervention_3.0")
If ActiveCell.Address = "C95" Or ActiveCell.Address = "M95" Then
Run "Int3_Update_Page3"
ElseIf ActiveCell.Address = "H95" Then
Range("M95:N95").ClearContents
End If
End With
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

khalid79m
11-15-2009, 02:52 PM
i cant post a workbook as it is confidential info, ill see i can create a dummy sheet.

Bob Phillips
11-15-2009, 03:22 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OtherSh As Worksheet

On Error GoTo ws_exit

Application.EnableEvents = False
Application.ScreenUpdating = False
'Page 3

Set OtherSh = ThisWorkbook.Sheets("Intervention_3.0")

If Target.Address = OtherSh.Range("C95").Address Then
Run "Int3_Update_Page3"
ElseIf Target.Address = OtherSh.Range("H95").Address Then
Me.Range("M95:N95").ClearContents
ElseIf ActiveCell.Address = OtherSh.Range("M95").Address Then
Run "Int3_Update_Page3"
End If

ws_exit:
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

khalid79m
11-20-2009, 03:06 AM
again xld you did it..