Application.GetSaveAsFilename question

[Bishop]

Why can't I get this to work? I'm trying to use GetSaveAsFilename to prompt
the user to save a file using a specific file naming protocol. Examples of
what the filename should look like:

GSO C&A PF Jul 09 wk1 bm
VHeathrow C&A PF Dec 09 wk5 lw
Austin C&A PF May 09 wk2 gh

I'm using IFN to set up what the filename should look like then trying to
set InitialFilename parameter equal to IFN but all I keep getting is "C&A PF
09 ". So basically it's picking up everything between ""'s and excluding the
variables. How do I get the variables in the assignment?
Private Sub PanicSwitch_Click()
Dim AUserFile As Variant
Dim FNwk As String
Dim FNmonth As String
Dim FNname As String
Dim IFN As String

Month7Select = Month7.Value
MonthRSelect = MonthR.Value
WeekSelect = Week.Value
NameSelect = AName.Value
CenterSelect = Center.Value
Cells(1, 25) = Month7Select
Cells(1, 1) = MonthRSelect
Cells(2, 1) = WeekSelect
Cells(1, 2) = NameSelect
Cells(2, 2) = CenterSelect
Unload NotSoFast

If MonthRSelect = "January" Then FNmonth = Jan
If MonthRSelect = "February" Then FNmonth = Feb
If MonthRSelect = "March" Then FNmonth = Mar
If MonthRSelect = "April" Then FNmonth = Apr
If MonthRSelect = "May" Then FNmonth = May
If MonthRSelect = "June" Then FNmonth = Jun
If MonthRSelect = "July" Then FNmonth = Jul
If MonthRSelect = "August" Then FNmonth = Aug
If MonthRSelect = "September" Then FNmonth = Sep
If MonthRSelect = "October" Then FNmonth = "Oct"
If MonthRSelect = "November" Then FNmonth = Nov
If MonthRSelect = "December" Then FNmonth = Dec
If WeekSelect = "Week 1" Then FNweek = wk1
If WeekSelect = "Week 2" Then FNweek = wk2
If WeekSelect = "Week 3" Then FNweek = wk3
If WeekSelect = "Week 4" Then FNweek = wk4
If WeekSelect = "Week 5" Then FNweek = wk5
If NameSelect = "Bishop Minter" Then FNname = bm
If NameSelect = "Carlos Trespalacios" Then FNname = ct
If NameSelect = "Dennis Murphy" Then FNname = dm
If NameSelect = "Gary Hayden" Then FNname = gh
If NameSelect = "Gloria Montoya" Then FNname = gm
If NameSelect = "Kenneth Accomando" Then FNname = ka
If NameSelect = "Lisa Muttillo" Then FNname = lm
If NameSelect = "Lorraine Warburton" Then FNname = lw
If NameSelect = "Warner Langlois" Then FNname = wl

IFN = CenterSelect & "C&A PF" & FNmonth & " 09 " & FNweek & " " & FNname

AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN,
FileFilter:="Excel Workbooks(*.xls),*.xls", FilterIndex:=1, Title:="You Must
Save Before You Proceed")
End Sub

 

[Dave Peterson]

I don't see where fnmonth is set.

IFN = CenterSelect & "C&A PF" & FNmonth & " 09 " & FNweek & " " & FNname

And FNweek/FNWk looks like a problem, too.

 

[Jacob Skaria]

Dear Bishop

When you assign a value to a string variable it should be within double quotes

Should be FNmonth = "Jan"
and NOT FNmonth = Jan

I would suggest using Option Explicit on top of the module to notify such
errors.


If this post helps click Yes

 

[Bishop]

FNmonth is defined in the group of If Then statements:

If MonthRSelect = "January" Then FNmonth = Jan
etc

Good catch on the FNwk. I missed that.

 

[Bishop]

Ok, I added quotes as you suggested. I stepped through the code line by line
and for some reason my If Then statements aren't assigning values to my
variables. After stepping through, when I get to the bottom of the If Then
statement block none of the variables have assigned values. The all read
="". Why aren't they working?

 

[Dave Peterson]

What's Jan?

Did you mean "Jan"?

FNmonth is defined in the group of If Then statements:

If MonthRSelect = "January" Then FNmonth = Jan
etc

Good catch on the FNwk. I missed that.

 

[Bishop]

Ok, figured it out. The "Unload NotSoFast" command was erasing all of my
variables before they could be reassigned via the If Then statements to be
used in the IFN variable. I moved the Unload NotSoFast command below the If
Then block and now it works as intended. I've included the final code below:

Private Sub PanicSwitch_Click()
Dim AUserFile As Variant
Dim FNweek As String
Dim FNmonth As String
Dim FNname As String
Dim IFN As String

Month7Select = Month7.Value
MonthRSelect = MonthR.Value
WeekSelect = Week.Value
NameSelect = AName.Value
CenterSelect = Center.Value
Cells(1, 25) = Month7Select
Cells(1, 1) = MonthRSelect
Cells(2, 1) = WeekSelect
Cells(1, 2) = NameSelect
Cells(2, 2) = CenterSelect

If MonthRSelect = "January" Then FNmonth = "Jan"
If MonthRSelect = "February" Then FNmonth = "Feb"
If MonthRSelect = "March" Then FNmonth = "Mar"
If MonthRSelect = "April" Then FNmonth = "Apr"
If MonthRSelect = "May" Then FNmonth = "May"
If MonthRSelect = "June" Then FNmonth = "Jun"
If MonthRSelect = "July" Then FNmonth = "Jul"
If MonthRSelect = "August" Then FNmonth = "Aug"
If MonthRSelect = "September" Then FNmonth = "Sep"
If MonthRSelect = "October" Then FNmonth = "Oct"
If MonthRSelect = "November" Then FNmonth = "Nov"
If MonthRSelect = "December" Then FNmonth = "Dec"
If WeekSelect = "Week 1" Then FNweek = "wk1"
If WeekSelect = "Week 2" Then FNweek = "wk2"
If WeekSelect = "Week 3" Then FNweek = "wk3"
If WeekSelect = "Week 4" Then FNweek = "wk4"
If WeekSelect = "Week 5" Then FNweek = "wk5"
If NameSelect = "Bishop Minter" Then FNname = "bm"
If NameSelect = "Carlos Trespalacios" Then FNname = "ct"
If NameSelect = "Dennis Murphy" Then FNname = "dm"
If NameSelect = "Gary Hayden" Then FNname = "gh"
If NameSelect = "Gloria Montoya" Then FNname = "gm"
If NameSelect = "Kenneth Accomando" Then FNname = "ka"
If NameSelect = "Lisa Muttillo" Then FNname = "lm"
If NameSelect = "Lorraine Warburton" Then FNname = "lw"
If NameSelect = "Warner Langlois" Then FNname = "wl"

IFN = CenterSelect & " C&A PF " & FNmonth & " 09 " & FNweek & " " & FNname

Unload NotSoFast

AUserFile = Application.GetSaveAsFilename(InitialFileName:=IFN,
FileFilter:="Excel Workbooks(*.xls),*.xls", FilterIndex:=1, Title:="You Must
Save Before You Proceed")
End Sub