VBA Application.Run of Private Sub Issue

lac0403

Board Regular
Joined
Apr 25, 2008
Messages
50
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Which workbook are the sheets you trying to protect/unprotect in?
 
Upvote 0
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.)
 
Upvote 0
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.
 
Upvote 0
As it is, without any workbook references, the code will run on whatever workbook is active at the time it's executed.
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top