Use the built-in dialogs using VBA in Microsoft Excel

It's not always necessary to invent everything on your own when you can use something that already exists.
You have access to most of the built-in dialogs in Excel and the other applications in Office.
If you want to let the user decide where to save a workbook, you can display the built-in dialog Save as like this:

Application.Dialogs(xlDialogSaveAs).Show

To get a list over all accessible dialogs in Excel, use the Object browser.
To display it, press F2 while you have a module activated. Select Excel as library and look for the
constants beginning with xlDialog....

If you just want to retrieve a filename from the user that you want to use later to open or save a workbook,
you can use the GetOpenFilename or GetSaveAsFilename methods. Both methods displays their respective dialogs,
but the don't open or save the file when the user confirms the dialog. The methods instead returns the complete
filename to the file the user wants to open or save.

FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _
    1, "Custom Dialog Title", , False)
FullFileName = Application.GetSaveAsFilename("DefaultFilename.xls", _
    "Excel files (*.xl*),*.xl*", 1, "Custom Dialog Title")

The result can be used any way you want to. You can later open a selected workbook like this:

Workbooks.Open FullFileName

Or you can save a workbook like this:

ActiveWorkbook.SaveAs FullFileName

When the user has confirmed the FileOpen dialog or the FileSaveAs dialog,
he/she might also have changed the active or current folder, so I often use these two methods as
a simple approach to let the user select a folder. You can find another way of letting the user
select a folder in the Files and folders section.