Copy cell data into file name in save as file name

[tomneedshelp]

I have a read only file that I want users to be able to save the workbook in the same folder as the original read only file which I will not know ahead of time - up to the user. I have set up a "Save as " button on the worksheet using Application.GetSaveAsFilename which works fine. When the "Save As" window pops up, I would like "File Name" box populated with data from cells in the worksheet as a "suggested name" that will make sense when we pull it up later - (it is an price and order sheet). The cell names are "H7","H4" and "H14", If any of these are empty, it would be nice to be able to put up a text message to tell them to populate it correctly.

Thanks in advance

[Leith Ross]

Hello tomneedshelp,

To add a document name to the SaveAs dialog box, you have to call it from the Excel Built-in Dialogs. The macro below does that. Since this method only returns True or False, the file name and path are retrieved from the Recent Files list. The last saved file will always be at the top of the list.

Please Login or Register  to view this content.

[tomneedshelp]

Keith, Thanks for the help - it saves the file as you said, but I am not getting the message box to come up as expected when a cell is empty. I set up a simple Message Box Button to try out the message box function and that comes up every time, but when I put in this code, it doesn't seem to come up when the target cell is empty.

Did I screw something up?

Sub SaveFile()

If IsEmpty(Trim(Range("H7"))) Then
MsgBox "The file name is incomplete. Please Fill In Dealer Name.", vbExclamation
End If
End Sub

[tomneedshelp]

Got it,

For some reason (way over my head) I had to eliminate the Trim statement and then it worked fine.

Thanks for your help

[Leith Ross]

Hello tomsneedshelp,

Nice work! That was my fault. IsEmpty requires a Variant argument, in this case the cell. Trim removes the leading and trailing spaces from the cell value and returns a string value.