change save as name

ladbroke

Board Regular
Joined
Jun 2, 2006
Messages
175
Office Version
  1. 365
Platform
  1. Windows
The other day i asked for help to get a command button to load up the save as function..

Now my query is, once its loaded, is it possible to change its save name.

What i would prefer, is when i press 'save as' command button that the save name would be what is written in cell C1 on sheet 2..

is this actually possible, and if so, can someone please post a code.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Hi,

all you need to do is to replace the filename by
Sheets(2).Range("C1")

if you need more help then post your code

also a "search" would provide you with many examples
use "search"link on top of the page
search for all keywords
"saveas cell reference"

kind regards,
Erik

EDIT: (I used Google advanced search and got better threads at first sight)
 
Upvote 0
Yes, what Erik says (and is suggesting) is the most common way you see this done.
However, if you're needing to actually show the SaveAs dialogbox (to allow the user
to choose a different folder or something) then I'm not sure you can assign the SaveAs
name into it. (at least not with vba anyway.)

Is there a reason you need to show the dialogbox?
 
Upvote 0
Hi, Dan, meeting again :)

I was waiting for ladbrokes code, but something like this would solve that problem
Code:
Option Explicit

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(2).Range("C1"))
    If file_name = False Then Exit Sub
    
    ThisWorkbook.SaveAs file_name & ".xls"

    End If

End Sub

God bless you!
Erik

EDIT: added a DOT before "xls"
 
Upvote 0
Thanks Erik, just got online.

this is the code i get to load up the save as..

ill try the above code first.

Code:
Private Sub CommandButton1_Click() 
Application.GetSaveAsFilename 
End Sub
 
Upvote 0
just out of interest, where would i put that code.. i want it to work with my command button.
 
Upvote 0
Thanks Erik, just got online.

this is the code i get to load up the save as..

ill try the above code first.

Code:
Private Sub CommandButton1_Click() 
Application.GetSaveAsFilename 
End Sub
this code will not do anything than allow you to pick a name
it will not save your workbook

"my" code should go to the workbookmodule
it will only be triggered when there is a SaveAs (manually or by code)
 
Upvote 0
Morning Erik.
That works just like it should. :biggrin:
That's what I tried yesterday but I guess I must've been doing something a little different.
 
Upvote 0
Dan,
Nice to work in team, we can get more results :)

ladbroke,
missed your reply
to work with my command button
this part should go in your buttoncode
Code:
    ChDrive Left(ActiveWorkbook.Path, 3)
    ChDir ActiveWorkbook.Path
    
    file_name = Application.GetSaveAsFilename(Sheets(2).Range("C1"))
    If file_name = False Then Exit Sub
    
    ThisWorkbook.SaveAs file_name & ".xls"
I just noticed there was no DOT before "xls" in previous code

NOTE: the workbookcode from above will only work when SaveAs is done manually
quote out the lines
Code:
    If SaveAsUI Then
    End If
to work under all circumstances

best regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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