Application.visible........
 

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

Application.visible........
 

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

Application.visible........
 

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

Application.visible........
 

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

Application.visible........
 

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

Application.visible........
 

Could you not use Application events to trap workbook close and not allow
the close of your workbook, and thereby the app.

--

HTH

Bob Phillips

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

"Duncan" wrote in message
oups.com...
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

Application.visible........
 

Well what i wanted to do before was put some sort of variable within
the file and set it to 1 when it opened, and on workbook close if its 1
then cancel=true, and on the actual close button change the variable to
0 so it is allowed to close that way.

The problem I had just before I last posted was that the cancel=true
done absolutely nothing!, and I didnt know why, the book just seems to
ignore my code and I really dont have a clue why.

(Im not really that good with VBA, only learned it this year with the
help of this group)

Duncan


Bob Phillips wrote:

Could you not use Application events to trap workbook close and not allow
the close of your workbook, and thereby the app.

--

HTH

Bob Phillips

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

"Duncan" wrote in message
oups.com...
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

Application.visible........
 

What I gave previously was code that worked in the one workbook and would
either hide or not hide Excel depending upon how it was opened. What I am
now suggesting is this scenario

- when workbook (any) workbook is opened, check it
- if it is you form workbook, check any other open
- if other workbooks open, make that workbook non-visible
- if no other workbooks open, make Excel non-visible
- when workbook close (either through closing the wb directly, or Excel
shutdown), check it

The thing I am not sure about is what those shutdown rules might be, do you
want to abandon the shutdown if THE workbook is open, if THE workbook is
open and some others?

--

HTH

Bob Phillips

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

"Duncan" wrote in message
oups.com...
Well what i wanted to do before was put some sort of variable within
the file and set it to 1 when it opened, and on workbook close if its 1
then cancel=true, and on the actual close button change the variable to
0 so it is allowed to close that way.

The problem I had just before I last posted was that the cancel=true
done absolutely nothing!, and I didnt know why, the book just seems to
ignore my code and I really dont have a clue why.

(Im not really that good with VBA, only learned it this year with the
help of this group)

Duncan


Bob Phillips wrote:

Could you not use Application events to trap workbook close and not
allow
the close of your workbook, and thereby the app.

--

HTH

Bob Phillips

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

"Duncan" wrote in message
oups.com...
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

Application.visible........
 

Bob:

I only want to abondon shutdown of that particular workbook so that it
doesnt close when others are closed, so in the workbook_close i would
cancel it if it hasnt been closed by my close button. Basically I want
this book to stay open regardless of excel intervention (like other
workbooks being open in the beginning and then when the user closes
them it closes mine as well)

If mine is the only one open for a start then no problem, because excel
cannot be closed while hidden and any other workbooks after mine will
open in their own instance because I check the "ignore remote requests"
while mine is open.

So really I want to use your code supplied above which I have tested
and would work, but to stop mine from closing along with other
workbooks for the instances where the user already had a workbook open
when opening mine.

And possibly at the same time if the others are shut then hide the app
and ignore remote so that mine is locked off at that point making any
others opened after that use their own instance.

Does any of this make sense? I know what im trying to get across but
its hard to put into words.

Duncan



Bob Phillips wrote:

What I gave previously was code that worked in the one workbook and would
either hide or not hide Excel depending upon how it was opened. What I am
now suggesting is this scenario

- when workbook (any) workbook is opened, check it
- if it is you form workbook, check any other open
- if other workbooks open, make that workbook non-visible
- if no other workbooks open, make Excel non-visible
- when workbook close (either through closing the wb directly, or Excel
shutdown), check it

The thing I am not sure about is what those shutdown rules might be, do you
want to abandon the shutdown if THE workbook is open, if THE workbook is
open and some others?

--

HTH

Bob Phillips

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

"Duncan" wrote in message
oups.com...
Well what i wanted to do before was put some sort of variable within
the file and set it to 1 when it opened, and on workbook close if its 1
then cancel=true, and on the actual close button change the variable to
0 so it is allowed to close that way.

The problem I had just before I last posted was that the cancel=true
done absolutely nothing!, and I didnt know why, the book just seems to
ignore my code and I really dont have a clue why.

(Im not really that good with VBA, only learned it this year with the
help of this group)

Duncan


Bob Phillips wrote:

Could you not use Application events to trap workbook close and not
allow
the close of your workbook, and thereby the app.

--

HTH

Bob Phillips

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

"Duncan" wrote in message
oups.com...
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