Macro for a "Save Form" Button. Help please.

[codyssey]

Hello!

I have created an xlsm file that contains two worksheets. One of them is a locked down form that contains a list of options and pricing for our dealers. the top row is frozen and has a print, save form, email form, and reset button. The problem I am having is with the save form button. I spent almost 4 hours troubleshooting my code today but I still run into errors that I cannot seem to fix, and it doesn't really work the way I was hoping. I'm really hoping someone can give me a fresh idea on this code. Here's what I would like to happen When the button is clicked:

1. The user clicks save form button.
2. A save as dialog box pops up, defaulting to the directory the master xlsm template is in (the current directory)
3. User is only able to save as xlsm extension
4. A field on the form, J8, contains a PO number. I would like the new filename in the save as dialog field to display "existing filename PO_("J8").xlsm" or, in order to differentiate the file name if a PO is not entered, append the current date instead of the PO.
5. What I was doing was replacing the string " PO_.xlsm" with ".xlsm" to remove the PO label from the filename if they didn't enter anything in J8
6. When/if they click cancel instead of save on the dialogue box, the box should close without error :P (my major issue today) and if they click save, then the file is saved as the new name, plus the PO. If a PO is not entered, then save with the current date appended.
7. After the new file is saved, the original is left untouched/unsaved

I am sorry I don't have the code I used at the moment. It's at work, and I figured I would seek insight tonight and hopefully someone can give me some pointers so that tomorrow can be more productive. Thank you all!

 

[V_Malkoti]

See if this helps:

Sub testsaveas()
    Dim strDefaultName As String, strPONum As String, strpath As String
    
    strPONum = ThisWorkbook.Sheets("Sheet1").Range("J8").Value
    strDefaultName = Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1) & "_"
    If Trim(strPONum) <> "" Then
        strDefaultName = strDefaultName & "PO_" & strPONum
    Else
        strDefaultName = strDefaultName & Format(Date, "yyyymmdd")
    End If
    strDefaultName = strDefaultName & ".xlsm"
    
    ChDir ThisWorkbook.path
    strpath = Application.GetSaveAsFilename(strDefaultName, fileFilter:="Macro enabled file (*.xlsm), *.xlsm")
    
    If strpath <> "False" Then
        msgbox "You selected " & strpath
    Else
        msgbox "You clicked cancel"
    End If
    
End Sub

 

[codyssey]

Oh my lawd. Thank you very much, Malkoti. That worked.

Here's one more thing that would be nice that I am just now thinking of since I have it in front of me.

I'll be using this code in about 75 different order forms that all have a different worksheet name. For example,

strPONum = ThisWorkbook.Sheets("DVN 6X13 PRICE LIST").Range("J8").Value

Can we make ThisWorkbook.Sheets reference the "2nd" or "current" worksheet somehow, so I don't need to go through and change he terms in each set of code? I was using ActiveWorksheet.sheets or something but I don't think it works.

I just took a VBA course. Passed with an A. The obviously didn't push me enough! Then again, I have little experience inside excel.

 

[V_Malkoti]

You can refer to second sheet as:

strPONum = ThisWorkbook.Sheets(2).Range("J8").Value

For active sheet:

strPONum = ThisWorkbook.Activesheet.Range("J8").Value