Saving an excel file based on prepopulated cells

Question

I made a Commandbutton that will allow the user to save the file based on the values within the excel cells in which the cells are pre-populated to begin with. Also how do you implement this fuction GetSaveAsFilename so the user can choose a save destination but not change the title. But I am getting an error executing this code.

Private Sub CommandButton2_Click()
Sub SaveMyWorkbook()

    Dim strPath As String
    Dim strFolderPath As String

    strFolderPath = "C:\Users\"

    strPath = strFolderPath & _
        DoNotPrint - Setup.Range("C7").Value & " " & _
        DoNotPrint - Setup.Range("C8").Value & " " & _
        DoNotPrint - Setup.Range("C45").Value & " " & _
        DoNotPrint - Setup.Range("C9").Value & ".xlsm"

End Sub

@dwirony it looks like its going the be name of the workbook. — Zack E, Mar 08 '19 at 20:11
@dwirony I have multiple worksheets and one of them is called "DoNotPrint - Setup" where the values come from — GoldFusion, Mar 08 '19 at 20:13
The error is am getting is "Expected end sub" but if I do add another end sub same error — GoldFusion, Mar 08 '19 at 20:17
That error is because as @TimWilliams mentioned. You have `End Sub` at the end of the code twice. — Zack E, Mar 08 '19 at 20:19
Even If I remove an extra "End Sub" getting same error and also the "Private Sub CommandButton2_Click()" is highlighted yellow — GoldFusion, Mar 08 '19 at 20:20
You will need to remove `Sub SaveMyWorkbook()` as well since it is not needed. — Zack E, Mar 08 '19 at 20:23

score 3 · Answer 1 · answered Mar 08 '19 at 20:13

Best guess:

With Thisworkbook.sheets("DoNotPrint - Setup")

    strPath = strFolderPath & .Range("C7").Value & " " & _
                              .Range("C8").Value & " " & _
                              .Range("C45").Value & " " & _
                              .Range("C9").Value & ".xlsm"

End with

Selecting a folder to save to:

VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable

Zack E · Answer 2 · 2019-03-08T20:21:36.680

1

To allow the user to choose the folder I use this:

Private Sub CommandButton2_Click()

    Dim strPath As String
    Dim strFolderPath As String

    strFolderPath = "C:\Users\"

    strPath = strFolderPath & _
        DoNotPrint - Setup.Range("C7").Value & " " & _
        DoNotPrint - Setup.Range("C8").Value & " " & _
        DoNotPrint - Setup.Range("C45").Value & " " & _
        DoNotPrint - Setup.Range("C9").Value & ".xlsm"

    With Application.FileDialog(msoFileDialogSaveAs)
        .AllowMultiSelect = False
        .InitialFileName = strPath
        .FilterIndex = 2
        .Title = Place Title Here if you want
            If .Show = -1 Then .Execute
    End With
End Sub

edited Mar 08 '19 at 20:21

answered Mar 08 '19 at 20:13

Zack E

696
7
23

Is there a way for you to implement ".range" values I have included above so the file name is automated? – GoldFusion Mar 08 '19 at 20:19
Getting "Run-time error 424 Object required", I removed the ".Title" because " .Initial file name" is the same thing – GoldFusion Mar 08 '19 at 20:25
1

VBA is not going to like implicitly using worksheet names as objects. Look at the answer by Tim Williams for a worksheet object creation. – Mark Balhoff Mar 08 '19 at 20:27
I completely overlooked that part @MarkBalhoff. Thanks for pointing that out. – Zack E Mar 08 '19 at 20:29

score 0 · Accepted Answer · answered Mar 08 '19 at 20:38

Based on Tim's and Zack's Answer, this worked

Private Sub CommandButton2_Click()
    Dim strPath As String
    Dim strFolderPath As String

    strFolderPath = "C:\Users\"


With ThisWorkbook.Sheets("DoNotPrint - Setup")

    strPath = strFolderPath & .Range("C7").Value & " " & _
                              .Range("C8").Value & " " & _
                              .Range("C45").Value & " " & _
                              .Range("C9").Value & ".xlsm"
    End With
    With Application.FileDialog(msoFileDialogSaveAs)
        .AllowMultiSelect = False
        .InitialFileName = strPath
        .FilterIndex = 2

            If .Show = -1 Then .Execute
    End With

End Sub

Saving an excel file based on prepopulated cells

3 Answers3