VBA to unprotect all sheets run my code and then protect.

[pete4monc]

Hi guys

I'm hoping someone can help me, I've got some code which runs perfect (from button click) but I need to password protect the sheet so no one fiddles oops I mean changes anything.
Problem is when the sheets are protected the vba doesn't run? VBA on one sheet will populate cells on another.

I have done this vba for one sheet and it works

Sheets("CustomerService").Unprotect "Pete"
MY CODE HERE
Sheets("CustomerService").Protect "Pete"

So I modified it to run on all sheet names and it fails? Any ideas?

Sheets("PCD" , "Sales", "PCDPlanning",  "CustomerService1").Unprotect "Pete"
MY CODE HERE
Sheets("PCD" & "Sales" & "PCDPlanning" & "CustomerService1").Protect "Pete"

Thanks in advance guys.

 

[mumps]

Are you running the code on one sheet at a time or are you looping through all the sheets and running the code on all of them at once?

 

[pete4monc]

The code will be running on multiple sheets at one time.

 

[mikerickson]

Dim oneSheet as Worksheet

For Each oneSheet in ThisWorkbook.Worksheets
    oneSheet.Unprotect Password:= "Pete"
Next oneSheet

' your routine

For Each oneSheet in ThisWorkbook.Worksheets
    oneSheet.Protect Password:= "Pete"
Next oneSheet

 

[mumps]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets(Array("PCD", "Sales", "PCDPlanning", "CustomerService1"))
        ws.Unprotect Password:="Pete"
        'your code here
        ws.Protect Password:="Pete"
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[dmt32]

You can set UserInterFaceOnly to True - this is an optional argument of the Protect Method.

Setting this argument to True should allow all your macros to run on the required Worksheet(s) that are protected with or without a password.

You will need to do this each time workbook is opened which can be applied in the Workbook_Open event

example

Private Sub Workbook_Open()
    Dim ws As Worksheet

    For Each ws In Worksheets(Array("PCD", "Sales", "PCDPlanning", "CustomerService1"))

        ws.Protect Password:="Pete", UserInterFaceOnly:=True
    Next ws
  
End Sub

Now applied, hopefully, your macros should all work ok


Dave

 

[pete4monc]

Dim oneSheet as Worksheet

For Each oneSheet in ThisWorkbook.Worksheets
    oneSheet.Unprotect Password:= "Pete"
Next oneSheet

' your routine

For Each oneSheet in ThisWorkbook.Worksheets
    oneSheet.Protect Password:= "Pete"
Next oneSheet

Hi Mike
Unfortunately nothing happens. After the VBA it just jumps to the last tab but nothing's in my macro has worked?
Sorry

 

[pete4monc]

Appreciate the help, but my coding already has

Dim ws As Worksheet

I have already tried this and it throws up a conflict?

I've attached my code just in case it helps.

Sub CopyRange2()

'CustomerService, Purchasing, LWProduction & Purchasing1 Macro


Dim Answer As VbMsgBoxResult

    Answer = MsgBox("Is the curser in the correct place?", vbYesNo, "Run macro")
    If Answer = vbYes Then
     
   
[B]Sheets("PCD" , "Sales", "PCDPlanning",  "CustomerService1").Unprotect "Pete"[/B]

                
    Application.ScreenUpdating = False
    Dim lCol As Long, sAddr As String, fnd As Range, x As Long, ws As Worksheet
    lCol = ActiveSheet.Cells(4, Columns.Count).End(xlToLeft).Column
    x = ActiveCell.Row
    Set fnd = Rows(x).Find("y", LookIn:=xlValues, LookAt:=xlWhole)
    If Not fnd Is Nothing Then
    
        sAddr = fnd.Address
        Do
        Range("I" & (ActiveCell.Row)).Select
        ActiveCell.Value = Date

            Set ws = Sheets(Cells(4, fnd.Column).Value)
            
            With ws
                Range("A" & x & ":G" & x).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            End With
            Set fnd = Rows(x).FindNext(fnd)
        Loop While fnd.Address <> sAddr
        sAddr = ""
    End If
    Application.ScreenUpdating = True
    
    Range("M5:N999").Select
    Selection.ClearContents
    Range("A2").Select
    
    MsgBox "All matching data has been copied."
    
    End If

    
[B]Sheets("PCD" & "Sales" & "PCDPlanning" & "CustomerService1").Protect "Pete"[/B]


End Sub

 

[mumps]

Is the sheet you want to protect/unprotect represented by Sheets(Cells(4, fnd.Column).Value)?

 

[pete4monc]

Is the sheet you want to protect/unprotect represented by Sheets(Cells(4, fnd.Column).Value)?

Yes.