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

jmk15315

Board Regular
Joined
Nov 7, 2021
Messages
73
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Can you elaborate on what isn't working? Getting an error message? if so, on what line?
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
No, Letters and numbers

This is how it comes up now...

1678129024099.png


This is how it use to come up...
1678129120977.png


"C2" value = 12345
"C23" value = R00
"C6" value = Test Customer
 
Upvote 0
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:

VBA Code:
    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")
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top