Protection - UserInterfaceOnly

[Loomah]

Hi All
I'm almost afraid to ask but I really do think I'm going mad!!

I'm protecting a worksheet (xl97, SR2) using UserInerfaceOnly, with relevant cells locked and unlocked but once data has been entered the sheet becomes unprotected. This even happens with a password.

I want the protection in order to be able to change 'things', depending on user input, through the worksheet change event.

I could always reprotect after every change but that seems a bit pointless! so am I missing something really fundamental that I haven't come accross before??

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

Hey Loomah - if the sheet is protected - userinterfaceonly - it should not "unprotect" itself unless:

a: a reference is made (in code) to the protection property of the worksheet
b: the workbook is closed and then re-opened - the userinterfaceonly argument must be set on workbook open each time as it only lasts for the session the workbook is open for...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Loomah]

Kind sir!
No references made to protection property
Protection code is in the open event - all on its lonesome!

Just had a thought that for some reason the fact that the book requires a password to open might have had something to do with it but no such luck!

Ive just rechecked a coupla things and the sheet is protected when opened. One part of my change event does what I want it to do without unprotecting the sheet but this (rather clumsy) bit unprotects the sheet, if fired, for some reason

    If Not Intersect(Target, Columns("C").Cells) Is Nothing Then
    ResetNamedRanges
        If Target <> "" And Target.Row <> 3 Then
            Target.Offset(0, -1) = Target.Offset(-1, -1) + 1
        ElseIf Target.Row = 3 Then
            Target.Offset(0, -1) = 1
            Application.EnableEvents = True
            Exit Sub
        Else
            Target.Offset(0, -1).ClearContents
        End If
        Range("O" & Target.Row - 1).Copy Range("O" & Target.Row)
    End If

Never had a problem with this before!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

What does ResetNamedRanges do ??

NEver sen this before either - have used this method loads on workbooks and would hate to think it could be broken so arbitrarily

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Loomah]

xlbo
ResetNamedRanges does what it suggests, ie recreates 2 named ranges to reflect data that is added or deleted. Using Dynamic named ranges in this case is a no go as there could be gaps in the data and (for some reason) formulas elsewhere in the book didn't like them. Just plain mean, I thought!

The other thing with this sub is that it is called from the other part of the change event that works ok!

I've worked around this problem before in the good old fasioned way it was done before UserInterfaceOnly entered my consciousness but these things are starting to p me off now!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

Bizarre - cannot replicate the error - using XP though....maybe it's a 97 specific issue - I believe that was the year it was introduced so maybe they had some teething troubles with it...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Loomah]

I give up. It works today!!!

The only difference is that I resaved the file with the password to open it after adding the code. Dunno if that's the reason for the problem but......

Another feature!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​