On Change Event and Intersect

[headly]

Have code that reads (thanks to ozgrid)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Not Intersect(Target, Range("c13:c22")) Is Nothing Then
ActiveCell.Offset(0, 2).Value = ActiveCell.Value * ActiveCell.Offset(0,
1).Value
End If

but that effects the range c13:c22 on all sheets; How do I keep it specific
to a particular sheet?

TIA

 

[Gary''s Student]

Use worksheet code rather than workbook code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("c13:c22")) Is Nothing Then
ActiveCell.Offset(0, 2).Value = ActiveCell.Value * ActiveCell.Offset(0,
1).Value
End If
End Sub

 

[paul.robinson]

Hi
Put the code in the Worksheet_Change event for the sheet (double click
the sheet in the VBE, change (General) at the top to worksheet and
choose the event in the right hand dropdown. For most workbook level
events there is a corresponding worksheet level event.
Alternatively, you can change the existing code to work only on
specified sheets

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

If Sh.Name = "MySheet" then
If Not Intersect(Target, Range("c13:c22")) Is Nothing Then
ActiveCell.Offset(0, 2).Value = ActiveCell.Value *
ActiveCell.Offset(0, 1).Value
End If
End if

End Sub

regards
Paul

 

[Mike H]

Hi,

I'm guessing but I think you probably want target instead of activecell and
to keep it sheet specific:-

Right click the sheet tab, view code and paste it in on the right

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("c13:c22")) Is Nothing Then
Target.Offset(0, 2).Value = Target.Value * Target.Offset(0, 1).Value
End If


End Sub

Mike

 

[Jim Thomlinson]

Your code should be in the sheet rather than in ThisWorkbook. Right click the
sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("C13:C22")) Is Nothing Then _
Target.Offset(0, 2).Value = Target.Value * Target.Offset(0, 1).Value
ErrorHandler:
Application.EnableEvents = True
End Sub

Note that there are a couple of changes. If more than a single cell was
changed then nothing happens. If the change occured in C13:C22 then it turns
off events and makes the change which keeps the code from making a recursive
call based in the change that the code makes.