Glaswegian
10-13-2009, 03:22 AM
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?
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?