open excel in new instance from vba

[olikershaw]

Hi All

New to posting on this forum but have used it a lot in the past for assistance.

I have a excel database with a userform which works perfectly but when I close the userform it closes all instances of excel that are open. If excel is not open it works perfectly

Here is the code for workbook open and then workbook close

Private Sub Workbook_Open()

Application.Visible = False
If ThisWorkbook.ReadOnly Then
MsgBox "This program is already in use, please try again in a few minutes.", vbInformation
ThisWorkbook.Close savechanges:=False

Else

Application.Visible = False
Fault_Reporting.Show
End If
End Sub



Private Sub CommandButton12_Click()
answer = MsgBox("Are you sure you want to exit?", vbYesNo + vbQuestion + vbDefaultButton2, "Warning")
If answer = vbNo Then Exit Sub
Unload Me
ThisWorkbook.Save
Application.Quit
End Sub

Can any one help

 

[RoryA]

If you don't need to worry about a personal macro workbook, then you could test if workbooks.count > 1 before quitting the application.

 

[Artik]

Instead of line

Application.Quit

insert

  If Application.Workbooks.Count > 1 Then
    ThisWorkbook.Close
  Else
    Application.Quit
  End If

Artik

(I'm late )

 

[olikershaw]

If you don't need to worry about a personal macro workbook, then you could test if workbooks.count > 1 before quitting the application.

How would this work? Excuse my ignorance I am quite new to VBA.
Cheers

 

[olikershaw]

Instead of line

Application.Quit

insert

  If Application.Workbooks.Count > 1 Then
    ThisWorkbook.Close
  Else
    Application.Quit
  End If

Artik

(I'm late )

Hi Tried this. It still Closes the other workbook but then when I reopen the original excel with the userform it comes back.

Cheers

 

[Artik]

I didn't notice that you were hiding the Excel application at the beginning.
Fix the code to:

  If Application.Workbooks.Count > 1 Then
    Application.Visible = True
    ThisWorkbook.Close
  Else
    Application.Quit
  End If

Artik

 

[olikershaw]

I didn't notice that you were hiding the Excel application at the beginning.
Fix the code to:

  If Application.Workbooks.Count > 1 Then
    Application.Visible = True
    ThisWorkbook.Close
  Else
    Application.Quit
  End If

Artik

Perfect.

Thankyou. Works perfect

 

[olikershaw]

I didn't notice that you were hiding the Excel application at the beginning.
Fix the code to:

  If Application.Workbooks.Count > 1 Then
    Application.Visible = True
    ThisWorkbook.Close
  Else
    Application.Quit
  End If

Artik

Hi Artik

With this code whilst only being really picky.

is there a way to not make excel maximise the window if theres a workbook already open.

If i am to open my userform then there want excel to be minimized and stay minimized even when i close the userform.

Thanks for your help

 

[Artik]

So far I have no idea how to do it. My attempts so far have ended with an "Out of memory" error or displaying a window with another workbook open.

Artik

 

[olikershaw]

So far I have no idea how to do it. My attempts so far have ended with an "Out of memory" error or displaying a window with another workbook open.

Artik

Ok Thanks

its only me being picky its not a big issue. Also I use application.ontime to close if someone has kept it open open accident but if you have another excel open and then close the userform it closes other excels after the ontime

Cheers