I am working on my first application with Excel VBA. We went live yesterday with the application. The application has 16 user forms. These forms allow the user to modify "databases" on Excel worksheets within the workbook. Foreseeing ahead I will have to release a new version at some point. When I do, this will have to copy these "datatbases" on worksheets. So I added a button to the maintenance user form to copy the databases from the old version workbook to the new application workbook. This code seems to work. But after I close the old version application workbook and activate the new version workbook the new version shows the maintenance user form. This form has a button to close the maintenance user form by hiding it and the shows the user form which is a set of buttons which allows the user to select the next user form to open. When the user clicks on the close maintenance user form button. The user form is hidden and then excel displays a white screen like it was opening a file and excel then says "Excel not responding" after a little while the application closes with the excel file being recovered. This only happens when I when the user closes the maintenance user form after copying the databses. Not sure why it is doing this.
Here is the code to copy one database:
Private Sub cmbCopydb_Click()
Dim Filename As String
Dim msgValue As VbMsgBoxResult
Dim iRow As Integer
Dim OldApp As Workbook
Dim NewApp As Workbook
' user wants to copy databases from old application file
' Create filename
Filename = "C:\Users\" & Application.UserName & "\Desktop\Inventory App Demo3.xlsm"
' Check if files exists
If Dir(Filename) = "" Then
' File does not exists
MsgBox ("Old application file ( " & Filename & " ) does not exist, locate file and try again")
Exit Sub
End If
' Confirm user wants to copy databases
msgValue = MsgBox("Do you want to copy the databases from " & Filename, vbYesNo + vbInformation, "Confirmation")
If msgValue = vbNo Then
Exit Sub
End If
' Open File
Set NewApp = ActiveWorkbook
Me.Hide
Set OldApp = Workbooks.Open(Filename)
' Turn off display alerts
Application.DisplayAlerts = False
' Copy sleeve inventory by deleting current sheet in new app and then copying from old app
NewApp.Worksheets("DB1").Delete
OldApp.Worksheets("DB1").Copy Before:=NewApp.Worksheets("DB2")
' Turn alerts back on
Application.DisplayAlerts = True
' Close old application
OldApp.Close SaveChanges:=False
Me.Show
NewApp.Activate
End Sub
Here is the code that executes when the user clicks on the close button. Not much going on here.
Private Sub cmdClose_Click()
Me.Hide
SwitchBoard.Show
End Sub
With some debug statements I have determined that after the user clicks on the close button the application goes to the main screen but it acts like the user selected the maintenance screen again and selected to add the databases again. I put a public boolean variable in to detect this but the app still crashes. Any help would be greatly appreciated.