PDA

View Full Version : Closing a program without killing other workbooks / "Wait please" pop up window



elvagonumero1
04-09-2015, 07:18 PM
Hi, I'm trying to make something that I know that someone ask it before but I didn't really found the answer.

First, I'm using userforms and I'm finishing the code with this:



Private Sub CommandButton1_Click()
Application.Quit
End Sub


But this automaticly shuts down excel and all my open workbooks, instead I would like that it close only the workbook where the code is written. I've tried this next line but didn't work



Private Sub CommandButton1_Click()
Dim bookname As String
Dim i As Integer

For i = 1 To Application.Workbooks.Count
bookname = Application.Workbooks(i).Name
If bookname = "MyWorkbook.xls" Then
'Application.Workbooks(i).Close
End If
Next i
End Sub

Second, I would like that my userforms doesn't display the windows' closing "X" at the right corner, and then only can be close by a comandbutton inside the window.

Third, when the code is running it process a decent amount of data and takes about 3/4 of a minute, but it looks like it is doing nothing, so I would to have a pop up window in front of everything that tells the user to wait while it runs. Could this be done?

All the help would be appreciate, and forgive me if can't make myself understood

By now that's all, thanks in advance
Country Venezuela, Language Spanish, Excel 2003

mancubus
04-10-2015, 12:26 AM
first question



Private Sub CommandButton1_Click()
ThisWorkbook.Close False
End Sub


change False to True if you want to save the workbook.

mancubus
04-10-2015, 12:30 AM
second question:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=164

in userform's codemodule:


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = 0 Then
Cancel = True
MsgBox "The X is disabled, please use a button on the form.", vbCritical
End If

End Sub

mancubus
04-10-2015, 12:52 AM
third question.

create a userform. name it as WaitForm, or whatever you like.
insert a label. resize userform and label.
change label's "caption" to desired text like "macro is executing, pls wait".
or you can insert an image which reads this.


modify your code to load and unload this form.

like:


Private Sub CommandButton1_Click()

Load WaitForm

'Your entire code here..
'Your entire code here..
'Your entire code here..
'Your entire code here..
'Your entire code here..

Unload WaitForm

End Sub