New Chart types - other than the Excel built in charts

[lohithsriram]

Hi All,


We have a custom chart generator tool used in our organization to generate pre-defined chart types(that again basic charts) in specific colors, size etc., to keep it uniform across the regions, across different departments and people.


All the basic charts are covered/available in this tool along with few custom combinations like (1 Column + 2 Lines Axis, 2 lines + 2 Columns Axis etc.,) and this was built for Excel 2003 version. Now, we are in a phase of migrating to Office 2010 and as a part of migration, have a plan to revamp the custom chart tool to add some more exciting financial based charts.


I need all your expert help to suggest some custom chart types for me to try and test it and check the feasibility in adding it up to the tool. Also, if you can share your experience with such migrations, measures to do/not do and anything that you want me to hear about.


I have listed few chart types (Pareto, Waterfall, Histogram etc.,) and need a similar ones.


Thank you,

Lohith

 

[bobhc]

Good day Lohith


You do not say what/where this custom generator came from, how it works..nothing, if your company paid for it check with the seller if it will migrate and for new chart types, if it was made in house check with the programmer.

A lot that works in 2003 will not work in 2010, 2010 has a much larger spreadsheet cell area and some cell references/names in 2003 will crash in 2010.

As for the difference between 2003 and 2010 you are entering a happy Excel world....But every one of your users will scream blue murder about the lose of the old drop-down menus and will demand that they get them back....this is impossible....make them learn the almighty ribbon....if they are still complaining after two weeks I have found that to make the change over move smoothly is to sack the buggers...sack one the rest learn real quick.

 

[lohithsriram]

Hi bobhc,


Many thanks for writing on this. Sorry that I got busy with some urgent stuff and didn't get time to reply.

Though the idea of "Sack one, get all done" drools me, unfortunately I will have to implement this for the management and I might stand first on the list if that so happens Back on the topic, we are in the phase of migration and YES, people cribbing about moving to 2010 is a common scene for me now, but they do not have any choice as the projects in pipeline work only with 2010.

About the chart tool, this is a in-house product customized to make fit to the company needs to generate the basic/built in charts in specific size and colors, as these charts will be copied to the Word templates/documents where a defined chart holders are placed and charts will be copied into it. To put it simple, this is a tool to fix the size and color of the built in charts in excel.

I have now migrated this Office 2003 chart tool to 2010 with VBA fixes wherever required and made it to work exactly AS-IS what it does in 2003. Since this tool is pretty old and with the growing needs we need to add the capability of generating advanced chart types like (Pareto, Waterfall, Histogram etc.,).


The tool has a basic screen to select the data,chart type, define the series properties etc. and provides a preview of what chart are we making. I am happy to discuss further on this.


I need more help from you experts before I start on this. Thank you.

 

[Smallman]

Hi Bob


Getting your drop down menus back is possible with a slight of hand. I saw this puppy years ago on J Walkenbach’s site. I just went back there found it and reworked his code (updated it slightly). Put the following in your personal macro workbook.

[pre]

Option Explicit
Option Base 1
Sub MakeXL03 ()
Dim cb As CommandBar
Dim cbc As CommandBarControl
Dim OldMenu As CommandBar
Dim ar As Variant
Dim i As Integer

ar = Array("&File", "&Edit", "&View", "&Insert", "F&ormat", "&Tools", "&Data", "&Window", "&Help")
On Error Resume Next
Application.CommandBars("Old Menus").Delete
On Error GoTo 0
Set OldMenu = Application.CommandBars.Add("Old Menus", , True)
For i = LBound(ar) To UBound(ar)
CommandBars("Built-in Menus").Controls(ar(i)).Copy OldMenu
Next i
Application.CommandBars("Old Menus").Visible = True
End Sub

Now run this from the workbook module of your personal macro workbook.


Private Sub Workbook_Open()
MakeXL03
End Sub

[/pre]
Now you and your old school menus are wedded together forever. Giddy Up!


Smallman

 

[bobhc]

Good day Smallman


Nice....but to be honest it is my belief that the drop down is dead, all hail the mighty ribbon, the ribbon is far easier to use once that initial small learning curve has been conquered.I am using 2013 and it has some nice improvements, I think the biggest problem is Microsoft, I know they have to earn a living but many small to medium company's (the backbone of most countries economy's can not afford to keep upgrading at the price asked, also many do not want to use software that is in the cloud.A lack of understandable trust)so that means that tens of thousands of user are still stuck with 2003, by the time the worlds economy picks up and allows company's to upgrade users will have a real struggle to come to terms with the ribbon and all the new concepts in Excel 2068.

 

[bobhc]

Good day Lohith


You say it is a in-house product so to be honest I think you would need to post the code so that the VBA'ers can have a look at it,......but this would make it public software, so perhaps you could upload sections of code and ask the relevant question!!

 

[Smallman]

Hi Bob


It was just for demonstation purposes. If you want it it is there. After using the Ribbon I think it is an improvement. You can even customise the ribbon to have all your favs in the one place but I have held off that because I will not know how to use someone else's machine if I get too used to it. I am not using XL 13 yet but can buy it with work, home use Office Pro for AUD 15. This is very reasonable. The HUP program is not available to all though.


Take care


Smallman

 

[lohithsriram]

Hi Bob, Smallman,


Thank you for writing on this post. I can't share the code as the compliance norms are very strict here and moreover it has got several modules, Form and a class modules. I am throwing a survey to the floor and the end users about the common charts they would expect which is not available on the tool and based on that will list out the top5/10 charts.


I would need you guys help in future when we start to work on these new chart types and will share my plan and projections on it. Thank you.

 

[bobhc]

@Lohith


Please let us know how you get on