I have four dashbards each with 4 charts containing macros, and each in it's
own workbook. The data for the macros is all in the same workbook. It works
fine.

This morning I had a request to create a table of contents so that the user
could click a button and open a dashboard. I crated this in a separate
workbook. It includes buttons with macros so, if the user clicked on army,
for example, they would go to the dashboard with all of the army information.
I created it in it's own workbook.
Then I decided that if they were going to use a table of contents to go to a
dashboard, I needed a way for them to get back to the TOC. So I created a
button in each dashboard that will take the user back to the TOC. Here's my
problem:
When a user is in the TOC and selects army, he goes to the army dashboard.
Then he selects 'back to TOC" if he wants to return. However, if the user
forgets, and selects army again, one of two things happens; either they get a
message saying the dashboard is already open, and then they get a VB error,
or they are allowed to go back to the dashboard getting no error. But ALWAYS,
when they go back to the dashboard, the chart that has the 'return to TOC"
button loses focus, and they have to click out of the chart and click back
in. By losing focus I mean the chart has three circles on each side instead
of 3 black squares. The user thinks the chart doesn't work.
How do I keep the chart from losing focus. This is my first time creating
macros. Is there someting special I should be doing? Thank you.

By "goes to the army dashboard", do you mean the army workbook is opened, or
is it already open? If your code first opens the workbook, this explains the
error. Make the code a little smarter, so that, if the workbook is already
open, your code only activates it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jackie" wrote in message
...
I have four dashbards each with 4 charts containing macros, and each in
it's
own workbook. The data for the macros is all in the same workbook. It
works
fine.

This morning I had a request to create a table of contents so that the
user
could click a button and open a dashboard. I crated this in a separate
workbook. It includes buttons with macros so, if the user clicked on army,
for example, they would go to the dashboard with all of the army
information.
I created it in it's own workbook.
Then I decided that if they were going to use a table of contents to go to
a
dashboard, I needed a way for them to get back to the TOC. So I created a
button in each dashboard that will take the user back to the TOC. Here's
my
problem:
When a user is in the TOC and selects army, he goes to the army dashboard.
Then he selects 'back to TOC" if he wants to return. However, if the user
forgets, and selects army again, one of two things happens; either they
get a
message saying the dashboard is already open, and then they get a VB
error,
or they are allowed to go back to the dashboard getting no error. But
ALWAYS,
when they go back to the dashboard, the chart that has the 'return to TOC"
button loses focus, and they have to click out of the chart and click back
in. By losing focus I mean the chart has three circles on each side
instead
of 3 black squares. The user thinks the chart doesn't work.
How do I keep the chart from losing focus. This is my first time creating
macros. Is there someting special I should be doing? Thank you.

By goes to the army dashboard, I mean that the dashboard is opend by the
macro. Once it's opened if it's already open, I get the error. I would like
to do what you are suggesing but I don't know how. Can you explain to me how
I can make it work so that if the workbook is open, the code only activates
it. Thanks.

Jackie

"Jon Peltier" wrote:

By "goes to the army dashboard", do you mean the army workbook is opened, or
is it already open? If your code first opens the workbook, this explains the
error. Make the code a little smarter, so that, if the workbook is already
open, your code only activates it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jackie" wrote in message
...
I have four dashbards each with 4 charts containing macros, and each in
it's
own workbook. The data for the macros is all in the same workbook. It
works
fine.

This morning I had a request to create a table of contents so that the
user
could click a button and open a dashboard. I crated this in a separate
workbook. It includes buttons with macros so, if the user clicked on army,
for example, they would go to the dashboard with all of the army
information.
I created it in it's own workbook.
Then I decided that if they were going to use a table of contents to go to
a
dashboard, I needed a way for them to get back to the TOC. So I created a
button in each dashboard that will take the user back to the TOC. Here's
my
problem:
When a user is in the TOC and selects army, he goes to the army dashboard.
Then he selects 'back to TOC" if he wants to return. However, if the user
forgets, and selects army again, one of two things happens; either they
get a
message saying the dashboard is already open, and then they get a VB
error,
or they are allowed to go back to the dashboard getting no error. But
ALWAYS,
when they go back to the dashboard, the chart that has the 'return to TOC"
button loses focus, and they have to click out of the chart and click back
in. By losing focus I mean the chart has three circles on each side
instead
of 3 black squares. The user thinks the chart doesn't work.
How do I keep the chart from losing focus. This is my first time creating
macros. Is there someting special I should be doing? Thank you.

Hi,

One simple way would be to add a line of code that closed the army file when
the user clicks the Back to TOC. That code would be

ActiveWorkbook.Close SaveChanges:=False


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"jackie" wrote:

By goes to the army dashboard, I mean that the dashboard is opend by the
macro. Once it's opened if it's already open, I get the error. I would like
to do what you are suggesing but I don't know how. Can you explain to me how
I can make it work so that if the workbook is open, the code only activates
it. Thanks.

Jackie

"Jon Peltier" wrote:

By "goes to the army dashboard", do you mean the army workbook is opened, or
is it already open? If your code first opens the workbook, this explains the
error. Make the code a little smarter, so that, if the workbook is already
open, your code only activates it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jackie" wrote in message
...
I have four dashbards each with 4 charts containing macros, and each in
it's
own workbook. The data for the macros is all in the same workbook. It
works
fine.

This morning I had a request to create a table of contents so that the
user
could click a button and open a dashboard. I crated this in a separate
workbook. It includes buttons with macros so, if the user clicked on army,
for example, they would go to the dashboard with all of the army
information.
I created it in it's own workbook.
Then I decided that if they were going to use a table of contents to go to
a
dashboard, I needed a way for them to get back to the TOC. So I created a
button in each dashboard that will take the user back to the TOC. Here's
my
problem:
When a user is in the TOC and selects army, he goes to the army dashboard.
Then he selects 'back to TOC" if he wants to return. However, if the user
forgets, and selects army again, one of two things happens; either they
get a
message saying the dashboard is already open, and then they get a VB
error,
or they are allowed to go back to the dashboard getting no error. But
ALWAYS,
when they go back to the dashboard, the chart that has the 'return to TOC"
button loses focus, and they have to click out of the chart and click back
in. By losing focus I mean the chart has three circles on each side
instead
of 3 black squares. The user thinks the chart doesn't work.
How do I keep the chart from losing focus. This is my first time creating
macros. Is there someting special I should be doing? Thank you.

Thank you Shane, but it would be more user friendly if the user clicks on the
TOC button again the army file is activated. Sometimes it is activated, and
other times I get a message saying the file is alredy open, and then a VB
error message.


Jackie

"ShaneDevenshire" wrote:

Hi,

One simple way would be to add a line of code that closed the army file when
the user clicks the Back to TOC. That code would be

ActiveWorkbook.Close SaveChanges:=False


If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"jackie" wrote:

By goes to the army dashboard, I mean that the dashboard is opend by the
macro. Once it's opened if it's already open, I get the error. I would like
to do what you are suggesing but I don't know how. Can you explain to me how
I can make it work so that if the workbook is open, the code only activates
it. Thanks.

Jackie

"Jon Peltier" wrote:

By "goes to the army dashboard", do you mean the army workbook is opened, or
is it already open? If your code first opens the workbook, this explains the
error. Make the code a little smarter, so that, if the workbook is already
open, your code only activates it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jackie" wrote in message
...
I have four dashbards each with 4 charts containing macros, and each in
it's
own workbook. The data for the macros is all in the same workbook. It
works
fine.

This morning I had a request to create a table of contents so that the
user
could click a button and open a dashboard. I crated this in a separate
workbook. It includes buttons with macros so, if the user clicked on army,
for example, they would go to the dashboard with all of the army
information.
I created it in it's own workbook.
Then I decided that if they were going to use a table of contents to go to
a
dashboard, I needed a way for them to get back to the TOC. So I created a
button in each dashboard that will take the user back to the TOC. Here's
my
problem:
When a user is in the TOC and selects army, he goes to the army dashboard.
Then he selects 'back to TOC" if he wants to return. However, if the user
forgets, and selects army again, one of two things happens; either they
get a
message saying the dashboard is already open, and then they get a VB
error,
or they are allowed to go back to the dashboard getting no error. But
ALWAYS,
when they go back to the dashboard, the chart that has the 'return to TOC"
button loses focus, and they have to click out of the chart and click back
in. By losing focus I mean the chart has three circles on each side
instead
of 3 black squares. The user thinks the chart doesn't work.
How do I keep the chart from losing focus. This is my first time creating
macros. Is there someting special I should be doing? Thank you.

Use something like this:


' sFileName is the path and file name of the workbook to be activated or
opened

For Each WB In Application.Workbooks
If WB.FullName = sFileName Then
' found the file
WB.Activate
bFound = True
Exit For
End If
Next
If Not bFound Then
Application.Workbooks.Open sFileName
End If


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______




"jackie" wrote in message
...
By goes to the army dashboard, I mean that the dashboard is opend by the
macro. Once it's opened if it's already open, I get the error. I would
like
to do what you are suggesing but I don't know how. Can you explain to me
how
I can make it work so that if the workbook is open, the code only
activates
it. Thanks.

Jackie

"Jon Peltier" wrote:

By "goes to the army dashboard", do you mean the army workbook is opened,
or
is it already open? If your code first opens the workbook, this explains
the
error. Make the code a little smarter, so that, if the workbook is
already
open, your code only activates it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jackie" wrote in message
...
I have four dashbards each with 4 charts containing macros, and each in
it's
own workbook. The data for the macros is all in the same workbook. It
works
fine.

This morning I had a request to create a table of contents so that the
user
could click a button and open a dashboard. I crated this in a separate
workbook. It includes buttons with macros so, if the user clicked on
army,
for example, they would go to the dashboard with all of the army
information.
I created it in it's own workbook.
Then I decided that if they were going to use a table of contents to go
to
a
dashboard, I needed a way for them to get back to the TOC. So I created
a
button in each dashboard that will take the user back to the TOC.
Here's
my
problem:
When a user is in the TOC and selects army, he goes to the army
dashboard.
Then he selects 'back to TOC" if he wants to return. However, if the
user
forgets, and selects army again, one of two things happens; either they
get a
message saying the dashboard is already open, and then they get a VB
error,
or they are allowed to go back to the dashboard getting no error. But
ALWAYS,
when they go back to the dashboard, the chart that has the 'return to
TOC"
button loses focus, and they have to click out of the chart and click
back
in. By losing focus I mean the chart has three circles on each side
instead
of 3 black squares. The user thinks the chart doesn't work.
How do I keep the chart from losing focus. This is my first time
creating
macros. Is there someting special I should be doing? Thank you.

Hi Jon,
I get a syntax error he
If WB.fullname = k:\readiness and customer ops\fp_dash.xls Then

I tried changing fullname to the name of the file. I know nothing about VB.
I appreciate your help.

Jackie



"Jon Peltier" wrote:

Use something like this:


' sFileName is the path and file name of the workbook to be activated or
opened

For Each WB In Application.Workbooks
If WB.FullName = sFileName Then
' found the file
WB.Activate
bFound = True
Exit For
End If
Next
If Not bFound Then
Application.Workbooks.Open sFileName
End If


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______




"jackie" wrote in message
...
By goes to the army dashboard, I mean that the dashboard is opend by the
macro. Once it's opened if it's already open, I get the error. I would
like
to do what you are suggesing but I don't know how. Can you explain to me
how
I can make it work so that if the workbook is open, the code only
activates
it. Thanks.

Jackie

"Jon Peltier" wrote:

By "goes to the army dashboard", do you mean the army workbook is opened,
or
is it already open? If your code first opens the workbook, this explains
the
error. Make the code a little smarter, so that, if the workbook is
already
open, your code only activates it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jackie" wrote in message
...
I have four dashbards each with 4 charts containing macros, and each in
it's
own workbook. The data for the macros is all in the same workbook. It
works
fine.

This morning I had a request to create a table of contents so that the
user
could click a button and open a dashboard. I crated this in a separate
workbook. It includes buttons with macros so, if the user clicked on
army,
for example, they would go to the dashboard with all of the army
information.
I created it in it's own workbook.
Then I decided that if they were going to use a table of contents to go
to
a
dashboard, I needed a way for them to get back to the TOC. So I created
a
button in each dashboard that will take the user back to the TOC.
Here's
my
problem:
When a user is in the TOC and selects army, he goes to the army
dashboard.
Then he selects 'back to TOC" if he wants to return. However, if the
user
forgets, and selects army again, one of two things happens; either they
get a
message saying the dashboard is already open, and then they get a VB
error,
or they are allowed to go back to the dashboard getting no error. But
ALWAYS,
when they go back to the dashboard, the chart that has the 'return to
TOC"
button loses focus, and they have to click out of the chart and click
back
in. By losing focus I mean the chart has three circles on each side
instead
of 3 black squares. The user thinks the chart doesn't work.
How do I keep the chart from losing focus. This is my first time
creating
macros. Is there someting special I should be doing? Thank you.

If WB.fullname = "k:\readiness and customer ops\fp_dash.xls" Then

and if you're not sure of the capitalization, use:

If LCase(WB.fullname) = LCase("k:\readiness and customer ops\fp_dash.xls")
Then

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"jackie" wrote in message
...
Hi Jon,
I get a syntax error he
If WB.fullname = k:\readiness and customer ops\fp_dash.xls Then

I tried changing fullname to the name of the file. I know nothing about
VB.
I appreciate your help.

Jackie



"Jon Peltier" wrote:

Use something like this:


' sFileName is the path and file name of the workbook to be activated or
opened

For Each WB In Application.Workbooks
If WB.FullName = sFileName Then
' found the file
WB.Activate
bFound = True
Exit For
End If
Next
If Not bFound Then
Application.Workbooks.Open sFileName
End If


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______




"jackie" wrote in message
...
By goes to the army dashboard, I mean that the dashboard is opend by
the
macro. Once it's opened if it's already open, I get the error. I would
like
to do what you are suggesing but I don't know how. Can you explain to
me
how
I can make it work so that if the workbook is open, the code only
activates
it. Thanks.

Jackie

"Jon Peltier" wrote:

By "goes to the army dashboard", do you mean the army workbook is
opened,
or
is it already open? If your code first opens the workbook, this
explains
the
error. Make the code a little smarter, so that, if the workbook is
already
open, your code only activates it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jackie" wrote in message
...
I have four dashbards each with 4 charts containing macros, and each
in
it's
own workbook. The data for the macros is all in the same workbook.
It
works
fine.

This morning I had a request to create a table of contents so that
the
user
could click a button and open a dashboard. I crated this in a
separate
workbook. It includes buttons with macros so, if the user clicked on
army,
for example, they would go to the dashboard with all of the army
information.
I created it in it's own workbook.
Then I decided that if they were going to use a table of contents to
go
to
a
dashboard, I needed a way for them to get back to the TOC. So I
created
a
button in each dashboard that will take the user back to the TOC.
Here's
my
problem:
When a user is in the TOC and selects army, he goes to the army
dashboard.
Then he selects 'back to TOC" if he wants to return. However, if the
user
forgets, and selects army again, one of two things happens; either
they
get a
message saying the dashboard is already open, and then they get a VB
error,
or they are allowed to go back to the dashboard getting no error.
But
ALWAYS,
when they go back to the dashboard, the chart that has the 'return
to
TOC"
button loses focus, and they have to click out of the chart and
click
back
in. By losing focus I mean the chart has three circles on each side
instead
of 3 black squares. The user thinks the chart doesn't work.
How do I keep the chart from losing focus. This is my first time
creating
macros. Is there someting special I should be doing? Thank you.