Protect Sheet - excluding selected cells

[Bebbspoke]

Hi Peoples,
I have a single sheet .xlsm which I wish to distribute... where users can ONLY modify a few specific cells... but they cannot access the formulae or macros which operate on the cells.
I have followed all "instructions" as found via Microsoft Office etc. but always end up with either a totally protected (locked) sheet or a sheet that is fully unlocked...
Please could someone fully detail the process sequence... including the "select" action on the Protection tab which at times appears to be tri-state
(i.e. blank, filled or ticked).
Thank you.

 

[Declamatory]

Select all cells

Right click and select format cells.

Untick the locked box. Click OK.

Select the cells you want to hide the formulas on and protect from modification.

Right click on one of the selected cells and select format cells.

Tick the locked box and hidden box. Click OK.

Click on review and protect sheet. Insert password and you're good to go.

 

[Bebbspoke]

Declamatory; - thanks for your suggestion - it hangs on "merged cells"... true - the xlsm sheet does contain merged cells (but I was careful to select the locked cells appropriately), and the only cells I wish to remain user accessible are only single individual cells, none of which are merged! - so I remain thwarted...

I have since retried - (I had not been as thorough with merged cells as previously thought)!

However - the sheet still refuses to save with all locked or accessible as desired...

 

[Bebbspoke]

It seems now that I can save with locked & unlocked as desired... but there remains a problem; -

Some (4) of the "User cells" function mathematically soley on the displayed data of the sheet - when the remainder of the sheet is locked - and the User cells perform correrctly...

However; - a further two of the "User cells" are effctively numeric inserts to individual (randomising) macros and the macro processes become inhibited...

This is most frustrating! - I wish to release the sheet with full functionality but lock access to any mathematics or processing... presently driving me up the proverbial wall!

 

[Declamatory]

When you say hangs you mean when a macro is run?

If so you can put this in your vba code which will allow the macro to update cells but not users.

Sheets("Sheet1").Protect "password", UserInterFaceOnly:=True

Alternatively you can put code in which unprotects the workbook, allows the maro to make changes and protects the book again.

ActiveSheet.Unprotect "password"
'your code
ActiveSheet.Protect "password"

 

[Bebbspoke]

Declamatory - thank you for your response - yes the "hang" was to infer that the macros do not operate.

Please excuse my naievety but
a/: - Am I correct in assuming that where "password" is given in inverted commas; - the actual pw should be typed?
&
b/: - where within the macro code string should the given fix patches be placed?

I thank you for your time in assistig this idiot!...

 

[Declamatory]

Yes where it says password it is your password.

So if your password was paper and your sheet was called mysheet you would put this at the beginning of your VBA code

Sheets("mysheet").Protect "paper", UserInterFaceOnly:=True

 

[Bebbspoke]

Hello Declamatory - thank you for your time & patience...
The macro code is as follows: -

Sub RandomBinarySeq4()
Dim Cnt As Long
Dim RandomIndex As Long
Dim Ones As Long
Dim Zeroes As Long
Dim Tmp As Variant
Dim Arr As Variant
Static IsRandomized As Boolean
Dim Count As Variant
Dim Percent As Variant
Dim Rng As Range
Dim iCnt As Integer
Dim Dn As Range
Dim nStr As String

Dim lngR As Long
Range("A3:A" & Rows.Count).ClearContents
Range("C3:D" & Rows.Count).ClearContents

If Not IsRandomized Then... it goes on for several more lines...

I have tried inserting the following instruction: -
Sheets(Sheet1).Protect "password", UserInterFaceOnly:=True

either as the opening line or directly below the initial "Sub" line... I have tried both with & without the quotation marks with no success...
please also note that I do not wish the "Users" to be able to access the macro... as they may then access the pw... which defeats the object!

Your further advice would be most welcome... thank you.

 

[Declamatory]

Hi,

You should be able to put the line below the initial sub line. Please note the code will need to be changed to be called the same sheet name you want protected and the password has to match the password protection on the worksheet.

Sheets("THIS IS YOUR SHEET NAME").Protect "THIS IS YOUR PASSWORD", UserInterFaceOnly:=True

In terms of preventing someone from viewing the code:



When in VBA editor click on tools and select VBAProject Properties. On the protection tab tick Lock Project for viewing and insert a password. Click OK and save your workbook. Then close it down. When you re-open the workbook the code will be hidden. Macros can be run but not viewed.

 

[Declamatory]

Sub RandomBinarySeq4()

ActiveSheet.Unprotect "password"

Dim Cnt As Long
Dim RandomIndex As Long
Dim Ones As Long
Dim Zeroes As Long
Dim Tmp As Variant
Dim Arr As Variant
Static IsRandomized As Boolean
Dim Count As Variant
Dim Percent As Variant
Dim Rng As Range
Dim iCnt As Integer
Dim Dn As Range
Dim nStr As String

Dim lngR As Long
Range("A3:A" & Rows.Count).ClearContents
Range("C3:D" & Rows.Count).ClearContents

ActiveSheet.Protect "password"