Ok Bob,

Your code works exactly as stated, no problems there. The problem lies
with my spreadsheet and how is it set out.

As I dont want users to be able to close the spreadsheet by closing the
app, this solution you have provided will not fit for me, it works
perfectly but if other workbooks are already open then closing them
afterwards by the big red X (close app < close book) it closes my
application that I want to remain open.

I think I will leave this one so that they just have to open it on its
own first, i cannot see a workaround.

Many thanks for your help

Duncan


Bob Phillips wrote:

Let us know how it goes.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Duncan" wrote in message
oups.com...
Bob:

Many thanks, I believe this works to all of my requirements but I have
yet to test it in all aspects. I am sure it will be perfect though as
it works initially.

I must be honest; I was being completely thick earlier when I first
read your post and I should have really figured it out myself!

Many thanks again

Duncan





Bob Phillips wrote:

This is the sort of thing I meant

Workbook_Open

Private Sub Workbook_Open()
Application.DisplayAlerts = False
Application.IgnoreRemoteRequests = True
Application.DisplayAlerts = True
Sheets("sheet2").Visible = True
Sheets("sheet1").Visible = xlVeryHidden
UserForm1.Show vbModeless
End Sub

Userform

Private mWBCount As Long

Private Sub Userform_Activate()
mWBCount = VisibleBooks
If mWBCount = 1 Then
Application.Visible = False
Application.EnableCancelKey = xlDisabled
Else
Windows(ThisWorkbook.Name).Visible = False
End If
End Sub

Private Sub UserForm_Terminate()
If mWBCount = 1 Then
Application.Visible = True
Application.EnableCancelKey = xlErrorHandler
End If
End Sub

and in a standard module

Function VisibleBooks() As Long
Dim wb As Workbook
Dim cWBs As Long

For Each wb In Application.Workbooks
If Windows(wb.Name).Visible Then
cWBs = cWBs + 1
End If
Next wb

VisibleBooks = cWBs

End Function



--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Duncan" wrote in message
ups.com...

Private Sub Workbook_Open()
Application.OnWindow = ThisWorkbook.Name & "!ThisWbkOnly"
Application.DisplayAlerts = False
Application.IgnoreRemoteRequests = True
Application.DisplayAlerts = True
Application.Visible = False
Application.EnableCancelKey = xlDisabled
Sheets("sheet2").Visible = True
Sheets("sheet1").Visible = xlVeryHidden
Userform1.Show vbmodless
End Sub

Sub ThisWbkOnly()
If Not ActiveWorkbook.Name = ThisWorkbook.Name Then
ActiveWorkbook.Close False
MsgBox "Excel private instance !", 64
End If
End Sub

I think this covers most of it, but there is some code within a class
module called from the form which sets it modeless too, its from
formfun which I have tried to learn and adapt but I am still
struggling
to understand it...

Duncan




Duncan wrote:

Bob,

I will post back tomorrow as I have to shoot off now, that is if you
have time to look back tomorrow?

I will say though there is an awful lot of code to post to show what
it
is doing here and there, really I have utilised a lot from other
coders
and made the form modeless and suchlike which makes the
application.visible = false setting very very needed, I will explain
tomorrow.

Many thanks

Duncan


Bob Phillips wrote:

Show me your code, and I will see what I can do.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Duncan" wrote in message
ups.com...
Bob,

I have tried just placing this in my workbook_open above the
rest of
my
code but it didnt work.

Am I applying it wrong? as to be honest I didnt know how to tie
it
in
with what I have...

(it broke on "If Windows(wb.Name).Visible Then" when I had
another
workbook open first)

Duncan

Bob Phillips wrote:

Count the visible workbooks, if greater than 1 hide the
workbook,
else
hide
the application

Function VisibleBooks() As Long
Dim wb As Workbook
Dim cWBs As Long

For Each wb In Application.Workbooks
If Windows(wb.Name).Visible Then
cWBs = cWBs + 1
End If
Next wb

VisibleBooks = cWBs

End Function


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing
direct)

"Duncan" wrote in message
oups.com...
Hi all,

I am sure this can be done, its just a case of how!

When I open my file it hides the application so I can work
only
with
the form, everything is working fine and I have spent ages
making it,
only to realise that if other worksheets are open before
opening
this
file - it hides them with the app.

now this scenario could be avoided if the users opened this
file
first
before any other excel files because after this one is open
everything
else opens in its own instance.

But what I would love to be able to do is leave their other
instance
open when mine is opened, so that nothing is hidden away
from
them
that
they already have open.

I thought maybe I could see if others are open, then create
a
new
instance, tie their sheets to it and then hide my instance.
I
just
dont
have a clue how to do this (or even if it can be done)

If anyone can think of a solution to this I would be most
gratefull.

Many thanks in advance

Duncan