How to get "Save As" code to allow me to specify a location

Dustan

New Member
Joined
Jun 19, 2012
Messages
47
Hello,

I have a vba userform that I want a command button to allow the user to save the active sheet

The code I have is
Code:
Dim SaveName As String
SaveName = Range("I3") & " Imaging Work Order"
ActiveWorkbook.SaveAs FileName:=(SaveName), FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False

My problem is that this doese not allow the user to select where the file is saved. It does save with the name being in the format "(Range I3) Imaging Work Order" but I don't know where is saves to.

Any help is appreciated!

Thanks!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Try something like this...

Code:
    [color=darkblue]Dim[/color] SaveName    [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] MyPath      [color=darkblue]As[/color] [color=darkblue]String[/color]


    [color=green]'Prompt user to select the save path[/color]
    [color=darkblue]With[/color] Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\Temp\"               [color=green]' Default path[/color]
        .Title = "Please Select a Folder"
        .ButtonName = "Select Folder"
        .AllowMultiSelect = [color=darkblue]False[/color]
        .Show
        [color=darkblue]If[/color] .SelectedItems.Count = 0 [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]   [color=green]' User clicked cancel[/color]
        MyPath = .SelectedItems.Item(1) & "\"
    [color=darkblue]End[/color] [color=darkblue]With[/color]




SaveName = Range("I3") & " Imaging Work Order"


ActiveWorkbook.SaveAs Filename:=MyPath & SaveName, FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        [color=darkblue]False[/color], CreateBackup:=[color=darkblue]False[/color]
 
Upvote 0

Forum statistics

Threads
1,211,742
Messages
6,103,654
Members
447,876
Latest member
rodqntr

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