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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.