How can I open open a new file, run a userform there, and close the old file?

[Binyamin]

Hi, I use this forum a lot for learning purposes, and this is the first question that I've had that I can not find the answer to.


Here is my code:

​

Function CheckAndBackup()

'Check last backup date​

Dim LastBackupDate As Date​

Dim DateDifferenceCheck As Integer​

​

LastBackupDate = Sheets("calculations").Range("w22").Value​

DateDifferenceCheck = Now() - LastBackupDate​

​

If DateDifferenceCheck > 10 Then 'backup is required​

​

MsgBox DateDifferenceCheck & " Days have passed since the last backup (" & LastBackupDate & "). Backup will now proceed and the program will restart."​

'update last backup date for use in all save files​

Sheets("calculations").Range("w22").Value = Now()​

'save current file​

Application.DisplayAlerts = False​

ThisWorkbook.Save​

'save backup version of the file​

Dim BackupFolder As String​

Dim BackupFileName As String​

Dim FullFileName As String​

​

Dim Original_File As String​

Original_File = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name​

BackupFolder = "\\ServerName\BackupLocation"​

BackupFileName = "Backup - " & Day(Now()) & "." & Month(Now()) & "." & Year(Now()) & " - " & Hour(Now()) & "h" & Minute(Now())​

​

​

FullFileName = BackupFolder & "\" & BackupFileName & ".xlsm"​

ActiveWorkbook.SaveAs FileName:=FullFileName, FileFormat:= _​

xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _​

, CreateBackup:=False 'after this save we are working from the backed up file so:​

​

'Continue working from original version​

Workbooks.Open Original_File​

​

ThisWorkbook.Close​

​

Application.DisplayAlerts = True​

​

End If​

​

​

End Function


The code works great, the only problem is that the "Private Sub Workbook_Open()" of the new file calls a userform and interrupts the closing of the previous file. Is there any way that I can successfully close the previous file and keep this "Private Sub Workbook_Open()" as is?

And feel free to use this code, it is a generic function that works great for automatically backing up excel files.

 

[Binyamin]

ahh, i'm so stupid.......

I just need to reverse the save order.....

Here is the solution:

Function CheckAndBackup()'Check last backup date

Dim LastBackupDate As Date
Dim DateDifferenceCheck As Integer


LastBackupDate = Sheets("calculations").Range("w22").Value
DateDifferenceCheck = Now() - LastBackupDate

If DateDifferenceCheck > 10 Then 'backup is required


​

MsgBox DateDifferenceCheck & " Days have passed since the last backup (" & LastBackupDate & "). Backup will now proceed and the program will restart."
'update last backup date for use in all save files
Sheets("calculations").Range("w22").Value = Now()

'save backup version of the file
Dim BackupFolder As String
Dim BackupFileName As String
Dim FullFileName As String

Dim Original_File As String

Original_File = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
BackupFolder = "\\ServerName\BackupLocation"
BackupFileName = "Backup - " & Day(Now()) & "." & Month(Now()) & "." & Year(Now()) & " - " & Hour(Now()) & "h" & Minute(Now())

Application.DisplayAlerts = False

FullFileName = BackupFolder & "\" & BackupFileName & ".xlsm"
ActiveWorkbook.SaveAs FileName:=FullFileName, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False 'after this save we are working from the backed up file so:


'Continue working from original version

FullFileName = BackupFolder & "\" & BackupFileName & ".xlsm"
ActiveWorkbook.SaveAs FileName:=FullFileName, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.DisplayAlerts = True​

End If​

End Function