Protect Sheet - excluding selected cells

Bebbspoke

Board Regular
Joined
Oct 10, 2014
Messages
193
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Declamatory

Active Member
Joined
Nov 6, 2014
Messages
319
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.
 
Last edited:

Bebbspoke

Board Regular
Joined
Oct 10, 2014
Messages
193
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...
 
Last edited:

Bebbspoke

Board Regular
Joined
Oct 10, 2014
Messages
193
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

Active Member
Joined
Nov 6, 2014
Messages
319

ADVERTISEMENT

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.

Code:
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.

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

Bebbspoke

Board Regular
Joined
Oct 10, 2014
Messages
193
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

Active Member
Joined
Nov 6, 2014
Messages
319

ADVERTISEMENT

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

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

Bebbspoke

Board Regular
Joined
Oct 10, 2014
Messages
193
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

Active Member
Joined
Nov 6, 2014
Messages
319
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.

Code:
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.
 
Last edited:

Declamatory

Active Member
Joined
Nov 6, 2014
Messages
319
Code:
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"
 
Last edited:

Forum statistics

Threads
1,136,324
Messages
5,675,096
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top