VBA Userform to show userform without switching to excel while saving

Question

I have vba code which copy the contain from one excel to another excel. The problem is that while saving the file, vba userform switch to excel with dialog box as 'saving'. I have used below code and userform showmodel = False but it is not working. Please let me know if we have anything else to solve it.

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim myFileNameDir As String Dim myFileNameDir2 As String Dim ws As Worksheet Dim ws2 As Worksheet Dim emailID As String Dim supername As String

myFileNameDir2 = TextBox2.Value

Application.ScreenUpdating = False Application.DisplayAlerts = False Workbooks.Open FileName:=myFileNameDir2, UpdateLinks:=0 Application.ScreenUpdating = False Application.DisplayAlerts = False

Set ws2 = Worksheets(1)

myFileNameDir = TextBox1.Value

Application.ScreenUpdating = False Application.DisplayAlerts = False Workbooks.Open FileName:=myFileNameDir, ReadOnly:=True, UpdateLinks:=0 Application.ScreenUpdating = False Application.DisplayAlerts = False

Set ws = Worksheets(1)

Dim cell As Range Dim II As Integer Dim III As Integer Dim Foundcell As Range

II = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

For III = 2 To II emailID = ws2.Cells(III, "D").Value

ws2.Cells(III, "P").Value = ws2.Cells(III, "A").Value & "-" & ws2.Cells(III, "C").Value

Set Foundcell = ws.Range("AA2:AA1048576").find(What:=emailID) Do Until Foundcell Is Nothing  ws2.Cells(III, "H").Value = Foundcell.Offset(, -6)  ws2.Cells(III, "G").Value = Foundcell.Offset(, -17)

 Exit Do Loop

If IsEmpty(ws2.Cells(III, "H").Value) Then

Else supername = ws2.Cells(III, "H").Value Set Foundcell = ws.Range("D2:D1048576").find(What:=supername) Do Until Foundcell Is Nothing

 ws2.Cells(III, "I").Value = Foundcell.Offset(, 23)  ws2.Cells(III, "J").Value = Foundcell.Offset(, 17)

 Exit Do Loop End If

If IsEmpty(ws2.Cells(III, "J").Value) Then Else supername = ws2.Cells(III, "J").Value Set Foundcell = ws.Range("D2:D1048576").find(What:=supername) Do Until Foundcell Is Nothing

 ws2.Cells(III, "K").Value = Foundcell.Offset(, 23)  ws2.Cells(III, "L").Value = Foundcell.Offset(, 17)

 Exit Do Loop End If

If IsEmpty(ws2.Cells(III, "L").Value) Then

Else supername = ws2.Cells(III, "L").Value Set Foundcell = ws.Range("D2:D1048576").find(What:=supername) Do Until Foundcell Is Nothing

 ws2.Cells(III, "M").Value = Foundcell.Offset(, 23)  ws2.Cells(III, "N").Value = Foundcell.Offset(, 17)

 Exit Do Loop End If

If IsEmpty(ws2.Cells(III, "N").Value) Then

Else supername = ws2.Cells(III, "N").Value Set Foundcell = ws.Range("D2:D1048576").find(What:=supername) Do Until Foundcell Is Nothing

 ws2.Cells(III, "O").Value = Foundcell.Offset(, 23)

 Exit Do Loop End If

Next III

Application.ScreenUpdating = False Application.DisplayAlerts = False

ws.Activate 'ActiveWorkbook.Save ActiveWorkbook.Close

Application.ScreenUpdating = False Application.DisplayAlerts = False

ws2.Activate ActiveWorkbook.Save ActiveWorkbook.Close

Application.ScreenUpdating = False Application.DisplayAlerts = False

Can you provide (a) some screen shots with this userform which causing problems and (b) more code, especially this saving procedure. — Limak
I see you have hard time with editing your post. To make a block of code, you have to paste all the code, than highlight it and click on this "Code Sample" button. Also, you don't need to save your post before editing, you can simply look at the bottom of page, there is a preview of your post there. — Limak

user3598756 user3598756 · Accepted Answer · 2016-10-31T13:25:54

there's quite a mess in there...

as for the moment being you can try to substitute:

ws2.Activate ActiveWorkbook.Save ActiveWorkbook.Close

with:

ws2.Close True

and the same for ws, should you need it (but I see commented statements)

VBA Userform to show userform without switching to excel while saving

1 Answers