Determining if Other Workbooks Are Open
 

Excel 2003. I have placed a button on my workbook's "switchboard" and want
to do the following when the button is depressed:

1. If there are other workbooks opened, then just close this workbook
(Activeworkbook.Close)
2. If there are no other workbooks opened, then quit the Excel application
(Application.Quit)

How may I test to see if there are any other workbooks opened before I take
one of the above two actions? Thanks for the help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Determining if Other Workbooks Are Open
 

Hi Doug

If Application.Workbooks.Count = 1 Then
' only me
Else
' others
End If

Remember to deal with potential "save changes ?" requests.

HTH. Best wishes Harald

"Chaplain Doug" skrev i melding
...
Excel 2003. I have placed a button on my workbook's "switchboard" and
want
to do the following when the button is depressed:

1. If there are other workbooks opened, then just close this workbook
(Activeworkbook.Close)
2. If there are no other workbooks opened, then quit the Excel
application
(Application.Quit)

How may I test to see if there are any other workbooks opened before I
take
one of the above two actions? Thanks for the help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Determining if Other Workbooks Are Open
 

Since a user might have personal.xls or other hidden workbooks, you can't
just check workbooks.count.

Perhaps something like:

Dim cnt as Long
Dim bk as Workbook
cnt = 0
for each bk in Workbooks
if bk.Name < thisworkbook.Name then
if bk.Windows(1).Visible then
cnt = cnt + 1
end if
end if
Next
if cnt = 0 then
application.close
' code stops at this point
else
thisworkbook.Close
end if

--
Regards,
Tom Ogilvy


"Chaplain Doug" wrote in message
...
Excel 2003. I have placed a button on my workbook's "switchboard" and
want
to do the following when the button is depressed:

1. If there are other workbooks opened, then just close this workbook
(Activeworkbook.Close)
2. If there are no other workbooks opened, then quit the Excel
application
(Application.Quit)

How may I test to see if there are any other workbooks opened before I
take
one of the above two actions? Thanks for the help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Determining if Other Workbooks Are Open
 

"Tom Ogilvy" skrev i melding
...
Since a user might have personal.xls or other hidden workbooks, you can't
just check workbooks.count.

Good point. I never use that one myself, so it's not on my radar.

Otoh, I often open xla files, edit them and forget wether I saved them or
not.

Best wishes Harald

Determining if Other Workbooks Are Open
 

Thanks Tom (and Harald):

This led to another question for which I submitted a post.

Excel 2003. I was checking how many workbooks Excel thought were open, when
I only had one workbook open. Workbooks.Count returned 3. I checked what
they we

Workbooks(1).Name = PERSONAL.XLS
Workbooks(2).Name = PERSONAL (version 1).xls
Workbooks(3).Name = The workbook I have open

Why do I have the PERSONAL.XLS opened, when I did not explicitly open it?
Why are there two versions of PERSONAL.XLS open? How may I get rid of any or
all of these "PERSONAL" workbooks? If I need to have one of them, then how
do I get rid of the other (like the (version 1))? Thanks for the help.


"Tom Ogilvy" wrote:

Since a user might have personal.xls or other hidden workbooks, you can't
just check workbooks.count.

Perhaps something like:

Dim cnt as Long
Dim bk as Workbook
cnt = 0
for each bk in Workbooks
if bk.Name < thisworkbook.Name then
if bk.Windows(1).Visible then
cnt = cnt + 1
end if
end if
Next
if cnt = 0 then
application.close
' code stops at this point
else
thisworkbook.Close
end if

--
Regards,
Tom Ogilvy


"Chaplain Doug" wrote in message
...
Excel 2003. I have placed a button on my workbook's "switchboard" and
want
to do the following when the button is depressed:

1. If there are other workbooks opened, then just close this workbook
(Activeworkbook.Close)
2. If there are no other workbooks opened, then quit the Excel
application
(Application.Quit)

How may I test to see if there are any other workbooks opened before I
take
one of the above two actions? Thanks for the help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Determining if Other Workbooks Are Open
 

Looks like Bob Phillips answered this one at your other post.

--
Regards,
Tom Ogilvy

"Chaplain Doug" wrote in message
...
Thanks Tom (and Harald):

This led to another question for which I submitted a post.

Excel 2003. I was checking how many workbooks Excel thought were open,
when
I only had one workbook open. Workbooks.Count returned 3. I checked what
they we

Workbooks(1).Name = PERSONAL.XLS
Workbooks(2).Name = PERSONAL (version 1).xls
Workbooks(3).Name = The workbook I have open

Why do I have the PERSONAL.XLS opened, when I did not explicitly open it?
Why are there two versions of PERSONAL.XLS open? How may I get rid of any
or
all of these "PERSONAL" workbooks? If I need to have one of them, then
how
do I get rid of the other (like the (version 1))? Thanks for the help.


"Tom Ogilvy" wrote:

Since a user might have personal.xls or other hidden workbooks, you
can't
just check workbooks.count.

Perhaps something like:

Dim cnt as Long
Dim bk as Workbook
cnt = 0
for each bk in Workbooks
if bk.Name < thisworkbook.Name then
if bk.Windows(1).Visible then
cnt = cnt + 1
end if
end if
Next
if cnt = 0 then
application.close
' code stops at this point
else
thisworkbook.Close
end if

--
Regards,
Tom Ogilvy


"Chaplain Doug" wrote in
message
...
Excel 2003. I have placed a button on my workbook's "switchboard" and
want
to do the following when the button is depressed:

1. If there are other workbooks opened, then just close this workbook
(Activeworkbook.Close)
2. If there are no other workbooks opened, then quit the Excel
application
(Application.Quit)

How may I test to see if there are any other workbooks opened before I
take
one of the above two actions? Thanks for the help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Determining if Other Workbooks Are Open
 

Harald,

Your post was not visible to me when I posted, so while it appears I was
contradicting you in a rather rude fashion, that is certainly not what I
was doing. I was just explaining to the OP why I had such a verbose
response.

Best Regards,
Tom Ogilvy

"Harald Staff" wrote in message
...
"Tom Ogilvy" skrev i melding
...
Since a user might have personal.xls or other hidden workbooks, you
can't
just check workbooks.count.

Good point. I never use that one myself, so it's not on my radar.

Otoh, I often open xla files, edit them and forget wether I saved them or
not.

Best wishes Harald