Working with a locked spreadsheet model

I am not a VBaler user but in Lotus123 once you lock everything you will find that almost nothing can be changed. I suppose it is to allow one to view a spreadsheet without allowing changes. Actually it is easier to make the whole file read only for all users. You can always keep your own alterable copy which is not 'read only'.
If you want to hide the macros why not just hide the sheet which has all the macros on it. The 'hide' command is usually in the 'Style' dropdown and you should find the hide sheet command there.
Personally I hate using protected cells as it gets very confusing and drives me to distraction - to the extent I now don't use it them all!
Apologies if you already know all of this - it's always difficult to know how much someone knows. Some people get very touchy and behave as if they've been insulted. Oh well such is life....

If the file is unlocked, hidden sheets can be "unhidden" by anyone.  My reason for locking the file is to protect the source code and cell formulas.  I was hoping that there was a version that allowed for locking and also allowed for changes to unprotected ranges.  I was willing to go through effort of unprotecting ranges in order to be able to control what was hidden.

If the file is unlocked, hidden sheets can be "unhidden" by anyone.  My reason for locking the file is to hide the source code and cell formulas.  I was hoping that there was a version that allowed for locking and also allowed for changes to unprotected ranges.  I was willing to go through effort of unprotecting ranges in order to be able to control what was hidden. its worth many points to me if there is a way around what seems to be a Lotus design flaw.

Lotus123 does work in strange ways but there is one thing for certain and that is if your spreadsheet is 'locked' you cannot change protected cells - and you cannot un-protect them either.

The default position for all cells in all worksheet is 'protected' but un-locked. So to make use of the protection scheme you first need to un-lock the spread sheet by file/workbookproperties/security and untick the 'Lock worksheet' item.

Then highlight the cells you do NOT want to protect, rightclick on them and select the 'key' symbol and deselect the 'Protect cells from changes' and then go back into file/workbookproperties/security and tick the 'Lock worksheet' item. This will lock the work sheet but allow changes to those cells that are unprotected.

The confusing part of the whole mechanism is when the worksheet is first used. The file/workbookproperties/security is ticked in the 'Lock worksheet' item. This needs to be un-ticked first. When it is re-ticked you will be asked for a password.

To make any subsequent changes to the worksheet you will need to un-tick the file/workbookproperties/security 'Lock worksheet' item before you can proceed with the changes.

There is a degree of logic in Lotus123's approach in that you don't need to go through every part of the spreadsheet to protect everything. In fact you need to go through it to un-protect all the cells you want to be able to be changed even when the spreadsheet is locked.

The only problem comes if you are attempting to midify by means of a macro a cell that is protect and locked as such.

A scrpit/macro that incorporates unlocking of a spreadsheet would be of the type:

Sub openup
     [<<J:\lotus\work\123\test001.123>>Window 1].Activate
     [<<J:\lotus\work\123\test001.123>>].Title = ""
     [<<J:\lotus\work\123\test001.123>>].Subject = ""
     [<<J:\lotus\work\123\test001.123>>].Keywords = ""
     [<<J:\lotus\work\123\test001.123>>].Description = ""
     [<<J:\lotus\work\123\test001.123>>].Revisions = ""
     [<<J:\lotus\work\123\test001.123>>].Lock False,"james"
     [<<J:\lotus\work\123\test001.123>>].DataProtected = False
     [<<J:\lotus\work\123\test001.123>>].AlwaysReserve = True
     [<<J:\lotus\work\123\test001.123>>].ShowSheetFrame = True
     [<<J:\lotus\work\123\test001.123>>].ShowDrawLayer = True
     [<<J:\lotus\work\123\test001.123>>].ShowFormulaMarkers = False
     [<<J:\lotus\work\123\test001.123>>].ShowCellCommentMarkers = True
     [<<J:\lotus\work\123\test001.123>>].ShowVersionBorders = True
     [<<J:\lotus\work\123\test001.123>>].ShowTableBorders = True
     [<<J:\lotus\work\123\test001.123>>].ShowGridLines = True
     [<<J:\lotus\work\123\test001.123>>].GridLineColor.ColorName = "25% gray"
     [<<J:\lotus\work\123\test001.123>>].ShowSheetTabs = True
     [<<J:\lotus\work\123\test001.123>>].ShowScrollBars = True
     [<<J:\lotus\work\123\test001.123>>].ShowManualPageBreaks = True
     [<<J:\lotus\work\123\test001.123>>].ShowAutomaticPageBreaks = True
     [<<J:\lotus\work\123\test001.123>>].ShowDesignModeMenu = False
     [<<J:\lotus\work\123\test001.123>>].ZoomScale = 87
     [Window 1].Activate
End Sub

Where the password is 'james' without the ''

This could be in your script or macro and called each time you want to alter a protected cell. If you record a macro you will be able to create a similar one for re-locking the spreadsheet. The file I used was called 'test001.123'

I do hope this helps - even though it is a tad confusing.

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

PAQ'd and pts removed

Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
patrickab
EE Cleanup Volunteer

I have been away for some time.  I just returned.  I have not been able to try out the suggestion.  I am not sure that I can do it with a macro.  I will see if I can incorporate the script.

It works.  People can still get around it, but it requires a more technical knowledge of Lotus.  I am hiding the scripts in another work book.  It will only be opened under macro control.  If the user uses Alt F3 to run a script the protect and unprotect scripts will not be vivible unless the user finds the workbook with the scripts and opens it.