Excel assistance

marine1

marine1

Honorary Master
Joined
Sep 4, 2006
Messages
50,636
Reaction score
3,183
Location
A black hole in the universe - JHB
Good morning everyone

I need some help please and I am a basic user on Excel.
I have a payslip system I had made but want to lock down certain cells only.
So where it has anything in orange I want to completely lock that down from me being able to change that.
I only want to be able to change the Green section.
Please could you assist me?
I have about 50 of these in one book so they all need to be the same.

749541
 
If you
marine1 said:
I tried all that and it either locks the whole bloody sheet or not at all.
I tried to unlock certain cells but it doesnt allow it to be edited

View attachment 749545

View attachment 749547
Click to expand...

Select the range/cells you want unlocked and right click. There should be a check box for it to be locked/unlocked

Edit: missed a step: go to format cells and select the protection tab
 
**Edit** Ignore - problem solved

@Daruk is correct in how you lock and unlock cells. And it seems that you are following the steps.

You do select the cells you want unlocked, you find a quick button on the Home Tab
749593
Press the lock to remove the locked status.
When you do the protection, do not allow everything. No inserting/deleting of rows or columns. You can also prevent then from selecting the locked cells.
749597

My preferred method is to unlock the whole sheet - and then lock only those cells that I need locked. Experience tells me that people will always come running to be allowed to make changes.

Not sure why this isn't working for you though. It always does. Perhaps unlock everything. Any formula cells will have the green arrow telling you that they are unprotected. Then lock the ones that you want.
 
Last edited:
Daruk said:
1. Start with unlocked sheet.
2. RIght click cell / range to unlock and select format cells
View attachment 749555

3. Click the protection tab and unselect the 'locked' box then click OK:
View attachment 749557

4. On the Review tab, click 'Protect sheet':
View attachment 749561

5. Choose your protection options and click OK

View attachment 749563

You will be unable to enter anything into all cells except the unprotected cells:
View attachment 749565

View attachment 749567


@marine1 I think your issue is here:
View attachment 749569

Those rights are way too promiscuous - you're allowing all users everything including inserting stuff, formatting stuff, deleting stuff...
Click to expand...
GOD BLESS U !!!!!!! Thank you so much, your explanation was 100% spot on
I owe you
Not sure why it has to be co complicated to find a solution online, yet your explanation was easy and 100 % correct, thanks again man
 
The Allow Edit Changes works too but don't unlock any cells beforehand. All cells must be locked when you select the range. You must use a password.
 
Ok now next question,trying it on multiple sheets but protect sheet is greyed out so I assume protect workbook is the next option ?
All sheets are the same but now it only allows me to lock workbook and options are as follows:
749605


So I locked it and checked that those cells are indeed locked , as in pic above and the ones that I dont want locked are unlocked but I can still make changes to the "locked" cells.

This is when I tried multiple sheets at once
 
marine1 said:
Ok now next question,trying it on multiple sheets but protect sheet is greyed out so I assume protect workbook is the next option ?
All sheets are the same
Click to expand...
Protect sheet should not be greyed out. It works on every sheet. So this is strange.
You can use protect workbook - but it protects for structure changes. So you will have to do the protect sheet on every page first.
 
You have to find out what you are doing to cause it to be greyed out. Let me think about it. Unprotect your other sheets and see if it activates again.
Are you using the range edit feature ?
 
KT-B said:
You have to find out what you are doing to cause it to be greyed out. Let me think about it. Unprotect your other sheets and see if it activates again.
Are you using the range edit feature ?
Click to expand...
I literally started a brand new workbook and copies the info into it, made duplicate sheets then selected all the sheets which have the same info basically in the same cells, I selected all those sheets to protect those sheets at oce but the protect sheets is greyes out, nothing really that I did or changed :confused:
The minute I select more than one sheet it gets greyed out
 
marine1 said:
I literally started a brand new workbook and copies the info into it, made duplicate sheets then selected all the sheets which have the same info basically in the same cells, I selected all those sheets to protect those sheets at oce but the protect sheets is greyes out, nothing really that I did or changed :confused:
Click to expand...
You protect each sheet seperately
 


You could protect using VBA / Macro
This is my preferred method. I have one for unprotecting too. I actually just encode the password in so that it doesn't ask - but it will only ask once (I think)
 
Tried this:

Sub protect_all_sheets()
top:
pass = InputBox("password?")
repass = InputBox("Verify Password")
If Not (pass = repass) Then
MsgBox "you made a boo boo"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password:=craig1
Next
Exit Sub
oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then running this Macro."
End Sub



But it then when clicking F5 says

749617
 
marine1 said:
is it possible write it so I can copy and paste please?
Click to expand...
Sure

Sub ProtectALL()

Dim ws As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password", "Password Input")
For Each ws In ActiveWorkbook.Worksheets

ws.Protect Password:=Pwd, DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True



Next ws

End Sub

And @Daruk is correct - save it as a macro enabled file or you will lose the code. But you could have the code in another book and just run it while in this one. That way the file can remain as it is and the macro doesn't live in it. But will protect all the sheets.
 
Last edited:
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X