Userform closes when I closeing any workbook

[ITs65458]

Hello all,
I have a userform that opens on workbook oepn. the userform has multiple tabs. each tab is a different from that workers fill out during their shift. at the end of the day, the employee can save each tab via a command save button that grabs all the info from that tab, opens a specific workbook, and transfers all the data into that workbook and saves as a new excel file. Then, the original workbook needs to be closed without saving any changes. Keep in mind this is not the workbook that contains the userform, this is a workbook that the command save button opens. I use Workbooks("Blank_BSD_BETA1.1.1.xlsm").Close SaveChanges:=False as i dont want changes to be saved to it. the code to here executes perfect, but when the workbook is closed, it makes my userfrom disappear. I can bring the userform back up from a button on the workbook, but all the data on the tabs is lost. If anyone can help would be greatly appreciated. i need the user form to remain open while the VBA opens, transfers and closes other workbooks. Attached is a image from the userform

This is the code that opens a different workbook where the files are being transferred.

Private Sub CommandButton1BSD_Click()
 Workbooks.Open Filename:="G:\03 PROJECTS\Reports\Scanned Forms for Ivan\Digital Forms\Blank_BSD_BETA1.1.1.xlsm"

then in this workbook, at the end of the code, i have Workbooks("Blank_BSD_BETA1.1.1.xlsm").Close SaveChanges:=False
this causes to loose my userform and all the data entered on the other tabs before they have been saved.

 

[Logit]

The best method of assisting you would be to have your workbook posted to a CLOUD / DOWNLOAD website. Post the link here.

 

[ITs65458]

The best method of assisting you would be to have your workbook posted to a CLOUD / DOWNLOAD website. Post the link here.

We have restrictions to could based sharing. I can post the code here. There is code in multiple workbooks, so i will try to explain it as i go.

Starting with the main workbook where the userform lives and this workbook does not get closed till the end of the shift.
userform opens on start

Private Sub workbook_open()
Call UserForm1.Show(vbModeless)
End Sub

This code is in my first tab in command button. This opens the target workbook, transfers data from userform text and comboboxes into the target workbook, and clears the values in the userform text comboboxes. Then it calls a module on the target workbook.

Private Sub CommandButton1BSD_Click()
 Workbooks.Open Filename:="G:\03 PROJECTS\Reports\Scanned Forms for Ivan\Digital Forms\Blank_BSD_BETA1.1.1.xlsm"
    Sheets("SWA_BSD").Range("B2") = TextBox1BSD.Text
    Sheets("SWA_BSD").Range("B3") = TextBox2BSD.Text
    Sheets("SWA_BSD").Range("B4") = ComboBox4BSD.Value
    Sheets("SWA_BSD").Range("B5") = TextBox4BSD.Text
    Sheets("SWA_BSD").Range("B6") = TextBox5BSD.Text
    Sheets("SWA_BSD").Range("C9") = TextBox6BSD.Text
    Sheets("SWA_BSD").Range("C12") = TextBox7BSD.Text
    TextBox1BSD.Value = Null
    TextBox2BSD.Value = Null
    ComboBox4BSD.Value = Null
    TextBox4BSD.Value = Null
    TextBox5BSD.Value = Null
    TextBox6BSD.Value = Null
    TextBox7BSD.Value = Null
    Command_Call_M1BSD
 End Sub

Next code 'Command_Call_M1BSD' is on the userform workbook that remains open entire shift. This code calls a macro on the target workbook.

Sub Call_BSD()
 Application.Run "Blank_BSD_BETA1.1.1.xlsm!MakeFolderBSD"
End Sub

The code 'MakeFolderBSD' is on the target workbook. This code looks if the year, calendar week folders exist, and if not creates them and then places a copy of the target workbook and renames the copy with veh name, date, shift, ect.. At the end of this code is where the target workbook gets closed with no changes. this is what is causing my userform to disappear. here is the full code.

Sub MakeFolderBSD()
  Dim strVehNum As String, strShift As String, strYear As String, strPath As String, strKW As String, strWK As String, sDate As String, FSO As New FileSystemObject
  Dim fn As String
  
  strYear = Sheets("SWA_BSD").Range("F4") 'assume Year in F4
  strVehNum = Sheets("SWA_BSD").Range("B3") ' assumes vehicle number in T1
  strKWno = Sheets("SWA_BSD").Range("E1")  ' assumes KWno in F1
  strWK = Sheets("SWA_BSD").Range("F1")  ' assumes WK in E1
  strShift = Sheets("SWA_BSD").Range("E2") ' assumes shift in D1
  sDate = Format(Sheets("SWA_BSD").Range("B2"), "DD.MM.YY")
  strPath = Sheets("Links").Range("B1")
  
    
  If Not FSO.FolderExists(strPath & "\" & strYear) Then
    FSO.CreateFolder strPath & "\" & strYear
    End If
  If Not FSO.FolderExists(strPath & "\" & strYear & "\" & strWK & "_" & strKWno) Then
    
   FSO.CreateFolder strPath & "\" & strYear & "\" & strWK & "_" & strKWno
  End If
   fn = strPath & "\" & strYear & "\" & strWK & "_" & strKWno & "\SWA_Statistik_NAR " & strVehNum & " " & sDate & " " & strShift & ".xlsm"
   If Dir(fn) <> "" Then Clear_CellsBSD
   
    With Workbooks.Add(xlWBATWorksheet)
    ThisWorkbook.Sheets(Array("SWA_BSD", "Fahrzeugliste")).Copy after:=.Sheets(1)
    Application.DisplayAlerts = False
    .Sheets(1).Delete
    Application.DisplayAlerts = True
    On Error Resume Next
    Kill fn
    .SaveAs fn, xlOpenXMLWorkbookMacroEnabled
    .Close False
End With
Clear_CellsBSD
End Sub
Sub Clear_CellsBSD()
    Range("B2:B6").ClearContents
    Range("C9").ClearContents
    Range("C12").ClearContents
    Range("C20").ClearContents
    Range("B23:B24").ClearContents
    Range("C23").ClearContents
    Range("B26").ClearContents
    Workbooks("Blank_BSD_BETA1.1.1.xlsm").Close SaveChanges:=False
End Sub

Let me know if you have any idea. I have also made small changes when it calls the userform back up and then closes the target workbook, but no success there as well. I can call the userform back up and hide the target workbook. This does not erase my data in the userform textboxes, but if the worker needs to create second form of the same, it debugs that the file is already open. I dont want to cause confusion for them.

 

[ITs65458]

The best method of assisting you would be to have your workbook posted to a CLOUD / DOWNLOAD website. Post the link here.

The code is same for all other tabs in the userform. Each tab is a different form. Same code, just calls different workbook that opens.

 

[ITs65458]

The best method of assisting you would be to have your workbook posted to a CLOUD / DOWNLOAD website. Post the link here.

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com