Making interactive pick on a chart created by VBA

[phudgens]

Does anyone know if it is possible to make interactive data picks on an Excel chart that has been created by VBA? And if those picks, once made, can be fed automatically back into the VBA program?

Thanks,
Paul Hudgens
Denver

 

[xlbo]

can you expand on: "interactive data picks" ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[phudgens]

Once a chart has been generated by my VBA code, I'd like to be able to interactively make a pick on that plot with my mouse, the coordinates of which would then be fed back into the VBA program for further processing.

Thanks,
Paul H.

 

[xlbo]

You would probably have to somehow interact with the mouseover / mousedown chart events to get the co-ordinates of where you are picking / clicking

If you wanted to use just 1 program to create the chart and then pick up the co-ords, you might be able to do so by utilising a public boolean variable that gets set in the mousedown event and gets tested in a loop by your main program - would probably also have to use Do-Events to allow the mouseover event to fire

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Check out the GetChartElement Method. You have events available IF your chart is a ChartSheet.

However, if you have an embedded chart in a sheet, you must create a class object to expose the chart events in your ChartObject.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phudgens]

Based on Geoff's mention of the mouseover / mousedown options I did some other digging and came up with an example using the GetChartElement Method. I have incorporated that, and it is working, except that it uses the mouse x,y coordinate system rather than the plot x,y coordinate system. I'm trying to figure out how to convert between the two ( ie using the scale mins and maxes), and then send that data back to the original VBA program.

Thanks,
Paul H.

 

[SkipVought]

What are you using the coordinates for?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phudgens]

The goal is to allow the user to select particular points of interest ( in this case geological tops ) and then do further calculations using the original data within the areas of interest selected by the user.

Thanks,
Paul H.

 

[SkipVought]

"geological tops" are not chart objects.

Are you saying that the user selects a chart object (a series, a point)

You do NOT need mouse coordinates for this.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phudgens]

The user picks points on the graph (series) based on certain criteria. Those x,y points (in the plot coordinate system) would then be used by the original VBA program to do further calculations within the selected areas of interest. The primary goal is to acquire those x and y coordinates selected interactively by the user on the screen, and then returning control to the VBA program (or possibly to a new VBA program).

Thanks,
Paul H.

 

[SkipVought]

Have you looked at the VBA Help for GetChartElement? Depending on the ElementID, the Arg1 & Arg2 values give your data for determining that kind of information, WITHOUT using the coordinates.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[phudgens]

The GetChartElement method returns X and Y of the user selected point in pixels. The PlotArea properties are all measured as points (72/inch). Without a way to correctly convert pixels to points (which varies from computer to computer depending on resolution), I cannot convert X and Y to my data coordinate system. The GetChartElement method provides no such conversion.

 

[SkipVought]

The GetChartElement method returns X and Y of the user selected point in pixels.

Oh, really?

The user picks points on the graph (series) based on certain criteria.

Example: Click on a SERIES.

GetChartElement returns ElementID equal to xlSeries
GetChartElement returns Arg1 equal to SeriesIndex
GetChartElement returns Arg2 equal to PointIndex

Given these three values (NOT X,Y coordinates), one can determine EXACTLY where the user selected and run with the corresponding values.

Is that not what you are looking for?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Just in case you think I've been blowin' smoke, and only know what I read in the book...

Private Sub EmbChart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim IDNum As Long, rPN As Range
    Dim a As Long
    Dim b As Long
    [b]
    'get chart element (pass x,y coordinates)
    ActiveChart.GetChartElement x, y, IDNum, a, b
    
    'chart element SERIES
    If IDNum = xlSeries Then
    
        'if the 6th series is selected
        If a = 6 Then
        
            'assign the selected series POINT to SelectedDT
            wsSFC_ChartData1.[SelectedDT] = wsSFC_ChartData1.Range("Dte")(b)[/b]
            GetShort_Trav
            GetPN_NG_Trav_Short
            
            Set rPN = wsSFC_OPS.Columns(1).Find(wsSFC_ChartData1.[SelectedPN])
            
            'position the embedded spreadsheet object
            With wsSFC_OPS.ssShort
                .Visible = True
                .Top = rPN.Top
                .Left = rPN.Offset(0, 1).Left
                .Height = wsSFC_OPS.Range(rPN, rPN.Offset(wsSFC_ChartData1.[SelectedPN_Cnt])).Height
                .Width = wsSFC_OPS.ChartObjects(1).Width
            End With
            'load the embedded spreadsheet
            Sheet_Load
            
        Else
            wsSFC_OPS.ssShort.Visible = False   'embedded spreadsheet
        End If
    Else
        wsSFC_OPS.ssShort.Visible = False       'embedded spreadsheet
    End If
    InstatiateChart
End Sub

I have used this technique for years to make charts interactive.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

Just in case you think I've been blowin' smoke"


and......wheweeeee...we can smell ya from here.

Come on Skip, no one would ever accuse you of blowin' smoke. We ALL know you inhale.

Gerry