change save as name

ladbroke

Board Regular
Joined
Jun 2, 2006
Messages
174
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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)
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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"
 

ladbroke

Board Regular
Joined
Jun 2, 2006
Messages
174
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

ladbroke

Board Regular
Joined
Jun 2, 2006
Messages
174
Office Version
  1. 365
Platform
  1. Windows
just out of interest, where would i put that code.. i want it to work with my command button.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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)
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,141,759
Messages
5,708,361
Members
421,566
Latest member
7Nabisco

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
Top