Excel Help

[GeorgeUK]

looks like this does it.

Code: Select all

Dim sourcechart As Range
Dim z As String
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True

Dim UserWants As Range
Dim chartarea As Range
Dim x As Range
   
Set UserWants = ActiveCell
Set chartarea = Sheets("Sheet2").Range("A" & UserWants.Row)
Set x = chartarea.End(xlToRight)

Dim cht As Excel.Chart
Set cht = Charts.Add

Set sourcechart = Sheets("Sheet2").Range(chartarea.Address, (Cells(UserWants.Row, x.Column).Address))
z = sourcechart.Address
With cht
    .ChartType = xlLine
    .SetSourceData Source:=sourcechart, PlotBy:= _
    xlRows
    .Location Where:=xlLocationAsObject, Name:="Sheet1"

End With

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow
Dim CopyingTo As Range
Static x As Range
Set lastrow = Sheets("Sheet2").Range("A65536").End(xlUp)
Set CopyingTo = Sheets("Sheet2").Range("A5:A" & lastrow.Row)

Range(Range("F5"), Range("F65536").End(xlUp)).Copy _
Destination:=CopyingTo.End(xlToRight).Offset(0, 1)

    If CopyingTo.End(xlToRight).Offset(0, 1).Column > 120 Then
        CopyingTo.Offset(0, 1).Delete Shift:=xlToLeft
    End If

If ActiveSheet.ChartObjects.Count > 0 Then

    ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range(z), PlotBy:= _
    xlRows
End If
End Sub



Just alter the column > 120 to whatever you need
note - a fast refresh rate will allow ver little detail to display. maybe only a few seconds worth.

[bmann13]

Hi GeorgeUk,

Thanks for the updated code. Pardon my ignorance, but when I paste the code in the VB editor and run the sheet nothing happens. The prices are updating in the excel sheet as per usual but that is all. When I click on any runners nothign happens, no graph nothing. I must be doing something silly here.

[bmann13]

Okay I got it working but I am getting a few errors. When I try to click on another runner I get the following error:-

Run-time error '1004':

Application-defined or object-defined error

I then press the debug button and the VB editor opens up with ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range(z), PlotBy:= _
xlRows highlighted...any ideas why this is happening?

Again, I would like to thank you for taking the time and effort to help me.

[GeorgeUK]

The code (i think) seems to only be able to handle one chart open at a time.

Are you deleting the chart before opening another or do you need more than one open at a time?
(This may take a while to work out - going out tonight so can't do any coding)

There's also a bug in the code making it move faster than it should. Instead of the price updating just once, it is putting the price in many times because of the number of runners. I'll have it update only when the clock changes so you should get a better result from the data and graph.

[bmann13]

I was trying to open more then one chart without deleting the first chart. I would like to have more then one chart open... the 3 first runners. I'm being a bit ambitious here but is it possible to also plot the volume under the prices traded like in the bet angel vid?

Do this when you got time...have a great time out

[GeorgeUK]

It's never simple - is it?

I thought chart data could only be used if it was from the same sheet.
Kinda right. We can create a chart, then add a 2nd series of data from sheet3 so we can track the volume in the same way.

I don't have sound at work so can't hear what they are saying about the volume. Is it a % movement up and down? I'll have a listen to it when i get home. Not sure how we get more than one chart to show. i'd imagine you would want to be able to access the odds data so you can make bets.

Just done a quick search and it seems charts can't be put into userforms, which would keep them out of the way of the rest of the data. It seems it is possible to put them in a DialogSheet - not as a chart, but a gif image. Longshot - constantly saving and changing the gif image of the chart to c:\temp\ No idea how or if it will work though. Excel doesn't seem to have the ability of putting charts of any description let alone dynamic charts into forms

Anyone have any ideas??

We could have a "chart sheet" with the charts you doubleclicked to see?

Now you know why i usually have nothing to do with charts

[bmann13]

Yikes!

Have we opened pandora's box