Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
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. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
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. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
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. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
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. |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
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. |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
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. |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
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. |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
Here is my original code. It works except I cant count on it not locking up the chart. Sub open_fp_wrkbk() ' ' open_fp_wrkbk Macro ' Macro recorded 10/31/2008 by hp90673 ' ' ChDir "K:\Readiness and Customer Ops" Workbooks.Open Filename:="K:\Readiness and Customer Ops\FP_dash.xls" ActiveSheet.ChartObjects("Chart 7").Activate End Sub Below is the code you sent that I am trying to input. I entered it exactly as you explained in your last messge, but I get a error: --compile error: syntax. error Expected then or go to-- and the line --If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then-- is in red Heres all of the code as I enter it: Sub open_fp_wrkbk() ' ' open_fp_wrkbk Macro ' Macro recorded 10/31/2008 by Jackie ' For Each WB In Application.Workbooks If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then ' found the file WB.Activate bFound = True Exit For End If Next If Not bFound Then Application.Workbooks.Open sFileName End If I tried it with and without the LCase. Thank you. Jackie "Jon Peltier" wrote: 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. |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
Compare your line:
If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then with my suggestion: If LCase(WB.fullname) = LCase("k:\readiness and customer ops\fp_dash.xls") If you don't put quotes around the file name, VBA tries to figure out what commands you want it to execute. Also, if you're comparing the lower case (LCase) of a file name to a string with capital letters, you will never find a match. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "jackie" wrote in message ... Here is my original code. It works except I can't count on it not locking up the chart. Sub open_fp_wrkbk() ' ' open_fp_wrkbk Macro ' Macro recorded 10/31/2008 by hp90673 ' ' ChDir "K:\Readiness and Customer Ops" Workbooks.Open Filename:="K:\Readiness and Customer Ops\FP_dash.xls" ActiveSheet.ChartObjects("Chart 7").Activate End Sub Below is the code you sent that I am trying to input. I entered it exactly as you explained in your last messge, but I get a error: --compile error: syntax. error Expected then or go to-- and the line --If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then-- is in red Here's all of the code as I enter it: Sub open_fp_wrkbk() ' ' open_fp_wrkbk Macro ' Macro recorded 10/31/2008 by Jackie ' For Each WB In Application.Workbooks If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then ' found the file WB.Activate bFound = True Exit For End If Next If Not bFound Then Application.Workbooks.Open sFileName End If I tried it with and without the LCase. Thank you. Jackie "Jon Peltier" wrote: 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. |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
Thank you!. Sorry it took so long. I am swamped.
"Jon Peltier" wrote: Compare your line: If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then with my suggestion: If LCase(WB.fullname) = LCase("k:\readiness and customer ops\fp_dash.xls") If you don't put quotes around the file name, VBA tries to figure out what commands you want it to execute. Also, if you're comparing the lower case (LCase) of a file name to a string with capital letters, you will never find a match. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "jackie" wrote in message ... Here is my original code. It works except I can't count on it not locking up the chart. Sub open_fp_wrkbk() ' ' open_fp_wrkbk Macro ' Macro recorded 10/31/2008 by hp90673 ' ' ChDir "K:\Readiness and Customer Ops" Workbooks.Open Filename:="K:\Readiness and Customer Ops\FP_dash.xls" ActiveSheet.ChartObjects("Chart 7").Activate End Sub Below is the code you sent that I am trying to input. I entered it exactly as you explained in your last messge, but I get a error: --compile error: syntax. error Expected then or go to-- and the line --If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then-- is in red Here's all of the code as I enter it: Sub open_fp_wrkbk() ' ' open_fp_wrkbk Macro ' Macro recorded 10/31/2008 by Jackie ' For Each WB In Application.Workbooks If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then ' found the file WB.Activate bFound = True Exit For End If Next If Not bFound Then Application.Workbooks.Open sFileName End If I tried it with and without the LCase. Thank you. Jackie "Jon Peltier" wrote: 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. |
#12
Posted to microsoft.public.excel.charting
|
|||
|
|||
macro in chart loses focus
I am swamped.
Tell me about it.... - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "jackie" wrote in message ... Thank you!. Sorry it took so long. I am swamped. "Jon Peltier" wrote: Compare your line: If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then with my suggestion: If LCase(WB.fullname) = LCase("k:\readiness and customer ops\fp_dash.xls") If you don't put quotes around the file name, VBA tries to figure out what commands you want it to execute. Also, if you're comparing the lower case (LCase) of a file name to a string with capital letters, you will never find a match. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "jackie" wrote in message ... Here is my original code. It works except I can't count on it not locking up the chart. Sub open_fp_wrkbk() ' ' open_fp_wrkbk Macro ' Macro recorded 10/31/2008 by hp90673 ' ' ChDir "K:\Readiness and Customer Ops" Workbooks.Open Filename:="K:\Readiness and Customer Ops\FP_dash.xls" ActiveSheet.ChartObjects("Chart 7").Activate End Sub Below is the code you sent that I am trying to input. I entered it exactly as you explained in your last messge, but I get a error: --compile error: syntax. error Expected then or go to-- and the line --If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then-- is in red Here's all of the code as I enter it: Sub open_fp_wrkbk() ' ' open_fp_wrkbk Macro ' Macro recorded 10/31/2008 by Jackie ' For Each WB In Application.Workbooks If LCase(WB.FullName) = k:\readiness and customer Ops\FP_dash.xls Then ' found the file WB.Activate bFound = True Exit For End If Next If Not bFound Then Application.Workbooks.Open sFileName End If I tried it with and without the LCase. Thank you. Jackie "Jon Peltier" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - chart data update loses legend | Charts and Charting in Excel | |||
Pivot chart loses data connection when copied to new workbook | Charts and Charting in Excel | |||
HELP!! Unhide Sheet with Macro and focus on other sheet | Excel Discussion (Misc queries) | |||
focus | Excel Discussion (Misc queries) | |||
Focus | Excel Worksheet Functions |