Userform closes when I closeing any workbook

ITs65458

New Member
Joined
Nov 5, 2020
Messages
4
Office Version
  1. 2016
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.
VBA Code:
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.
 

Attachments

  • userform1.JPG
    userform1.JPG
    116.3 KB · Views: 12

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,797
The best method of assisting you would be to have your workbook posted to a CLOUD / DOWNLOAD website. Post the link here.
 

ITs65458

New Member
Joined
Nov 5, 2020
Messages
4
Office Version
  1. 2016
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
VBA Code:
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.

VBA Code:
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.

VBA Code:
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.

VBA 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

New Member
Joined
Nov 5, 2020
Messages
4
Office Version
  1. 2016
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,852
Messages
5,574,663
Members
412,608
Latest member
Guromir
Top