Unable to set the Locked property of the Range Class

[wsnyder]

Hi all,

Not sure why I am getting the error message

Unable to set the Locked property of the Range Class

The worksheet is definitely unlocked when I set a breakpoint in the code to check the Unprotect
The cells in the range are not merged.
Other ideas?

Thanks
-w

undefined 'Objects
'truncated
        Dim rngMessage1 As Range
        Dim rngMessage2 As Range
        
    'Initialize Objects
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Model")
        ws.Unprotect Password:="stuff"
        With ws
            Set rngMessage1 = .Range(.Cells(17, 17), .Cells(17, 18))
            Set rngMessage2 = .Range(.Cells(18, 17), .Cells(18, 18))
        End With
        
    'Clear messages and cell formats
'        rngMessage1.UnMerge
'        rngMessage2.UnMerge
        rngMessage1.Locked = False    '<<== Error here
        rngMessage2.Locked = False
        
        rngMessage1.ClearContents
        rngMessage2.ClearContents
        
        rngMessage1.Font.Italic = False
        rngMessage2.Font.Italic = False
        
        rngMessage1.Font.Color = vbBlack
        rngMessage2.Font.Color = vbBlack
        
        rngMessage1.Interior.Color = RGB(245, 245, 245)
        rngMessage2.Interior.Color = RGB(245, 245, 245)
        
        rngMessage1.Locked = True
        rngMessage2.Locked = True

'Truncated

    'Tidy up
        Set rngMessage1 = Nothing
        Set rngMessage2 = Nothing

        ws.Protect Password:="stuff"
        Set ws = Nothing
        Set wb = Nothing
        
End Sub

 

[6StringJazzer]

I tried your code. I am betting that the cells are merged.

If the cells are merged I get the error you show.
If the cells are merged and I uncomment your code to unmerge them, I do not get an error.
If the cells are not merged I do not get an error.

Never use merged cells.

 

[wsnyder]

Hmmmmm..... not merged.

Thanks,
w

 

[6StringJazzer]

I'll take your word for it that the cells are not merged but the screenshot does not demonstrate that. Here is a file where the cells are merged.

The only other thing I can do is to provide my sample file and you can compare it to yours to see if you can figure out what's different. The other option is to share your own file using some file sharing service.

 

[wsnyder]

Thanks,

Seem the problem may be that the worksheet is not unprotected.
I have in code:

ws.Unprotect Password:="stuff"

But when I set a breakpoint after and review the sheet is still protected
How to ensure that the sheet is unprotected?

Thanks
-w

 

[wsnyder]

It seems, there may be corruption in the module
I added a brand new module and entered same code, now it works.

Here's a response on Stackoverflow that seems to indicate this is a known issue.

Thanks,
-w

 

[6StringJazzer]

That is very interesting, and I was not aware of that. When I use Protect and Unprotect, I don't use it like you do so I have never run across this. I always put the code that acts on a sheet in the code module for that sheet* so I do not need to qualify Protect and Unprotect.

Did you try using Worksheets("Model") instead of ws as suggested in the Stackoverflow post?
_______________________________________
*Except for utility macros, which in my case do not protect and unprotect sheets anyway.

 

[wsnyder]

Thanks Jeff,
Yes, I tried in the old module, and it still did not work.
I then created a new module, and it did Unprotect in the new module

Thanks,
-w

 

[wsnyder]

Closing the thread.
Seems ws.unprotect is a known issue
Only recourse at this time is to create a new module and add the code there.

-w