Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Change Event and Intersect
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Change Event and Intersect
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 -- Gary''s Student - gsnu200781 "headly" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Change Event and Intersect
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 On Apr 22, 5:30*pm, headly wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Change Event and Intersect
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 "headly" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Change Event and Intersect
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. -- HTH... Jim Thomlinson "headly" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change a Worksheet_change event to a beforesave event? | Excel Programming | |||
MsgBox in Enter event causes combobox not to run Change event | Excel Programming | |||
Change event and calculate event | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
change event/after update event?? | Excel Programming |