PDA

View Full Version : Solved: bar diagram



maryam
01-19-2007, 05:32 AM
How Can I have an actice bar diagram on a User Form in VBA in Excel and how to define the macros for that?

JonPeltier
01-19-2007, 07:07 AM
Two ways:

Export chart as image file, load picture into image control on form
http://www.j-walk.com/ss/excel/tips/tip66.htm

Copy chart, use APIs to load clipboard contents into image control
http://oaltd.co.uk/DLCount/DLCount.asp?file=PastePicture.zip

You could also investigate the Office Web Charting Component, but I have never used it.

maryam
01-21-2007, 06:49 PM
I cannot download the demo file in the first link. in the second Zip file there is a lot of programming under module and form. Is there any easier way to make an active Bar diagram?

JonPeltier
01-21-2007, 06:52 PM
If there were a simpler way, do you think such smart programmers as John Walkenbach and Stephen Bullen would have wasted time developing these techniques?

johnske
01-21-2007, 08:23 PM
I'm not into charting, but I have a basic example of a userform chart using OWC (Office Web Components) here (http://xlvba.3.forumer.com/index.php?showtopic=209) that I did up for demo purposes that may get you started...

maryam
01-21-2007, 10:37 PM
Dear JohnSke,
This is fine. but how can I put North,south,Esat,west the horizentol axis and 1997 and 1998 the vertical bar diagrams?



Thanks & Regaeds,
Maryam

johnske
01-21-2007, 11:02 PM
Where it says .Type = chChartTypeBarClustered, change that to .Type = chChartTypeColumnClustered :)

johnske
01-21-2007, 11:28 PM
FYI, here's a list of all the chart-types available for OWC charts...


chChartTypeArea
chChartTypeAreaStacked
chChartTypeAreaStacked100
chChartTypeBarClustered
chChartTypeBarStacked
chChartTypeBarStacked100
chChartTypeBubble
chChartTypeBubbleLine
chChartTypeColumnClustered
chChartTypeColumnStacked
chChartTypeColumnStacked100
chChartTypeCombo
chChartTypeDoughnut
chChartTypeDoughnutExploded
chChartTypeLine
chChartTypeLineMarkers
chChartTypeLineStacked
chChartTypeLineStacked100
chChartTypeLineStacked100Markers
chChartTypeLineStackedMarkers
chChartTypePie
chChartTypePieExploded
chChartTypePieStacked
chChartTypePolarLine
chChartTypePolarLineMarkers
chChartTypePolarMarkers
chChartTypePolarSmoothLine
chChartTypePolarSmoothLineMarkers
chChartTypeRadarLine
chChartTypeRadarLineFilled
chChartTypeRadarLineMarkers
chChartTypeRadarSmoothLine
chChartTypeRadarSmoothLineMarkers
chChartTypeScatterLine
chChartTypeScatterLineFilled
chChartTypeScatterLineMarkers
chChartTypeScatterMarkers
chChartTypeScatterSmoothLine
chChartTypeScatterSmoothLineMarkers
chChartTypeSmoothLine
chChartTypeSmoothLineMarkers
chChartTypeSmoothLineStacked
chChartTypeSmoothLineStacked100
chChartTypeSmoothLineStacked100Markers
chChartTypeSmoothLineStackedMarkers
chChartTypeStockHLC
chChartTypeStockOHLC

maryam
01-21-2007, 11:35 PM
Thank you Johnski.

maryam
01-22-2007, 03:28 AM
Dear Johnske,
Where is VBA icon?

johnske
01-22-2007, 03:40 AM
Dear Johnske,
Where is VBA icon?Sorry, what do you mean?

maryam
01-22-2007, 04:50 AM
Made your code more readable, use VBA tags (http://www.vbaexpress.com/forum/showthread.php?t=3200)

johnske
01-22-2007, 05:41 AM
Oh OK :) It's on the top just to the right of centre in the pane where you post your reply...

JonPeltier
01-22-2007, 05:57 AM
So is the Office Web Component any simpler than using John's or Stephen's examples?

maryam
01-22-2007, 06:07 AM
Dear JohnPeltier,
I could just open Stephen's file and couldnt open demo file of John's.Thank you for replying my thread, but there were a lot of coding behind the module and form in Stephen's file which I couldnt understand. Office web component is just adding two controls to the form and 10 lines of programming. Sorry my VBA is very bad, but the problem is that I badly need it as I should develope a software related to chemical engineering as soon as possible. So I need your helps.



Best Regards,
Mariam

JonPeltier
01-22-2007, 06:53 AM
The problem with OWC is if you send the file to another user, they may not have OWC installed. OWC is not installed by default, nor is it an option in all editions of Office.

Stephen's code is complicated, but you don't need to manipulate it much.

Why could you not download John's example? Do the IT nazis at work prohibit downloads of exe files? His example is really pretty simple.

johnske
01-22-2007, 07:42 AM
Jon has a point there Maryam, OWC was only introduced in office 2000, so anyone using office 97 may have difficulties if they haven't installed OWC (it can be installed separately on '97).

Now I have office 2000 and my own examples that were created in office 2000 have been downloaded from here and from my own site over 80 times with not one post to say that it doesn't work.

However, I've noted that with others OWC files posted here that were created in office 2002 or 2003 - I can't get the OWC component to show simply because it was created in a later version.

In other words, the only real problem I see would be if someone were trying to view it in an earlier version than yours - but if you're using the earliest version (office 2000) the only problem then would be '97 users. :)

maryam
01-22-2007, 07:19 PM
dear Johnske I am using exel 2003.
Dear JonPeltier, I cannot open John's example still. When I want to open the demonstration it goes to extraction and finally doesn't come!

JonPeltier
01-23-2007, 06:46 AM
Save the exe file to disk, then double click on it. I have had no problem in the three or four times I've tested this during the life of this thread.

I would say in terms of ease and reliability, John's is best, Stephen's nearly so, and OWC somewhat further down the list.

maryam
01-23-2007, 06:50 AM
Thank you JohnPeltier,
I have another post about DATAGRID Control, Malik and Tommy replied to that thread, but now some there are some days that there is no other thread. DO you know about this also. Can you look at that post also please.

maryam
01-23-2007, 06:56 AM
I can save it but when double click to open it it shows " The publisher could not be verified" and when I click on run Zip magic Self-Extracting Archive comes and when I click on next and finally finished, nothing happend!!!!!:help

maryam
01-23-2007, 07:08 AM
when I extract the file it is not meaningful. would you please sent it to me via maryamunique@yahoo.com.


1000 times thanks

maryam
01-23-2007, 07:23 AM
Dear Jon,
Please ignore my last posts. I could open John's file finally. Thank you very much. If you can help with DataGrid Control I would be appritiated.



Regards,
Maryam

JonPeltier
01-23-2007, 07:25 AM
I've only used the DataGrid enough to determine that it was more trouble to me than I wanted to deal with for my applications. For most cases, I get by with a multi-column listbox and a set of textboxes and buttons to work with the selected row of the listbox.

maryam
04-30-2007, 06:38 PM
I dont necessarily want to have a bar diagram on a form, it can be a chart in a chartspace control on the worksheet. when I try Johnske's suggestion it works fine, but I cannot renew the diagram unless I change in the spread sheet. I dont need a spreadsheet and want to work with the data on the worksheet itself.

Bob Phillips
05-01-2007, 01:18 AM
Save the exe file to disk, then double click on it. I have had no problem in the three or four times I've tested this during the life of this thread.

I would say in terms of ease and reliability, John's is best, Stephen's nearly so, and OWC somewhat further down the list.

Not to mention that OWC is obsolete technology, it is superceded by Excel Services. Obviously a good idea that wasn't such a good idea (Or maybe just not enough of a revenue generator).

maryam
05-01-2007, 06:59 PM
but john's and Stephan's coding are for bringing the diagram to the form. I dont need it to be on the form. I just want a dynamic diagram on a worksheet itself.

JonPeltier
05-01-2007, 07:56 PM
Wait, you want a chart on a worksheet? This is built-in Excel functionality, doesn't need code or OWC or anything. Select your data, start the chart wizard, and follow the steps until you have a chart.

maryam
05-02-2007, 07:56 PM
You don't need VBA. Adjust the named ranges in this example to define horizontal, not vertical, ranges:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html


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


Local Time: 10:55 PM
Local Date: 05-02-2007
Location: http://www.vbaexpress.com/forum/images/flags/United%20States%203D.gif http://www.vbaexpress.com/forum/images/flags/states/Massachusetts%203D.gif

http://www.vbaexpress.com/forum/images/buttons/report.gif (http://www.vbaexpress.com/forum/report.php?p=98320)