PDA

View Full Version : [SOLVED] Passing variables to run a macro on multiple tabs



tkaplan
01-29-2015, 09:16 AM
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
01-29-2015, 10:13 AM
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
01-29-2015, 10:19 AM
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
01-29-2015, 10:22 AM
Since you already have the input values, remove those input parameters from your other routine. Put both routines in a Module.

Paul_Hossler
01-29-2015, 12:41 PM
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
01-29-2015, 12:51 PM
Private Sub Workbook_Open()
For Each sh In Sheets
sh.PageSetup.CenterHeader = Application.UserName & vbTab & Date
Next
End Sub

tkaplan
01-29-2015, 04:13 PM
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
01-29-2015, 04:21 PM
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
01-29-2015, 05:25 PM
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
01-29-2015, 05:33 PM
Edit: Didn't read the brief properly

Paul_Hossler
01-29-2015, 06:57 PM
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
01-31-2015, 05:56 PM
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