2 Problems - Changing VBA password and Inserting Footer in Multiple Workbooks

[motherteresa]

I have a 2 tier problem. First, I helped to create a template with a lot of VBA work that several folks use in the field. To keep people from screwing things up, I password protected the VBA portion. Second, I need to update the footer in each of those workbooks. Each workbook has several worksheets.

So, is there a way to write a macro to a new workbook called...VBAPASS_FOOTER and just give a copy of that workbook to each field rep and have them run the macro to:

1. Open all workbooks in a given field rep's folder (C:\WORKBOOKS) on their laptop and change the VBA code from xxx to edt123 without revealing what my original password was (the "xxx")?
2. Update the footer on all sheets in each of those workbooks?
3. Save the workbooks in the same folder with same name?
4. Have a dialogue box pop up confirming the task of updating all the workbooks in the folder is done?

Here are some challenges that I'm not sure matter:

1. When each workbook opens, a dialogue box opens to remind the user to do something. Hitting the enter key allows you to move into the workbook and begin working.
2. Each worksheet already has a footer.
3. Each worksheet is password protected.
4. Each workbook has 9 worksheets. The first 7 need the update. The last 2 are hidden worksheets that don't.
5. Some of the worksheets are landscape and some are portrait.

I've written the below code in one of my own workbooks to update the footer and it works well (feel free to improve it ). I placed it in the "ThisWorkbook" module, which also has some other code. But with so many workbooks to do for myself and the other field reps, it sure would help to automate the process if possible. Thanks in advance for any help.

Private Sub Workbook_BeforePrint(Cancel As Boolean)    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .CenterFooter = _
        "&""-,Bold""Education is FUN - Join us today"
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = True
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
End Sub

 

[motherteresa]

OK...a lot of looks, but no takers. In the hopes of getting a bite, I'm going to pare it down to just one part of the problem (fingers crossed).

Cindy Ellis provided this solution to open up a number of workbooks in a folder. It works great, But I can't get it to do what I want once I get in there...which is to change the footer of select sheets each with custom margins.

Her code:

Sub ProcessFilesInFolder()
    Dim FolderName As String, filepathname As String, NextFile As String
    Dim wbname As String, dsName As String
    
    FolderName = "C:\temp\"  'Change this to your directory
    NextFile = Dir(FolderName & "*.xlsx", vbNormal) 'change the extension if needed
    
    While NextFile <> ""
    
        filepathname = FolderName & NextFile
        Workbooks.Open FileName:=filepathname
            
        wbname = ActiveWorkbook.Name  'use WBName to refer to the name of the current workbook
        dsName = ActiveSheet.Name 'use DSName to refer to the name of the worksheet
                                  'that was created when you opened the text file
                                  
        '***********your code here*************
        
        'do whatever you need to do to the file here.
        MsgBox (wbname)   'this is just for testing...remove it when you know you're getting the files you want
        
        
        '***********end of your code here*************
        
        
        Workbooks(wbname).Close Savechanges:=True
        
        NextFile = Dir()   'this gets the name of the next file
        
    Wend


End Sub

What I'm trying to insert instead of running the MsgBox (wbname) command:

  Sheet2.PageSetup.CenterFooter = "&""-,Bold""Education is FUN - Join us today"
  .RightFooter = ""
  .LeftFooter = ""
        .LeftMargin = Application.InchesToPoints(0.45)
        .RightMargin = Application.InchesToPoints(0.45)
        .TopMargin = Application.InchesToPoints(1.22)
        .BottomMargin = Application.InchesToPoints(1.25)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
  Sheet4.PageSetup.CenterFooter = "&""-,Bold""Education is FUN - Join us today"
  .RightFooter = ""
  .LeftFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(1.22)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""

Any ideas?