If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Colours in user-defined charts
I have one more question about user-defined charts:
Is it possible to define the colours to be used? I can change the colours in usrgal.xls but when I apply the user-defined chart, the colurs revert to those in the current workbook. Any way around this? The users can not be expected to import the colours for every book and they have a lot of old books in wich they are going to use the new colour-set. -- Fredrik E. Nilsen |
#2
|
|||
|
|||
Colours in user-defined charts
Hi Fredrik,
As you have discovered the you can change the user defined colors for any workbook but the pallete is workbook specific. If all the colors you want to use are in the default pallete then you can create a User Defined custom chart using any of those colors and they will be available in all workbooks when you applie the User Defined chart. The downsides of this approach are 1. you must create a user defined chart for each of the chart types you use, 2. user defined charts don't retain all attributes so if the charts are fancy you may loose some features such as linked titles or linked data lables, 3. to apply the new colors used in the default chart requires a number of steps. 4. If all of the colors you want to use are not in the default pallete then the above approach fails. An easier way to deal with the problem might be to create a custom color pallete for the personal macro workbook and add a macro that imports that pallete into any workbook. Assuming you have created the custom pallete in the Personal Macro Workbook then the code you need to add to the PMW is very simple: Sub CustomChartColors() ' Keyboard Shortcut: Ctrl+Shift+C ActiveWorkbook.Colors = Workbooks("PERSONAL.XLS").Colors End Sub The shortcut key is optional. Alternately you could create a macro that redefines the colors of the pallete in any workbook and place that macro in the personal macro workbook. With either of these two approaches you will need to add the macro or the macro and the pallete to the personal marco workbook of each user. Of course you don't need to use the personal macro workbook, you could use an XLA. The advantage of an XLA is that you don't need to mess with the PMW which may already have thing in it. And a single copy of the XLA can live on a server. Sincerely, Shane Devenshire "Fredrik E. Nilsen" wrote: I have one more question about user-defined charts: Is it possible to define the colours to be used? I can change the colours in usrgal.xls but when I apply the user-defined chart, the colurs revert to those in the current workbook. Any way around this? The users can not be expected to import the colours for every book and they have a lot of old books in wich they are going to use the new colour-set. -- Fredrik E. Nilsen |
#3
|
|||
|
|||
Colours in user-defined charts
On Sun, 17 Dec 2006 07:17:01 -0800, ShaneDevenshire
wrote: Hi Fredrik, As you have discovered the you can change the user defined colors for any workbook but the pallete is workbook specific. If all the colors you want to use are in the default pallete then you can create a User Defined custom chart using any of those colors and they will be available in all workbooks when you applie the User Defined chart. The downsides of this approach are 1. you must create a user defined chart for each of the chart types you use, 2. user defined charts don't retain all attributes so if the charts are fancy you may loose some features such as linked titles or linked data lables, 3. to apply the new colors used in the default chart requires a number of steps. 4. If all of the colors you want to use are not in the default pallete then the above approach fails. An easier way to deal with the problem might be to create a custom color pallete for the personal macro workbook and add a macro that imports that pallete into any workbook. Assuming you have created the custom pallete in the Personal Macro Workbook then the code you need to add to the PMW is very simple: Sub CustomChartColors() ' Keyboard Shortcut: Ctrl+Shift+C ActiveWorkbook.Colors = Workbooks("PERSONAL.XLS").Colors End Sub The shortcut key is optional. Alternately you could create a macro that redefines the colors of the pallete in any workbook and place that macro in the personal macro workbook. With either of these two approaches you will need to add the macro or the macro and the pallete to the personal marco workbook of each user. Of course you don't need to use the personal macro workbook, you could use an XLA. The advantage of an XLA is that you don't need to mess with the PMW which may already have thing in it. And a single copy of the XLA can live on a server. Sincerely, Shane Devenshire Thank you very much for your reply, this clarifies a lot. In this case, I guess it would make sense to define the colours in xlusrgal.xls and to refer to that in the code sample you provided. A couple of more questions then comes to mind: If I apply your code this affects all charts in the workbook. This is not necessarily a problem but is there a way to affect only the selected chart? Regarding your first paragraph, I'm aware of the problems regarding user-defined charts not retaining all attributes. It is also impossible to know what kind of changes they are going to try to make so no matter how detailed my user-defined charts are, at one point my settings will fail. The charts are not going to be all that fancy really, the real problem is that there are too many of them to format manually. What I would like ideally was a button to apply all my settings on the selected chart, no matter how many lines, columns, axis etc. With "my settings" I mean: Colours, Line thickness, No legend, No axis names, Fixed textsizes, No background colour, White frame colour etc. Any simple way to do this? The users are experts in the financial system but really useless when it comes to Excel... -- Fredrik E. Nilsen |
#4
|
|||
|
|||
Colours in user-defined charts
Hi Fredrik,
To the best of my knowledge you can only apply colors from the current workbook's color pallete and the color is referenced not by its color but by its position. If you use my suggestion of the XLA macro you can put the XLA on a server and tell each user how to attach it. Once attached all they will need to do in the future is press the shortcut key whenever they open an old file with chart which they want to have updated to the new color scheme. It's a one time process for each file, not each chart. If you need to know how to make the file an xla and attach it, let me know. -- Cheers, Shane Devenshire "Fredrik E. Nilsen" wrote: I have one more question about user-defined charts: Is it possible to define the colours to be used? I can change the colours in usrgal.xls but when I apply the user-defined chart, the colurs revert to those in the current workbook. Any way around this? The users can not be expected to import the colours for every book and they have a lot of old books in wich they are going to use the new colour-set. -- Fredrik E. Nilsen |
#5
|
|||
|
|||
Colours in user-defined charts
On Sun, 17 Dec 2006 15:16:01 -0800, ShaneDevenshire
wrote: Hi Fredrik, To the best of my knowledge you can only apply colors from the current workbook's color pallete and the color is referenced not by its color but by its position. If you use my suggestion of the XLA macro you can put the XLA on a server and tell each user how to attach it. Once attached all they will need to do in the future is press the shortcut key whenever they open an old file with chart which they want to have updated to the new color scheme. It's a one time process for each file, not each chart. If you need to know how to make the file an xla and attach it, let me know. Thanks again Shane. I have already made the XLA and made a toolbar with all the user-defined charts, it works like a charm but with the limitations mentioned in you earlier post. -- Fredrik E. Nilsen |
#6
|
|||
|
|||
Colours in user-defined charts
If you want to apply these on a chart by chart basis then the real approach
is to develop code for all the changes you want - line thickness, axis, colors and so on. Run that code from an xla just as described earlier. This is safer than applying a user defined chart because of the problems I mentioned. You code will require that the user select the chart and then execute the code. As for color, if the color is from the default pallete, you might as well include it in the macro rather than changing the pallete. When you change the pallete you effect everything that uses it not just all charts. Regards, Shane -- Thanks, Shane Devenshire "Fredrik E. Nilsen" wrote: On Sun, 17 Dec 2006 15:16:01 -0800, ShaneDevenshire wrote: Hi Fredrik, To the best of my knowledge you can only apply colors from the current workbook's color pallete and the color is referenced not by its color but by its position. If you use my suggestion of the XLA macro you can put the XLA on a server and tell each user how to attach it. Once attached all they will need to do in the future is press the shortcut key whenever they open an old file with chart which they want to have updated to the new color scheme. It's a one time process for each file, not each chart. If you need to know how to make the file an xla and attach it, let me know. Thanks again Shane. I have already made the XLA and made a toolbar with all the user-defined charts, it works like a charm but with the limitations mentioned in you earlier post. -- Fredrik E. Nilsen |
#7
|
|||
|
|||
Colours in user-defined charts
On Sun, 17 Dec 2006 16:15:00 -0800, ShaneDevenshire
wrote: If you want to apply these on a chart by chart basis then the real approach is to develop code for all the changes you want - line thickness, axis, colors and so on. Run that code from an xla just as described earlier. This is safer than applying a user defined chart because of the problems I mentioned. You code will require that the user select the chart and then execute the code. As for color, if the color is from the default pallete, you might as well include it in the macro rather than changing the pallete. When you change the pallete you effect everything that uses it not just all charts. Thanks again, I really appreciate the time you take to explain it in an understandable way. Do you have any pointers regarding coding all the changes? I'm in a bit over my head here but I have basic knowledge about VB. In "real" english it would be something like: On the selected chart (no matter what type of chart it is): Change all lines to... (thickness, markings etc) Change all columns to... (no border etc) Set textsize to... Set legend placement to... Set colours to... etc.... The colour issue is not really a problem in this particular case, though I see your point. But: If I pull the colours from chart fill and chart line colours in xlusrgal.xls, that won't affect the standard colours? I see that it will affect the last 16 colours of the palettes but thats not a problem, it's a bonus feature. The users in this case are financial analysts and they make huge reports in Word every day. They pull charts from Excel in to Word and want to achieve consistant formatting throughout all the charts. -- Fredrik E. Nilsen |
#8
|
|||
|
|||
Colours in user-defined charts
I always recommend starting with the recorder. Then looking at the code and
deciding what needs to be generalized. This can be pretty complex. I would make the recording on the most complex chart I have to modify and then work from there. I would also recommend that you check out Jon Peltier website since he has lots of code and examples http://PeltierTech.com. -- Cheers, Shane Devenshire "Fredrik E. Nilsen" wrote: On Sun, 17 Dec 2006 16:15:00 -0800, ShaneDevenshire wrote: If you want to apply these on a chart by chart basis then the real approach is to develop code for all the changes you want - line thickness, axis, colors and so on. Run that code from an xla just as described earlier. This is safer than applying a user defined chart because of the problems I mentioned. You code will require that the user select the chart and then execute the code. As for color, if the color is from the default pallete, you might as well include it in the macro rather than changing the pallete. When you change the pallete you effect everything that uses it not just all charts. Thanks again, I really appreciate the time you take to explain it in an understandable way. Do you have any pointers regarding coding all the changes? I'm in a bit over my head here but I have basic knowledge about VB. In "real" english it would be something like: On the selected chart (no matter what type of chart it is): Change all lines to... (thickness, markings etc) Change all columns to... (no border etc) Set textsize to... Set legend placement to... Set colours to... etc.... The colour issue is not really a problem in this particular case, though I see your point. But: If I pull the colours from chart fill and chart line colours in xlusrgal.xls, that won't affect the standard colours? I see that it will affect the last 16 colours of the palettes but thats not a problem, it's a bonus feature. The users in this case are financial analysts and they make huge reports in Word every day. They pull charts from Excel in to Word and want to achieve consistant formatting throughout all the charts. -- Fredrik E. Nilsen |
#9
|
|||
|
|||
Colours in user-defined charts
"Fredrik E. Nilsen" wrote in message ... A couple of more questions then comes to mind: If I apply your code this affects all charts in the workbook. This is not necessarily a problem but is there a way to affect only the selected chart? Shane's code affects the color palette in effect for the entire workbook. Therefore it affects every chart and every worksheet in the workbook. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ |
#10
|
|||
|
|||
Colours in user-defined charts
"Fredrik E. Nilsen" wrote in message ... The colour issue is not really a problem in this particular case, though I see your point. But: If I pull the colours from chart fill and chart line colours in xlusrgal.xls, that won't affect the standard colours? I see that it will affect the last 16 colours of the palettes but thats not a problem, it's a bonus feature. If you pull the color palette from the user gallery workbook (i.e., copy the color palette), it will affect all 56 colors of the palette. If you pull the colors from the chart element colors of the user gallery (i.e., apply the user-defined chart types), it will not affect any of the palette colors in the target workbook. The color index of each chart element will use the color index (position in the palette) of the element in the user gallery, but not necessarily the same color if the palettes are not the same. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ |
Thread Tools | |
Display Modes | |
|
|