VBA Application.Run of Private Sub Issue

lac0403

New Member
Joined
Apr 25, 2008
Messages
49
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.

Code:
Private Sub ProtectAll()
 
Dim ws As Worksheet
Dim pwd As String
 
For Each ws In Worksheets
    ws.Protect Password:="dave"
Next ws
 
End Sub
Code:
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
Code:
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,466
Office Version
365
Platform
Windows
Which workbook are the sheets you trying to protect/unprotect in?
 

lac0403

New Member
Joined
Apr 25, 2008
Messages
49
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

New Member
Joined
Apr 25, 2008
Messages
49
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,466
Office Version
365
Platform
Windows
As it is, without any workbook references, the code will run on whatever workbook is active at the time it's executed.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,970
Messages
5,411,579
Members
403,380
Latest member
ifog671

This Week's Hot Topics

Top