[Resolved] Using Cell Text as filename in VB

apgriffiths

Board Regular
Joined
Jun 2, 2006
Messages
99
Hi all

I would like to write a bit of code so that when a user selects the Save As option the filename is automatically entered into the box as the text from a certain cell.
Can anybody help with this??
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi,

try this
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim file_name As Variant

    If SaveAsUI Then
    Cancel = True
    
    ChDrive Left(ActiveWorkbook.Path, 3)
    ChDir ActiveWorkbook.Path
    
    file_name = Application.GetSaveAsFilename(Sheets(1).Range("A1"))
    If file_name = False Then Exit Sub
    
    ThisWorkbook.SaveAs file_name & "xls"

    End If

End Sub
 
Upvote 0
Hi

The first part worked ok. The file name was correctly put into the box but after i clicked on save another Save as dialog opened and after that there was an error.
 
Upvote 0
another saveasbox ?

how did you use this code ?

I did this
new file
sheet1 a1 put any string (valid filename)
put code in thisworkbookmodule
menu File/SaveAs

got box
clicked OK
file was saved
(end code-execution)
 
Upvote 0
Hi Erik

I managed to sort it out. I changed the sheet name and i had originally referenced a merged cell and it didnt like it for some reason so ive now just referenced a single cell and it all works great.

Cheers
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,847
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top