VBA Application.Run of Private Sub Issue

[lac0403]

As usual, this is probably a simple answer, but it is stumping me.

I have a couple bits of code that have used for years to ProtectAll, UnProtectAll the sheets for templates. (They are private subs.) That way when I have code to run data imports, etc. I can usually just run the UnprotectAll at the beginning and ProtectAll at the end, and not re-do passwords all the time.

I used Application.Run (ProtectAll). It is not giving me errors, but the sheets are not being unprotected and protected.

Private Sub ProtectAll()
 
Dim ws As Worksheet
Dim pwd As String
 
For Each ws In Worksheets
    ws.Protect Password:="dave"
Next ws
 
End Sub

Private Sub UnprotectAll()
 
Dim ws As Worksheet
Dim pwd As String
pwd = "dave" 'password here
 
'-----baseline password unprotect-----
Application.ScreenUpdating = False
For Each ws In Worksheets
        ws.unprotect Password:=pwd
   Next ws
   
Application.ScreenUpdating = True
 
End Sub

Sub CopyDataIn()
 
'-----test for data
    If IsEmpty(OrigData.Range("B4").Value) Then
        MsgBox ("You Must Put Solid Quote Excel Output File Data into the SQData sheet. (Paste into Cell A1)")
        Exit Sub
    End If
   
Application.Run (UnprotectAll)
   
   
  '(1) find rows of Ops & Materials, for stop & starts
    Dim OpsRow As Long
    OpsRow = OrigData.UsedRange.Find("Operation Summary", LookIn:=xlValues, lookat:=xlPart).Row
      
    Dim MatlRow As Long
    MatlRow = OrigData.UsedRange.Find("Material Summary", LookIn:=xlValues, lookat:=xlPart).Row
      
    Dim ToolRow As Long
    ToolRow = OrigData.UsedRange.Find("Tools", LookIn:=xlValues, lookat:=xlPart).Row
   
  '(2)copy and paste over Operations Data
 
    OrigData.Activate
    OrigData.Range(Cells(OpsRow + 2, 2), Cells(MatlRow - 2, 7)).Copy
    SQDatabase.Activate
    Range("A2").PasteSpecial (xlPasteValues)
   
    OrigData.Activate
    OrigData.Range(Cells(OpsRow + 2, 18), Cells(MatlRow - 2, 18)).Copy
    SQDatabase.Activate
    Range("G2").PasteSpecial (xlPasteValues)
   
  '(3)copy and paste over material data
    Dim LastRow As Long
    LastRow = SQDatabase.Cells(Rows.Count, "A").End(xlUp).Row
   
    OrigData.Activate
    OrigData.Range(Cells(MatlRow + 2, 2), Cells(ToolRow - 2, 7)).Copy
    SQDatabase.Activate
    SQDatabase.Cells(LastRow + 1, 1).PasteSpecial (xlPasteValues)
   
    OrigData.Activate
    OrigData.Range(Cells(MatlRow + 2, 10), Cells(ToolRow - 2, 10)).Copy
    SQDatabase.Activate
    SQDatabase.Cells(LastRow + 1, 7).PasteSpecial (xlPasteValues)
 '(4)update pivot tables
    ActiveWorkbook.RefreshAll
   
    SQDatabase.Range("h2").Select
  '(5) go to next table for final entries
    SQIC.Activate
    Range("C5").Select
   
 
Application.Run (ProtectAll)
   
End Sub

Maybe fresh eyes can help - any help is appreciated

 

[Norie]

Which workbook are the sheets you trying to protect/unprotect in?

 

[lac0403]

All the sheets are in the same workbook. All the code is in the same workbook. The code is in two different modules. The Protect/Unprotect in one module, and the CopyDataIn is in a different module. (None in Workbook or sheets or forms.)

 

[lac0403]

I did not put the file name into the macro, because it is going into a template, and then each time the file is used it will be a different name. If I need to specify the workbook, it would have to work with Active workbook, or something like that.

 

[Norie]

As it is, without any workbook references, the code will run on whatever workbook is active at the time it's executed.