Microsoft Spreadsheet Control 9

Could you clarify what a "spreadsheet control" is and how you got the charts on a userform? I'm slightly confused and this information will probably get you a more swift response.

sorry,

I added the chart to the Userform by adding the microsoft graph 9 control and the microsoft spreadsheet control to the control toolbox from the Tools, References menu in the VBA editor in excel.

I now need to link the source data for the charts back to an excel worksheet. The spreadsheet object is supposed to have the same functionality as excel worksheets but I can not work out how to link it back to a worksheet.

My form is modeless so the user can change the source data in the worksheet. I am using the two graphs to show a before and after picture. The after comes as a result of certain parameters being changed in the worksheet!

I hopw this clarifies what I am trying to do!

Mark, I think he's refering to

Microsoft Office Chart Control

and

Microsoft Office Spreadsheet Control

Which can let you display the web components spreadsheets and charts on a userform (Just learning now how to use them).

If you want to test this add a user form, and right click on the controls to click on "More controls...". In there, look for the Microsoft Office Chart and Microsoft Office Spreadsheet controls and check them. Put one of each in the userform, and don't change their names.

In the userform put some simple values, like
<pre>
Name Value
a 1
b 2
c 3
d 4
e 5</pre>

and then, in the Userform_Initialize() event, put this code:

<pre>Private Sub UserForm_Initialize()
With ChartSpace1
Set c = .Constants
Set .DataSource = Spreadsheet1
.Charts.Add
With .Charts(0)
.Type = c.chChartTypeArea
.SetData c.chDimCategories, 0, "A2:A6"
.SetData c.chDimSeriesNames, 0, "B1"
.SeriesCollection(0).SetData c.chDimValues, 0, "B2:B6"
End With
End With
End Sub</pre>

BINGO ! a "real" chart on a userform, which you can update in real time.

Now.... what the OP wants is to link the chart to the object 'Sheet1', not the 'Spreadsheet1' control, which I haven't figured out yet. But, this is cool !

Boxer, I think this is cheating, but works. I too made it modeless, and, as you saw in the previous code, my chart range is A1:B6 in both the Sheet and the Spreadsheet.

So, I placed this code in the Sheet1 module

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B6")) Is Nothing Then
UserForm1.Spreadsheet1.Cells(Target.Row, Target.Column).Value = Target.Value
End If
End Sub</pre>

I also set the Visible property of Spreadsheet1 to False, so it would 'look' like it's a "real" link.

Bingo Juan

Thats exactly what I want to do, Mark, sorry if I didnt explain it to well!!

Signing off now been at work for nearly 14 hours, my boss is dragging me to the nearest bar for a few beers!!!

I hope you manage to figure this one out, it is something I have spent the last week trying to do!!!

I look forward to seeing what you come up with.

Cheers

Boxer

Great one Juan. I passed this one up and I hate to do that, but I was whipped on this one. From Boxer's last reply, I do not think that he realizes that the problem has been solved?

Tom
This message was edited by TsTom on 2002-08-09 14:32

Cheers Juan

we must have been typing out replies at the same time, I will try this when I get back to work on monday.

I really hope I can apply your code into my application.

Once again, many thanks. These new controls are really intriging to me!!

You are the man!!!!!!!!!!

Yes Tom, I think this is pretty weird. For example, the Spreadsheet control doesn't have a Range object , of course, the resize and offset methods are gone too.

I added this piece to the Initialize event to keep the userform in synch with the sheet.<pre> Dim i As Long
Dim j As Integer
For i = 1 To 6
For j = 1 To 2
Spreadsheet1.Cells(i, j).Value = Sheet1.Cells(i, j).Value
Next j
Next i</pre>

But, yes, pretty cool... a live chart in a userform. The spreadsheet control is pretty cool too, but has many limitations, for example, no array formulas.

Also, the help file sucks... very few examples and some methods don't even have a help associated.


_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-08-09 14:52

Juan

You are right it is very cool!!!

Many thanks for this. I am trying to work out exactly how your code works, so I can adapt it for future projects.



Boxer