Same code - one set works but other does not...

[Glaswegian]

Hi

I wonder if anyone can explain this for me.

I've created a workbook with various functions etc etc and, to preserve a record of changes, I have a succession of different versions.

In the most recent version, users can click a command button to view details on a hidden sheet. This sheet is locked down so that users cannot change any of the data. All controls (all controls are Toolbox controls) should also be locked, except for the 'View' button - they can click this for a second time and return to the start sheet. This is part of the code that now does not work:


With Sheets(mySht)
.Visible = True
.Unprotect Password:="+RADIation4/3?"
For Each ole In .OLEObjects 'ensure all OLE objects are locked
ole.Object.Locked = True
Next ole
.Label5.Caption = "Click here to close this view"
For Each c In myRng 'ensure all cells are locked
c.Locked = True
Next c
.cmbView.Object.Locked = False 'unlock the button so that users can close this view
.Protect Password:="+RADIation4/3?"
End With
mySht is a variable passed from a user form and myRng is set to a specific list of cells to which users may input data.

I was a bit puzzled when the code stopped on the red line, so I opened a previous version and found this code:


With Sheets(mySht)
.Visible = True
.Unprotect Password:="+RADIation4/3?"
For Each ole In .OLEObjects
ole.Object.Locked = True
Next ole
For Each c In myRng
c.Locked = True
Next c
.cmbView.Object.Locked = False
.Protect Password:="+RADIation4/3?"
End With
This second piece of code runs fine every time - no problems and no errors.

If I remove 'object' from the line in red, the code runs fine but does not lock the controls.

I'm stumped on this one - the only change has been the addition of the 'caption' line, so why does it not work?

[Glaswegian]

Very strange...now the first piece of code is working correctly. I did close and re-open Excel - perhaps that has cleared memory? Actually both pieces of code are now working...

[Glaswegian]

Spoke too soon - back to square one again....

[mdmackillop]

Can you post a sample?

[Glaswegian]

Hi Malcolm

Looks like this will have to wait until next week - it's school mid term break and I'll be away for the rest of the week. I won't have time to create a sample today - my apologies.

Further testing indicates it might be something to do with the hiding/unhiding of the data sheet - but that's just a guess at the moment.

As some further background, users only see a sheet called 'Start'. They input data into specific cells (all others are locked and the sheet is protected) and then click a button to 'complete' the file. The Start sheet is then copied, renamed using 'Data' plus the date and time and then hidden. This ensures that users cannot make further changes. There are buttons for other departments to unlock specific textboxes on the hidden sheet so that they can add comments. Other departments have a password for access. Once they have added comments, they click the same button and the workbook is saved, with the Data sheet being hidden once again.

I've created similar workbooks in the past with no issues, so this one is doubly annoying!

[Glaswegian]

Hi again

Managed to create a sanitised version - you'll likely receive plenty of errors as I've had to remove a sheet that contains the named ranges for the green cells - data validation. I've also changed all passwords to 'password' - all lower case.

Thanks again for any help.