It was working...can't find my mistake.

[jmk15315]

I have no formal excel training and am self-taught (mostly from the great people on this site)

Everything works great, or was working great is probably the better statement, but then it just stopped working.

I have a workbook that contains different variants of a customer proposal. In order to make it easier for my sales team to create a standard quote, I created a user form that allows for the selection of the appropriate item and the form will take care of the rest.

The sales rep inputs information on the userform which saves the data on "sheet2" and the file name is then created using this information.

I have verified the information is being directed to the correct sheet ("Sheet2") and in the correct cells ("C2" , "C23" , "C6")

Not sure where I have gone wrong, but hoping someone can point me in the correct direction.

My code starts here

Private Sub CommandButton2_Click()
  
   Dim strPath As String
   Dim saveAsFilename As Variant
   
   
    If Sheet2.Range("C6").Value = "" Then
        MsgBox "No Quote Number has been assigned." & vbNewLine & vbNewLine & "Please enter Quote Reference in order to continue"
        GoTo 999
    Else
        GoTo 100
    End If
   
100
' OPTION BUTTON - 1
   
    If OptionButton1 = True Then
    saveAsFilename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & Sheet2.Range("C2").Value & "." & Sheet2.Range("C23") & " - " & Sheet2.Range("C6"), _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Save")
   
    Sheet4.Visible = True
    Sheet4.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveAsFilename, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Sheet4.Visible = False
   
    Else
    GoTo 200
   
    End If

200
' OPTION BUTTON - 2
 
    If OptionButton2 = True Then
    saveAsFilename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & Sheet2.Range("C2").Value & "." & Sheet2.Range("C23") & " - " & Sheet2.Range("C6"), _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Save")
   
    Sheet5.Visible = True
    Sheet5.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveAsFilename, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Sheet5.Visible = False
   
    Else
    GoTo 300
   
    End If

300
' OPTION BUTTON - 3
 
    If OptionButton3 = True Then
    saveAsFilename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & Sheet2.Range("C2").Value & "." & Sheet2.Range("C23") & " - " & Sheet2.Range("C6"), _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Save")
   
    Sheet6.Visible = True
    Sheet6.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveAsFilename, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Sheet6.Visible = False
   
    Else
    GoTo 400
   
    End If

400
' OPTION BUTTON - 4
   
    If OptionButton4 = True Then
    saveAsFilename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & Sheet2.Range("C2").Value & "." & Sheet2.Range("C23") & " - " & Sheet2.Range("C6"), _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Save")
   
    Sheet7.Visible = True
    Sheet7.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveAsFilename, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Sheet7.Visible = False
   
    Else
    GoTo 500
   
    End If

500
' OPTION BUTTON - 5

    If OptionButton5 = True Then
    saveAsFilename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & Sheet2.Range("C2").Value & "." & Sheet2.Range("C23") & " - " & Sheet2.Range("C6"), _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Save")
   
    Sheet8.Visible = True
    Sheet8.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveAsFilename, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Sheet8.Visible = False
   
    Else
    GoTo 600
   
    End If

600
' OPTION BUTTON - 6

    If OptionButton6 = True Then
    saveAsFilename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & Sheet2.Range("C2").Value & "." & Sheet2.Range("C23") & " - " & Sheet2.Range("C6"), _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Save")
   
    Sheet9.Visible = True
    Sheet9.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveAsFilename, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Sheet9.Visible = False
   
    Else
    GoTo 900
   
    End If

900
    If saveAsFilename = False Then Exit Sub

     Dim Response As VbMsgBoxResult
   
    Response = MsgBox("A copy of the quote has been saved to your selected folder" & vbNewLine & vbNewLine & "Do you want to create another quote?", vbYesNo + vbQuestion, "Continue")
    
    If Response = vbYes Then
        Sheet2.Visible = xlSheetVisible
        Sheet2.Select
        Call RESET_SYSTEM_1
        Call RESET_SYSTEM_2
        Call RESET_SYSTEM_3
        Sheet2.Visible = xlSheetHidden
        Sheet1.Select
       
    Else
        Call SAVE_AND_EXIT
    End If

999

End Sub

 

[Candyman8019]

Can you elaborate on what isn't working? Getting an error message? if so, on what line?

 

[jmk15315]

Sorry,
The information to name the file is not populating in the save as window. I can type in a name, but it is not pulling the info from the worksheet as it is designed to.
i.e. Cells C2, C23 C6

 

[Candyman8019]

Without seeing your spreadsheet it may be tough to troubleshoot. In any of the 3 cells used for your filename, are there any characters that would be considered invalid for a filename, ie #&^<> etc...

 

[jmk15315]

No, Letters and numbers

This is how it comes up now...

This is how it use to come up...

"C2" value = 12345
"C23" value = R00
"C6" value = Test Customer

 

[Candyman8019]

Okay, a couple of things going on here. First...on your second and third cell of the filename you're missing the .value; and you'll also want a file extension at the end, such as .pdf. so try the following:

    saveAsFilename = Application.GetSaveAsFilename( _
        InitialFileName:=Application.DefaultFilePath & "\" & Sheet2.Range("C2").Value & "." & Sheet2.Range("C23").value & " - " & Sheet2.Range("C6").value & ".pdf", _
        FileFilter:="PDF File (*.pdf), *.pdf", _
        Title:="Save")

 

[jmk15315]

That fixed my problem. Not sure how I deleted the ".Value", but nonetheless, it is working again.

Thank you very much. Newbie mistake. Pardon me while I wipe the egg off my face.

 

[Candyman8019]

No problem. I’m happy to help.

 

[jmk15315]

So now I have the issue again. I moved the file from my home computer (Using Excel 2010) to my work computer using (Excel 365) There must be something different between the two versions that is causing an issue...Thought?

 

[Candyman8019]

There's nothing that I can see that would be different between 2010/o365. Do you have multiple copies of this file and maybe grabbed one that wasn't updated? Can you share the script that shows in o365?