By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,781 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,781 IT Pros & Developers. It's quick & easy.

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

P: 1
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_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:


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 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
Dec 26 '07 #1
Share this Question
Share on Google+
3 Replies


daniel aristidou
100+
P: 491
hi all,


Expand|Select|Wrap|Line Numbers
  1. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  2. If ThisWorkbook.Saved = False Then
  3.    If ThisWorkbook.FileFormat = xlTemplate Then
  4.       UserForm1.Show
  5.    End If
  6. End If
  7. End Sub
' The submit code:


[
Expand|Select|Wrap|Line Numbers
  1. I]Private Sub cmdSubmit_Click()
  2. If UserForm1.NoSavePrint = True Then
  3.    UserForm1.Hide
  4.    ThisWorkbook.Close False
  5. End If
  6. If UserForm1.NoSavePrint = False Then
  7.    UserForm1.Hide
  8.    Application.DisplayAlerts = False
  9.    Application.ScreenUpdating = False
  10.    ThisWorkbook.Save
  11.    ActiveWorkbook.SaveAs Filename:="\\sbs2003\OrdersTnua\JetBackup\OrderNo_" & Range("E8"), FileFormat:=xlNormal, Password:="jet", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
  12.     Application.DisplayAlerts = True
  13.     Application.ScreenUpdating = True
  14.     Call ThisWorkbook.Print2
  15.     ThisWorkbook.Close
  16. End If
  17. 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(~_~)
Jan 3 '08 #2

daniel aristidou
100+
P: 491
instead of this code
Expand|Select|Wrap|Line Numbers
  1. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  2. If ThisWorkbook.Saved = False Then
  3.    If ThisWorkbook.FileFormat = xlTemplate Then
  4.       UserForm1.Show
  5.    End If
  6. End If
  7. End Sub

Why not use
Expand|Select|Wrap|Line Numbers
  1. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  2. If ThisWorkbook.Saved = False And ThisWorkbook.FileFormat = xlTemplate Then
  3.          UserForm1.Show
  4.  
  5. End If
  6. End Sub
Jan 3 '08 #3

Expert 5K+
P: 8,434
Subscribing (please ignore this post).
Jan 4 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.