| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combine Intersect Range in If statements
I am trying to combine the two intersect ranges below into one macro that is
in a worksheet tab. The outside if range works but the inside one doesn't. What do I need to do to fix it? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim R As Long R = Target.Row On Error GoTo ErrHandler Application.EnableEvents = True If Not Intersect(Range("L2:m20"), Target) Is Nothing Then If Not Intersect(Range("A2:B2"), Target) Is Nothing Then End If If Target.Column < 9 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If If Target.Column < 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, 2).Value = Date ErrHandler: Application.EnableEvents = True End Sub Thanks, Ben |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine Intersect Range in If statements
I don't understand what you're doing, but maybe this will give you an outline:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub On Error GoTo ErrHandler If Not Intersect(Me.Range("L2:m20"), Target) Is Nothing Then 'you're in L2:M2, so do the stuff for L2:M2 here 'some code 'some more code Application.EnableEvents = False 'change some cells on the worksheet ElseIf Not Intersect(Me.Range("A2:B2"), Target) Is Nothing Then 'you're in A2:B2 'do what you need Application.EnableEvents = False 'change some different cells on the worksheet End If ErrHandler: Application.EnableEvents = True End Sub Ben Dummar wrote: I am trying to combine the two intersect ranges below into one macro that is in a worksheet tab. The outside if range works but the inside one doesn't. What do I need to do to fix it? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim R As Long R = Target.Row On Error GoTo ErrHandler Application.EnableEvents = True If Not Intersect(Range("L2:m20"), Target) Is Nothing Then If Not Intersect(Range("A2:B2"), Target) Is Nothing Then End If If Target.Column < 9 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If If Target.Column < 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, 2).Value = Date ErrHandler: Application.EnableEvents = True End Sub Thanks, Ben -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine Intersect Range in If statements
Thanks for the help!
I was trying to have a date set in 2 different cells based upon data changing in the corresponding 2 different rows. With your help I was able to the macro below work. I don't know if this is the bet way but it does work. Thanks Again! Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) ActiveSheet.Unprotect Password:="password" On Error GoTo ErrHandler If Not Intersect(Range("B2:B5000"), Target) Is Nothing Then If Target.Column < 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, 2).Value = Date Application.EnableEvents = True ElseIf Not Intersect(Range("I2:I5000"), Target) Is Nothing Then If Target.Column < 9 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True ErrHandler: Application.EnableEvents = True End Sub "Dave Peterson" wrote: I don't understand what you're doing, but maybe this will give you an outline: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub On Error GoTo ErrHandler If Not Intersect(Me.Range("L2:m20"), Target) Is Nothing Then 'you're in L2:M2, so do the stuff for L2:M2 here 'some code 'some more code Application.EnableEvents = False 'change some cells on the worksheet ElseIf Not Intersect(Me.Range("A2:B2"), Target) Is Nothing Then 'you're in A2:B2 'do what you need Application.EnableEvents = False 'change some different cells on the worksheet End If ErrHandler: Application.EnableEvents = True End Sub Ben Dummar wrote: I am trying to combine the two intersect ranges below into one macro that is in a worksheet tab. The outside if range works but the inside one doesn't. What do I need to do to fix it? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim R As Long R = Target.Row On Error GoTo ErrHandler Application.EnableEvents = True If Not Intersect(Range("L2:m20"), Target) Is Nothing Then If Not Intersect(Range("A2:B2"), Target) Is Nothing Then End If If Target.Column < 9 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If If Target.Column < 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, 2).Value = Date ErrHandler: Application.EnableEvents = True End Sub Thanks, Ben -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine Intersect Range in If statements
You're doing some extra stuff.
Since you're checking to see if you're in B2:B5000, there's no reason to check to see if you're not in column 2 (B). And since you're starting with B2, you can't be in row 1. So you don't need those checks. Same kind of thing for I2:I5000. And you may want to disable events before you change something on the sheet. If you don't, then the _change event will fire when you add the date. I'd do something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub On Error GoTo ErrHandler If Not Intersect(Me.Range("B2:B5000"), Target) Is Nothing Then 'it's in B2:B5000, so don't bother checking 'to see if it's column B and you can't be in row 1 'since you're in B2:B5000 'unprotect the worksheet before you change the cell Me.Unprotect Password:="password" 'turn off events to keep this change from 'making this even fire again application.enableevents = false Target.Offset(0, 2).Value = Date ElseIf Not Intersect(Me.Range("I2:I5000"), Target) Is Nothing Then 'same reasoning as before. You're in column 9 and outside row 1 'unprotect the worksheet before you change the cell Me.Unprotect Password:="password" 'turn off events to keep this change from 'making this even fire again application.enableevents = false Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If ErrHandler: Me.Protect Password:="password", DrawingObjects:=True, Contents:=True Application.EnableEvents = True End Sub Ben Dummar wrote: Thanks for the help! I was trying to have a date set in 2 different cells based upon data changing in the corresponding 2 different rows. With your help I was able to the macro below work. I don't know if this is the bet way but it does work. Thanks Again! Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) ActiveSheet.Unprotect Password:="password" On Error GoTo ErrHandler If Not Intersect(Range("B2:B5000"), Target) Is Nothing Then If Target.Column < 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, 2).Value = Date Application.EnableEvents = True ElseIf Not Intersect(Range("I2:I5000"), Target) Is Nothing Then If Target.Column < 9 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True ErrHandler: Application.EnableEvents = True End Sub "Dave Peterson" wrote: I don't understand what you're doing, but maybe this will give you an outline: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub On Error GoTo ErrHandler If Not Intersect(Me.Range("L2:m20"), Target) Is Nothing Then 'you're in L2:M2, so do the stuff for L2:M2 here 'some code 'some more code Application.EnableEvents = False 'change some cells on the worksheet ElseIf Not Intersect(Me.Range("A2:B2"), Target) Is Nothing Then 'you're in A2:B2 'do what you need Application.EnableEvents = False 'change some different cells on the worksheet End If ErrHandler: Application.EnableEvents = True End Sub Ben Dummar wrote: I am trying to combine the two intersect ranges below into one macro that is in a worksheet tab. The outside if range works but the inside one doesn't. What do I need to do to fix it? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim R As Long R = Target.Row On Error GoTo ErrHandler Application.EnableEvents = True If Not Intersect(Range("L2:m20"), Target) Is Nothing Then If Not Intersect(Range("A2:B2"), Target) Is Nothing Then End If If Target.Column < 9 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If If Target.Column < 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, 2).Value = Date ErrHandler: Application.EnableEvents = True End Sub Thanks, Ben -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine Intersect Range in If statements
Dave,
Is there a way to make it so that it only lock the worksheet when one of the two designated columns is intersected or if it is manually set to locked. I have manually unlocked and am trying to edit some other cells but after every entry regardless of where the cell is it lock the worksheet. I know I can take the password scetion out of the macro and put it back in when I am done editing it but would prefer to not have to do that everytime. Thanks, Ben "Dave Peterson" wrote: You're doing some extra stuff. Since you're checking to see if you're in B2:B5000, there's no reason to check to see if you're not in column 2 (B). And since you're starting with B2, you can't be in row 1. So you don't need those checks. Same kind of thing for I2:I5000. And you may want to disable events before you change something on the sheet. If you don't, then the _change event will fire when you add the date. I'd do something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub On Error GoTo ErrHandler If Not Intersect(Me.Range("B2:B5000"), Target) Is Nothing Then 'it's in B2:B5000, so don't bother checking 'to see if it's column B and you can't be in row 1 'since you're in B2:B5000 'unprotect the worksheet before you change the cell Me.Unprotect Password:="password" 'turn off events to keep this change from 'making this even fire again application.enableevents = false Target.Offset(0, 2).Value = Date ElseIf Not Intersect(Me.Range("I2:I5000"), Target) Is Nothing Then 'same reasoning as before. You're in column 9 and outside row 1 'unprotect the worksheet before you change the cell Me.Unprotect Password:="password" 'turn off events to keep this change from 'making this even fire again application.enableevents = false Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If ErrHandler: Me.Protect Password:="password", DrawingObjects:=True, Contents:=True Application.EnableEvents = True End Sub Ben Dummar wrote: Thanks for the help! I was trying to have a date set in 2 different cells based upon data changing in the corresponding 2 different rows. With your help I was able to the macro below work. I don't know if this is the bet way but it does work. Thanks Again! Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) ActiveSheet.Unprotect Password:="password" On Error GoTo ErrHandler If Not Intersect(Range("B2:B5000"), Target) Is Nothing Then If Target.Column < 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, 2).Value = Date Application.EnableEvents = True ElseIf Not Intersect(Range("I2:I5000"), Target) Is Nothing Then If Target.Column < 9 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True ErrHandler: Application.EnableEvents = True End Sub "Dave Peterson" wrote: I don't understand what you're doing, but maybe this will give you an outline: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub On Error GoTo ErrHandler If Not Intersect(Me.Range("L2:m20"), Target) Is Nothing Then 'you're in L2:M2, so do the stuff for L2:M2 here 'some code 'some more code Application.EnableEvents = False 'change some cells on the worksheet ElseIf Not Intersect(Me.Range("A2:B2"), Target) Is Nothing Then 'you're in A2:B2 'do what you need Application.EnableEvents = False 'change some different cells on the worksheet End If ErrHandler: Application.EnableEvents = True End Sub Ben Dummar wrote: I am trying to combine the two intersect ranges below into one macro that is in a worksheet tab. The outside if range works but the inside one doesn't. What do I need to do to fix it? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim R As Long R = Target.Row On Error GoTo ErrHandler Application.EnableEvents = True If Not Intersect(Range("L2:m20"), Target) Is Nothing Then If Not Intersect(Range("A2:B2"), Target) Is Nothing Then End If If Target.Column < 9 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If If Target.Column < 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, 2).Value = Date ErrHandler: Application.EnableEvents = True End Sub Thanks, Ben -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combine Intersect Range in If statements
If you're making the changes as a developer, I'd remove that .protect
statement. Put it back when you put it into production. But you could look to see what you're changing: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B2:b5000,i2:i5000")) Is Nothing Then Exit Sub End If On Error GoTo ErrHandler If Not Intersect(Me.Range("B2:B5000"), Target) Is Nothing Then Me.Unprotect Password:="password" Application.EnableEvents = False Target.Offset(0, 2).Value = Date ElseIf Not Intersect(Me.Range("I2:I5000"), Target) Is Nothing Then Me.Unprotect Password:="password" Application.EnableEvents = False Target.Offset(0, -2).Value = Date End If ErrHandler: Me.Protect Password:="password", DrawingObjects:=True, Contents:=True Application.EnableEvents = True End Sub Ben Dummar wrote: Dave, Is there a way to make it so that it only lock the worksheet when one of the two designated columns is intersected or if it is manually set to locked. I have manually unlocked and am trying to edit some other cells but after every entry regardless of where the cell is it lock the worksheet. I know I can take the password scetion out of the macro and put it back in when I am done editing it but would prefer to not have to do that everytime. Thanks, Ben "Dave Peterson" wrote: You're doing some extra stuff. Since you're checking to see if you're in B2:B5000, there's no reason to check to see if you're not in column 2 (B). And since you're starting with B2, you can't be in row 1. So you don't need those checks. Same kind of thing for I2:I5000. And you may want to disable events before you change something on the sheet. If you don't, then the _change event will fire when you add the date. I'd do something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub On Error GoTo ErrHandler If Not Intersect(Me.Range("B2:B5000"), Target) Is Nothing Then 'it's in B2:B5000, so don't bother checking 'to see if it's column B and you can't be in row 1 'since you're in B2:B5000 'unprotect the worksheet before you change the cell Me.Unprotect Password:="password" 'turn off events to keep this change from 'making this even fire again application.enableevents = false Target.Offset(0, 2).Value = Date ElseIf Not Intersect(Me.Range("I2:I5000"), Target) Is Nothing Then 'same reasoning as before. You're in column 9 and outside row 1 'unprotect the worksheet before you change the cell Me.Unprotect Password:="password" 'turn off events to keep this change from 'making this even fire again application.enableevents = false Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If ErrHandler: Me.Protect Password:="password", DrawingObjects:=True, Contents:=True Application.EnableEvents = True End Sub Ben Dummar wrote: Thanks for the help! I was trying to have a date set in 2 different cells based upon data changing in the corresponding 2 different rows. With your help I was able to the macro below work. I don't know if this is the bet way but it does work. Thanks Again! Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) ActiveSheet.Unprotect Password:="password" On Error GoTo ErrHandler If Not Intersect(Range("B2:B5000"), Target) Is Nothing Then If Target.Column < 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, 2).Value = Date Application.EnableEvents = True ElseIf Not Intersect(Range("I2:I5000"), Target) Is Nothing Then If Target.Column < 9 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True ErrHandler: Application.EnableEvents = True End Sub "Dave Peterson" wrote: I don't understand what you're doing, but maybe this will give you an outline: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub On Error GoTo ErrHandler If Not Intersect(Me.Range("L2:m20"), Target) Is Nothing Then 'you're in L2:M2, so do the stuff for L2:M2 here 'some code 'some more code Application.EnableEvents = False 'change some cells on the worksheet ElseIf Not Intersect(Me.Range("A2:B2"), Target) Is Nothing Then 'you're in A2:B2 'do what you need Application.EnableEvents = False 'change some different cells on the worksheet End If ErrHandler: Application.EnableEvents = True End Sub Ben Dummar wrote: I am trying to combine the two intersect ranges below into one macro that is in a worksheet tab. The outside if range works but the inside one doesn't. What do I need to do to fix it? Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim R As Long R = Target.Row On Error GoTo ErrHandler Application.EnableEvents = True If Not Intersect(Range("L2:m20"), Target) Is Nothing Then If Not Intersect(Range("A2:B2"), Target) Is Nothing Then End If If Target.Column < 9 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, -2).Value = Date '<<== CHECK RANGE End If If Target.Column < 2 Then Exit Sub If Target.Row = 1 Then Exit Sub Target.Offset(0, 2).Value = Date ErrHandler: Application.EnableEvents = True End Sub Thanks, Ben -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF statements referencing an absolute value or a range of values | Excel Worksheet Functions | |||
| How can I intersect 2 trendlines on the same spreadsheet? | Excel Discussion (Misc queries) | |||
| Intersect operations | Excel Worksheet Functions | |||
| Combine multiple cells into one cell range. | Excel Worksheet Functions | |||
| how to combine cells by selecting range? | New Users to Excel | |||
Linear Mode
