What I am trying to achieve is this
When I run the code I want it to open the file path & folder named in the in cell (“AE8”) and name the workbook from the value in cell (“AE10”). This is working fine
Then automatically save the file with a msgbox saying saved.
If file path & folder named in the in cell (“AE8”) does not exist I want it to go to InvalidDirectory
Then open up the root folder named in the cell("AB8") so the appropriate folder can be selected and save into that folder manually.
But it will not automatically save the workbook it just opens the window of the folder where I want to save the workbook waiting for me to save it manually, when I press the save button it will save OK, and the msgbox opens up just fine, but then the code goes on and picks up the root folder from InvalidDirectory: ChDir Range("AB8").Value
And opens a second window so I can select a folder asking to manually save again
The other issue is even when I have saved the file the top part of the code where I just want it to save as normal does not work it runs the code again rather than just saving.
Any help is appreciated
Full code below
When I run the code I want it to open the file path & folder named in the in cell (“AE8”) and name the workbook from the value in cell (“AE10”). This is working fine
Then automatically save the file with a msgbox saying saved.
If file path & folder named in the in cell (“AE8”) does not exist I want it to go to InvalidDirectory
Then open up the root folder named in the cell("AB8") so the appropriate folder can be selected and save into that folder manually.
But it will not automatically save the workbook it just opens the window of the folder where I want to save the workbook waiting for me to save it manually, when I press the save button it will save OK, and the msgbox opens up just fine, but then the code goes on and picks up the root folder from InvalidDirectory: ChDir Range("AB8").Value
And opens a second window so I can select a folder asking to manually save again
The other issue is even when I have saved the file the top part of the code where I just want it to save as normal does not work it runs the code again rather than just saving.
VBA Code:
If BeenSaved Then
ThisWorkbook.Save
Full code below
VBA Code:
Sub Save()
Static BeenSaved As Boolean
Dim mySaveFile As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If BeenSaved Then
ThisWorkbook.Save
Else
' Changing drive letter
ChDrive "L:\"
' Changing directory
ChDir Range("AE8").Value
On Error GoTo InvalidDirectory
' Prompt for new file location
mySaveFile = Application.GetSaveAsFilename(Range("AE10").Text & ".xlsm", "Microsoft Excel Workbook (*.xlsm), *.xlsm")
If mySaveFile = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=mySaveFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
' Set BeenSaved flag TRUE so will just do a Save next time
If Err = 0 Then BeenSaved = True
ActiveWorkbook.Save
MsgBox "File Saved!"
End If
InvalidDirectory:
ChDir Range("AB8").Value
' Prompt for new file location
mySaveFile = Application.GetSaveAsFilename(Range("AE10").Text & ".xlsm", "Microsoft Excel Workbook (*.xlsm), *.xlsm")
If mySaveFile = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=mySaveFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWorkbook.Save
MsgBox "File Saved!"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub