Working with various Ranges (Named Range) and Worsheet Events

[aefavant]

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.

Any work arounds??

See the code:

-----------------
[VBA]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[/VBA]
-----------------

Cheers!

[Bob Phillips]

Looping through all the names in a change event does seem a good idea.

What exactly are you trying to do, why are you using the change event?

[Paul_Hossler]

One thing you can try


[VBA]
ElseIf
Application.EnableEvents = False
Target.Formula <> dtval Then Target.Formula = dtval
Application.EnableEvents = True
[/VBA]

Paul

[aefavant]

Looping through all the names in a change event does seem a good idea.

What exactly are you trying to do, why are you using the change event?

Hi xld, thanks.

The idea behind all that is quite simple. It will act like a password-protected sheet, but without any password!
Instead of setting a pwd to disallow the user to change a range of cells, the Event is programmed underneath the worksheet to un-do any change the user makes in that range. The interesting bit is that the event allows formulas to being updated. That means you can set cells where the user is supposed to insert data and ranges where the math is done, important info is shown etc, where is is not supposed to change. So the sensitive area will be protected.

cheers!

[aefavant]

One thing you can try


[vba]
ElseIf
Application.EnableEvents = False
Target.Formula <> dtval Then Target.Formula = dtval
Application.EnableEvents = True
[/vba]

Paul

Humm..interesting, I think this might solve my question of how to place a "switch" for the event. Will test and let you know Paul. Thanks!

No good... Actually, it worked but made no difference to the code that was there before.
Still no luck with the over-one-named-range stuff. If I define three ranges "locka" and "lock1" and "lockb" the program only recognised one! Although the loop through ranges IS indeed working will.
If I specify the LIKE argument with for instance "lockb" is works perfectly for lockb!
I can't see what might be going wrong

[Paul_Hossler]

1. It would seem that if any named range is not in the target, the sub exits w/o checking to see if the target in another name later on

[vba]
If Application.Intersect(Target, Me.Range(n)) Is Nothing Then
Exit Sub
[/vba]


2. Your

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dtval = Target.Formula
End Sub
[/vba]

works if you select a single cell, but fails if you select more than one

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
MsgBox Target.Formula
End Sub
[/vba]



Paul

[aefavant]

1. It would seem that if any named range is not in the target, the sub exits w/o checking to see if the target in another name later on

[vba]
If Application.Intersect(Target, Me.Range(n)) Is Nothing Then
Exit Sub
[/vba]


2. Your

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dtval = Target.Formula
End Sub
[/vba]

works if you select a single cell, but fails if you select more than one

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address
MsgBox Target.Formula
End Sub
[/vba]



Paul

Hi Paul, thanks for bringing in some new consideration.
I attached the test file so eveyone can take a look at it.
What you say is true, but actually the code provides enough to select more than one cell and addresses them right, but only brings the ".value" of one. Try clicking on one, than after the two pop-ups hold down the control key and click on another cell.

It must be a tiny/simple detail I am missing!

In the code I assigned "lockada" to the LIKE argument. No mistakes here.
The right code (if you may change and check for yourself) would be "lock*", so the Event would run through every Named Range beginning with "lock" and trigger the un-do'ing function.
If you test the FOR EACH step you'll verify the run through all named ranges is working properly.