Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic 'Save As:' question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatic 'Save As:' question
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 "Ray" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic save as for CSV | Excel Discussion (Misc queries) | |||
Where does Save As Automatic Backup save its files? | Excel Discussion (Misc queries) | |||
Automatic Save As Macro | Excel Discussion (Misc queries) | |||
Automatic Save As | Excel Discussion (Misc queries) | |||
Automatic Save As | Excel Programming |