Excel VBA Problem : UserForm1.show issue (shows up twice)

[bennyad]

hi all,

i've created an Excel VBA script that shows UserForm1 when clicking on close (the "X" at the top right) without saving. the form simply prompts the user whether to save the file or not (with few more options).
the problem that occurs is: after clicking the submit button, the appears again and only after the second time i pressed the submit button it disappears.
in short: the form appears twice instead of just once (like i want).
my code:

Private Sub Workbook_Before Close(Cancel As Boolean)
If ThisWorkbook.Sa ved = False Then
If ThisWorkbook.Fi leFormat = xlTemplate Then
UserForm1.Show
End If
End If
End Sub


' The submit code:


Private Sub cmdSubmit_Click ()
If UserForm1.NoSav ePrint = True Then
UserForm1.Hide
ThisWorkbook.Cl ose False
End If
If UserForm1.NoSav ePrint = False Then
UserForm1.Hide
Application.Dis playAlerts = False
Application.Scr eenUpdating = False
ThisWorkbook.Sa ve
ActiveWorkbook. SaveAs Filename:="\\sb s2003\OrdersTnu a\JetBackup\Ord erNo_" & Range("E8"), FileFormat:=xlN ormal, Password:="jet" , WriteResPasswor d:="", ReadOnlyRecomme nded:=False, CreateBackup:=F alse
Application.Dis playAlerts = True
Application.Scr eenUpdating = True
Call ThisWorkbook.Pr int2
ThisWorkbook.Cl ose
End If
End Sub


i tried and tried and could not find out where's the error. everything is great except the fact that the form repeats twice.

will be grateful for any ideas.
thanks

[daniel aristidou]

hi all,

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = False Then
   If ThisWorkbook.FileFormat = xlTemplate Then
      UserForm1.Show
   End If
End If
End Sub

' The submit code:


[

I]Private Sub cmdSubmit_Click()
If UserForm1.NoSavePrint = True Then
   UserForm1.Hide
   ThisWorkbook.Close False
End If
If UserForm1.NoSavePrint = False Then
   UserForm1.Hide
   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   ThisWorkbook.Save
   ActiveWorkbook.SaveAs Filename:="\\sbs2003\OrdersTnua\JetBackup\OrderNo_" & Range("E8"), FileFormat:=xlNormal, Password:="jet", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Call ThisWorkbook.Print2
    ThisWorkbook.Close
End If
End Sub[/i]

i

Can you please submit comments into your code explaining what each line does. This will make it allot easier to solve your problem.
Reading comments help anormously when dealing with this type of problem

Daniel(~_~)

[daniel aristidou]

instead of this code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = False Then
   If ThisWorkbook.FileFormat = xlTemplate Then
      UserForm1.Show
   End If
End If
End Sub

Why not use

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ThisWorkbook.Saved = False And ThisWorkbook.FileFormat = xlTemplate Then
         UserForm1.Show
 
End If
End Sub

[Killer42]

Subscribing (please ignore this post).