Excel assistance

[TribbleZA]

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

View attachment 749617

This is messy without declarations . No idea that that Top: is either. It is using a loop but using a collection is much easier.
That code has too many oopses

 

[marine1]

Sure

Sub ProtectALL()

Dim ws As Worksheet
Dim Ped 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

Ok I used this then when using F5 it says the following:
After using any password, for eg: 12345 or Pwd

 

[TribbleZA]

Ok I used this then when using F5 it says the following:
After using any password, for eg: 12345 or Pwd

View attachment 749621

You have some sheets already protected ? I did not put code in that checks the existing state. Could be that

 

[marine1]

You have some sheets already protected ? I did not put code in that checks the existing state. Could be that

no nothing protected yet, I think

 

[TribbleZA]

no nothing protected yet, I think

Strange - the code runs well here on all sheets. Let me think. @Daruk any ideas ?
You have my error there - ped should be pwd. You were too quick with the copy. But it should still work. Strange.

 

[Dairyfarmer]

Ok now next question,trying it on multiple sheets but protect sheet is greyed out so I assume protect workbook is the next option ?

Ok just a quick one.
Why not make an input sheet. Make each payslip just links to the input sheet. That way ALL payslips are protected all the time and you only have to unprotect the columns in the input sheet.

 

[TribbleZA]

Your snippet is working fine on my machine:
View attachment 749635

Only correction was the Ped = InputBox..... (should be Pwd = InputBox....)

Yes - as I mentioned it is fixed above. But still runs with the error as a declaration is not always needed for strings. Just correct to have one.

 

[marine1]

I tried the macro on one sheet and it works but when selecting on multiple it fails
So it seems for some reason when selecting multiple sheets, even 2 or more it gives a runtime error 1004

Sub protectsheets()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
wsheet.Protect Password:="craig"
Next wsheet
End Sub

 

[marine1]

Its fine I will just run the macro for each sheet not sure why its giving an error on multiple sheets

 

[TribbleZA]

I tried the macro on one sheet and it works but when selecting on multiple it fails
So it seems for some reason when selecting multiple sheets, even 2 or more it gives a runtime error 1004

Sub protectsheets()
Dim wsheet As Worksheet
For Each wsheet In ActiveWorkbook.Worksheets
wsheet.Protect Password:="craig"
Next wsheet
End Sub

Don't select the sheets. Just be on the first sheet when you run

Use the code I or @Daruk gave you but do not group the sheets. Just be on the first one and run the code

 

[TribbleZA]

Its fine I will just run the macro for each sheet not sure why its giving an error on multiple sheets

It is running a collection - by selecting them you are making them a group. It won't work.

 

[TribbleZA]

Yes it needs an active sheet. Grouping makes multiple active and uses a different process.

 

[HAL 9000]

If each sheet has EXACTLY the same structure & identical layout - simply record a macro to capture the steps you apply to lock / unlock the various cells, as well as protecting the sheet & structure, onto the first sheet, then simply re-run the macro again on each subsequent sheet thereafter.

Handy tip - when originally recording the macro, allocate it a keyboard shortcut - I usually use CTRL+m - if I am only using a single macro in a workbook.