Passing variables to run a macro on multiple tabs

[tkaplan]

I am trying to create a macro that sets up my page headers and footers the same way every time. Each workbook has a different client name that may go on top and effective date. I want the user to enter that information in once, and that information can be stored in some variable so when i run the macro on any tabs in that workbook, the user does not have to enter client name again.

I have the following:

Sub HeaderInfo()


    Dim cName As String
    Dim eDate As String
    
    cName = InputBox("What is the client's name?", "Client Name")
    eDate = InputBox("What is the effective date?")


End Sub

and then i have the next macro for page setup to take in cName and eDate:

Sub PageSetupMacro(cName, eDate)
 
    With ActiveSheet.PageSetup
        .LeftFooter = "&P"
        .LeftHeader = _
        "&""Calibri,Bold""&16" & cName & Chr(10) & "&""Calibri,Regular""&A" & Chr(10) & "Effective " & eDate
    End With


End Sub

when i run HeaderInfo, it is asking me for the information, but then I am not able to run the PageSetupMacro. I don't want to call this macro from the HeaderInfo macro because I want to be able to run the PageSetupMacro as many times as I want within the workbook without having to run the HeaderInfo macro again.

what am I doing wrong?

Thanks!
T

[Kenneth Hobs]

Two ways to do it.
1. Make the variables Public. Good for the whole session.
2. Use the Registry. Good for all sessions.

For (1), move your Dims above the Sub.

Dim cName As String, eDate As String

Sub HeaderInfo()
  cName = InputBox("What is the client's name?", "Client Name")
  eDate = InputBox("What is the effective date?")
End Sub

[tkaplan]

Thanks Ken for taking the time to respond. I tried doing #1 but the PageSetupMacro is still not working. if i use PageSetupMacro(cName, eDate), then it doesn't show up in my list of macros to even run. if i delete it as an argument and just run it as PageSetupMacro, then the values come out blank.

[Kenneth Hobs]

Since you already have the input values, remove those input parameters from your other routine. Put both routines in a Module.

[Paul_Hossler]

You can't run a macro that requires parameters

If you wanted to combine the two macros and run the PageSetup on all sheets, you could do something like this

Option Explicit
Sub HeaderInfo()
    Dim cName As Variant, eDate As Variant
    Dim ws As Worksheet
  
    cName = InputBox("What is the client's name?", "Client Name")
    If Len(Trim(cName)) = 0 Then Exit Sub
    
    eDate = Application.InputBox("What is the effective date?", "Effective Date", , , , , , vbDate)
    If VarType(eDate) = vbBoolean Then
        If Not eDate Then Exit Sub
    End If
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws.PageSetup
            .LeftFooter = "&P"
            .LeftHeader = _
                "&""Calibri,Bold""&16" & cName & Chr(10) & "&""Calibri,Regular""&A" & Chr(10) & "Effective " & eDate
        End With
    Next
End Sub

[snb]

Private Sub Workbook_Open()
   For Each sh In Sheets
      sh.PageSetup.CenterHeader = Application.UserName & vbTab & Date
   Next
End Sub

[tkaplan]

okay, i'm not sure what was happening but once i took out the "option explicit" line from the top it started working. thank you!

[tkaplan]

You can't run a macro that requires parameters

If you wanted to combine the two macros and run the PageSetup on all sheets, you could do something like this

Option Explicit
Sub HeaderInfo()
    Dim cName As Variant, eDate As Variant
    Dim ws As Worksheet
  
    cName = InputBox("What is the client's name?", "Client Name")
    If Len(Trim(cName)) = 0 Then Exit Sub
    
    eDate = Application.InputBox("What is the effective date?", "Effective Date", , , , , , vbDate)
    If VarType(eDate) = vbBoolean Then
        If Not eDate Then Exit Sub
    End If
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws.PageSetup
            .LeftFooter = "&P"
            .LeftHeader = _
                "&""Calibri,Bold""&16" & cName & Chr(10) & "&""Calibri,Regular""&A" & Chr(10) & "Effective " & eDate
        End With
    Next
End Sub

this would require the user to enter the client name and date each time they run the macro, which i was trying to avoid. i wanted them to have to enter the information once and that information gets carried through when the run the second macro on any of the other pages.

[Kenneth Hobs]

As I said.

Option Explicit

Dim cName As String, eDate As String


Sub HeaderInfo()
  cName = InputBox("What is the client's name?", "Client Name")
  eDate = InputBox("What is the effective date?")
End Sub


Sub PageSetupMacro()
  With ActiveSheet.PageSetup
      .LeftFooter = "&P"
      .LeftHeader = _
      "&""Calibri,Bold""&16" & cName & Chr(10) & "&""Calibri,Regular""&A" & Chr(10) & "Effective " & eDate
  End With
End Sub

[Blade Hunter]

Edit: Didn't read the brief properly

[Paul_Hossler]

this would require the user to enter the client name and date each time they run the macro, which i was trying to avoid. i wanted them to have to enter the information once and that information gets carried through when the run the second macro on any of the other pages.

Well, like I said if you wanted to just run one macro which would get the data one time, and then automatically continue to update the other worksheets without having to run a second macro multiple times you could include the For Each ws loop like I did

If you want to run a data input macro and then run a second macro multiple times for each worksheet, then Ken's is the way to go

[mikerickson]

How about a function that remembers the last entry like

Function ClientName(Optional ForceUserEntry as Boolean) As String
    Static MyClientName As String

    If (MyClientName = vbNullString) Or (ForceUserEntry) Then
        ClientName = Application.InputBox("Enter the client's name", default:=MyClientName, type:=2)
        If ClientName = "False" Then ClientName = vbNullString: Exit Function

        myClientName = ClientName
    End If
    ClientName = MyClientName
End Function