Search code examples
excelvbauserform

Saving an excel file based on prepopulated cells


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

Solution

  • 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