P
Pete Rooney
Good morning,
I want to be able to display the Save As Dialog box - but - before I do it,
I want to change the default Save As folder to whatever value is contained in
a cell on a worksheet so when the dialog is displayed, it's pointing to the
specified folder.
Ideally, I also want the filename box to contain a value contained in
another variable, based on other cells in the worksheet, which I don't have a
problem with, so that the Save As dialog box shows the variablein the File
Name field, but which the user has the opportunity to change before clicking
"Save".
So far, here's what I have, but the first problem is, when the dialog box is
displayed, the folder showing is the one where the workbook was last saved,
NOT the folder based on the value in the cell.
The value in the folder cell is valid, as I don't get an error message at
the CHDIR command - it just isn't reflected in the SaveAs dialog box.
Also, I don't have any idea yet as to how to put the user-amendable value in
the File Name field yet.
Here's what I have so far:
Sub AATest()
Dim DropDownsSheet As Worksheet
Dim DefaultFolderCell As Range
Dim DefaultFolderValue As String
Set DropDownsSheet = ActiveWorkbook.Worksheets("Dropdowns")
Set DefaultFolderCell = DropDownsSheet.Range("DefaultFolder")
DefaultFolderValue = DefaultFolderCell.Value
MsgBox (DefaultFolderValue)
ChDir DefaultFolderValue
Application.Dialogs(xlDialogSaveAs).Show
End Sub
Thanks in advance for your help.
Regards
Pete
I want to be able to display the Save As Dialog box - but - before I do it,
I want to change the default Save As folder to whatever value is contained in
a cell on a worksheet so when the dialog is displayed, it's pointing to the
specified folder.
Ideally, I also want the filename box to contain a value contained in
another variable, based on other cells in the worksheet, which I don't have a
problem with, so that the Save As dialog box shows the variablein the File
Name field, but which the user has the opportunity to change before clicking
"Save".
So far, here's what I have, but the first problem is, when the dialog box is
displayed, the folder showing is the one where the workbook was last saved,
NOT the folder based on the value in the cell.
The value in the folder cell is valid, as I don't get an error message at
the CHDIR command - it just isn't reflected in the SaveAs dialog box.
Also, I don't have any idea yet as to how to put the user-amendable value in
the File Name field yet.
Here's what I have so far:
Sub AATest()
Dim DropDownsSheet As Worksheet
Dim DefaultFolderCell As Range
Dim DefaultFolderValue As String
Set DropDownsSheet = ActiveWorkbook.Worksheets("Dropdowns")
Set DefaultFolderCell = DropDownsSheet.Range("DefaultFolder")
DefaultFolderValue = DefaultFolderCell.Value
MsgBox (DefaultFolderValue)
ChDir DefaultFolderValue
Application.Dialogs(xlDialogSaveAs).Show
End Sub
Thanks in advance for your help.
Regards
Pete