Automatic 'Save As:' question

[Guest]

Hello -

I'm relatively new to VBA, but am learning quite fast. I'm working on a
user-template and thought I had an 'issue' solved, but apparently I was
wrong. Here's my code:
Private Sub SaveNewVersion_Click()
Ans = MsgBox("Did you update the data links yet?", vbYesNo)
Select Case Ans
Case vbYes
'Remove formulas to make file smaller
Call ValueOutFormulas
'Hide the Admin tab to prevent confusion
Sheets("Admin").Select
ActiveWindow.SelectedSheets.Visible = False
With ActiveWindow
.DisplayWorkbookTabs = False
End With
With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = "\\\Common\Logistics\Service Level\SL06\"
.Title = "Please select a location for the Availability Update"
.Show
If Err <> 0 Then MsgBox "File was NOT saved!"
If ThisWorkbook.Saved = True Then ThisWorkbook.Close
If ThisWorkbook.Saved = False Then _
MsgBox "File_Save was cancelled!"
Sheets("Admin").Visible = True
With ActiveWindow
.DisplayWorkbookTabs = True
End With
Sheets("Admin").Select
End With
Case vbNo
MsgBox "You must do that first!"
End Select
End Sub

What's SUPPOSED to happen is that the user presses a button to 'Save As',
all formulas are valued out, the Admin tab is hidden, all tab-names are
hidden, and the file is saved. However, I have TWO problems -- If/when the
user hits 'OK' from Save As dialog box, 1) the file isn't saved at all & 2)
the 'File_save cancelled' msgbox pops up (and tabs are unhidden) every time
(not just on cancellation).

Any ideas on how to change the code to fix these problems? I want to save
the file as a write-protected Excel workbook.

Thanks!
Ray

 

[NickHK]

Ray,
The dialog only gives you a path/file name, but does actually save the file.
You have .SaveAs Filename yourself, so you have to grab the return value
from Application.FileDialog(msoFileDialogSaveAs)
I use:
Dim RetVal as Variant

RetVal=Application.GetSaveAsFileName(...etc
If RetVal=...

Check the help for GetSaveAsFileName.

NickHK