Save As Cell content

[Shang_7]

I currently have the Save blocked to where you can only do a Save As.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Not SaveAsUI Then
Cancel = True
End If
End Sub


But I also am wanting when you go to Save As it will have the contents from Sheet "Data" B3 in the filename. I do not care to add the file save location, I only want to add the filename from B3 in .xlsm format. Is that possible to have the name automatically placed in the filename when you click on File/Save As?

 

[Kamolga]

Hi,

This code would save a file with name in B3 in the same location.

Dim DynamicFileName As String
DynamicFileName = Worksheets("Data").Range("B3").Value
ActiveWorkbook.SaveAs Filename:=DynamicFileName

Of course you have to consider you can not save as twice with the same name (I receive a message if I want to replace the existing file)

 

[Shang_7]

It didn't work for me. Maybe I am putting it in the wrong place. I have tried many things and it hasn't worked.

 

[Kamolga]

It didn't work for me. Maybe I am putting it in the wrong place. I have tried many things and it hasn't worked.

Here is what I did:

-Open a new 2016 excel document
-Rename Sheet1 by Data
-Write Exact in cell B3
-Save the file as Book1.xlsm (macro enabled workbook) on my desktop
-Open VBA and insert a module (module1)
-Write

 Sub Test()
Dim DynamicFileName As String
DynamicFileName = Worksheets("Data").Range("B3").Value
ActiveWorkbook.SaveAs Filename:=DynamicFileName
End Sub

-Run the macro Test and I got a file called Exact.xlsm on my desktop which is opened and Book1 is closed.

Hope it helps

 

[Shang_7]

OK I understand now. I was actually hoping to be able to only go to File/SaveAs and the filename that would be there from cell B3 without having to click on run a macro, but rather automatic. Is there a way to do that?

 

[Kamolga]

Simply put a button in the access toolbar or any ribbon that launches the macro (with a floppy disk icon for example)

Sub Saveas()
Application.GetSaveAsFilename Worksheets("Data").Range("B3").Value, "XLSM Files (*.xlsm), *.xlsm"
End Sub

FYI but I do not recommend it, in VBA, there are "events" that can launch macro. For example, it is possible to launch the macro every time the workbook opens, close, you change a sheet, before saving,... or even change the value of this specific cell.

Not only having this macro automatically launch when changing the value of B3 will impact the speed response of the file but the must annoying is that everytime you change the value of the cell, you lose the "undo" function.