Another Question about Save As Dialog box

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
 
M

Mike H

Pete,

Try this

Sub AATest()
savedir = Sheets("Sheet1").Range("A1").Value
fname = Sheets("Sheet1").Range("A2").Value
Application.Dialogs(xlDialogOpen).Show ("c:\" & savedir & "\" & fname)
End Sub

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top