aefavant
05-10-2010, 11:51 AM
Hi all!
I've been sunk in some code now when I try to work multiple (more than one) range and worksheet_change event.
The code works perfectly for a single range schema, but when I include other named ranges, it simple won't go (one range is ok, but not the other - in my 02 ranges test).
The idea behind the code is that the event will handle any change in cells falling into the ranges I define (dynamically defined) and will redo any changes. So if the user tries to modify any cell of the range the Event will bring up the old value of the cell!
The code works great for a single range. Since I want to "lock" those cells, I want to make the Event act upon any cell which begins with a certain name, in this case "lock*", so lock1, lock2,...locka, lockabc will all work.
I debugged the routine to make sure it would consider all ranges with "lock*" and it does!
But it becomes very unstable and does not lock all "lock"s. :banghead:
Any work arounds??
See the code:
-----------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng_names As Variant
Dim n As Variant
Set rng_names = ActiveWorkbook.Names
For Each n In rng_names
If n.Name Like "lock*" Then
If Application.Intersect(Target, Me.Range(n)) Is Nothing Then
Exit Sub
ElseIf Target.Formula <> dtval Then Target.Formula = dtval
Else: Exit Sub
End If
Else
End If
Next
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dtval = Target.Formula
End Sub
-----------------
Cheers!
I've been sunk in some code now when I try to work multiple (more than one) range and worksheet_change event.
The code works perfectly for a single range schema, but when I include other named ranges, it simple won't go (one range is ok, but not the other - in my 02 ranges test).
The idea behind the code is that the event will handle any change in cells falling into the ranges I define (dynamically defined) and will redo any changes. So if the user tries to modify any cell of the range the Event will bring up the old value of the cell!
The code works great for a single range. Since I want to "lock" those cells, I want to make the Event act upon any cell which begins with a certain name, in this case "lock*", so lock1, lock2,...locka, lockabc will all work.
I debugged the routine to make sure it would consider all ranges with "lock*" and it does!
But it becomes very unstable and does not lock all "lock"s. :banghead:
Any work arounds??
See the code:
-----------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng_names As Variant
Dim n As Variant
Set rng_names = ActiveWorkbook.Names
For Each n In rng_names
If n.Name Like "lock*" Then
If Application.Intersect(Target, Me.Range(n)) Is Nothing Then
Exit Sub
ElseIf Target.Formula <> dtval Then Target.Formula = dtval
Else: Exit Sub
End If
Else
End If
Next
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dtval = Target.Formula
End Sub
-----------------
Cheers!