Excel User Defined Function to Change Locked Status of Range

I am trying to auto-(un)lock a range based on a value in another column.  As a simple example I have three cells and am trying to use a user defined function to solve instead of a long loop cycle.

D5: is the value that drives the change (1=unlock,else=lock)
E5: UDF (see code below)
G5: range to lock

On page UDF first
=LockIt(D5,"G5")

Open in new window


The module level function
Public Function LockIt(ByVal nlValue As Integer, ByVal rangeIN As String) As Boolean
On Error GoTo ErrHandler:

    Dim lLock As Boolean
    Dim lSuccess As Boolean
    
    If nlValue = 1 Then
        lLock = False
    Else
        lLock = True
    End If
    
     Sheet1.range(rangeIN).Locked = lLock

    Debug.Print Sheet1.range(rangeIN).Locked
    
    LockIt = lLock

ErrCleanup:
    LockIt = lSuccess
    Exit Function
ErrHandler:
    lSuccess = False
    MsgBox Err.Description, vbExclamation + vbOKOnly, "Error Encountered"
    Resume ErrCleanup
End Function

Open in new window


The code is running without error, but the range lock status is not happening.  

Thanks.
cadwal01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony BerenguelCommented:
"The code is running without error, but the range lock status is not happening. "

do you mean that part of the selection structure is not being hit when it you expect that it should?
0
NorieAnalyst Assistant Commented:
You can't use UDF's to change the worksheet, eg formatting, protection, they can only be used to return values.

Note, I've seen some examples that kind of bend this rule, if that's the way to put it, but not too many.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony BerenguelCommented:
that's good to know, imnorie. I've been trying to troubleshoot this code to work but with no success. No doubt because UDFs are not meant to accomplish this sort of task.

cadwal01, maybe you need to do this with a user-triggered macro or with the worksheet_change event.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

suvmitraCommented:
Work with Allow User to Edit Ranges.

By default worksheet is locked. The only way of unlocking a portion to the user to work with "Allow User to Edit Ranges"

The sheet must be protected with a Password.

The code needs to

1. Unlock the sheet with password (if it is protected)
2. Allow user to edit certail ranges
3. Lock the Worksheet with the password.
0
cadwal01Author Commented:
Unfortunately, and I forgot to mention, I had to develop this in 2003 b/c of client environment.  Kind of figured this would be the answer.  Appreciate the insights.
0
NorieAnalyst Assistant Commented:
It's not a version thing as far as I know, you've never been able to use VBA UDFs for this sort of thing.

Also, I'm afraid you can't really use the Change event - it's not triggered by value of a formula changing.

If you wanted to use the Change event I suppose you could monitor other cells, but that might not be practical if you don't know where the UDF will be used and what cell references might be used in it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.