Quit application only if this is the only work book

[tony.reynolds]

I have a workbook called orders.xlsm. it operates entirely from userform so therfore i have the workbook hidden and the user can only see the userform.

on the close of the userform the code is as bleow.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
    If Not NewOrderSupplier = Empty Then
    Dim Msg, Style, Title, Response
    Msg = "It looks like you have started an order." & Chr(10) & "Do you want to cancel this order?"
    Style = vbYesNo + vbQuestion + vbDefaultButton2
    Title = "OrderStocker"
    Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then
        GoTo CloseWB
        Else
        Cancel = True
        Exit Sub
        End If
    End If

CloseWB:
Unload Me
ActiveWorkbook.Save
Application.Visible = True

Application.Quit
End If

End Sub

The only trouble i have is if i have another workbook open when I open Orders.xlsm it is still open in the same instance. so when this code is called the excell try to quit and if the other workbook is not saved it askes if you want to save before closing.

I would like to check

if
Orders .xlsm is the only workbook open then 

Workbooks("Orders.xlsm").Save
Application.Visible = True
Application.Quit

else

Workbooks("Orders.xlsm").Save
Application.Visible = True
Workbooks("Orders.xlsm").Close

Please anyone let me know if this can be done.

 

[wigi]

Will it really be the only workbook open? What about Personal.xls or its variants in Excel 2007/2010?

 

[shg]

Maybe ...

    ActiveWorkbook.Save

    If Workbooks.Count = 1 Or _
       (Workbooks.Count = 2 And Workbooks(1).Name Like "Personal.*") Then
        Application.Quit
    Else
        Application.Visible = True
        ActiveWorkbook.Close
    End If

 

[tony.reynolds]

Perfect exactly what im looking for....

Tell me.. if i have a workbook open callled "abc.xlsm" then open the "Orders.Xlsm" workbook which has

Private Sub Workbook_Open()

Application.Visible = False
MainPanel.Show

End Sub

The abc.xlsm is no longer visible to the user because application.visble=false

Can i make the "Orders.xlsm" open seperate to the abc.xls workbook?

I have changed the registry to make .xls files open in seperate instances but i couldnt make xlsm file do that.

 

[shg]

You could see if the workbook is open in its own instance of Excel after opening, and if not, tell the user to do so and then close the workbook.

 

[GTO]

After checking if the wokbook is in its own instance and finding in the negative, you could have the wb create an instance, replicate itself (saveas), open the original in the new instance, and kill the replicant.

 

[tony.reynolds]

Show me!

 

[tony.reynolds]

After checking if the wokbook is in its own instance and finding in the negative, you could have the wb create an instance, replicate itself (saveas), open the original in the new instance, and kill the replicant.

Thanks for all your input... show me example!

 

[GTO]

How is the wb originally opened by the user? Via code, or any method non via code (ie - File|Open, Windows Explorer, etc.)

 

[tony.reynolds]

just with explorer. (shortcut in quick links on taskbar)