Microsoft Office Forums - View Single Post - Way to select a range using the mouse on a chart?

omahadivision · 12-20-2012, 08:10 AM

Hi all,

I have written a module that determines the row of a address of a range of a series on my scatter plot chart. It works great except for one thing - I need to enter the custom range in VBA or by selecting the cells. I was wondering if there was a way to draw a rectangle or something similar on the chart and then have the range on the X axis covered in the rectangle become the range in the macro. There is only one series on my chart. Basically, it's a wavelength spectrum where the peaks vary, and I'm looking for ways to select individual peaks.

Here's the code as is with the range manually entered, with minpoint1 being the address of the minimum:

Code:

Dim minpoint1 As Integer

minpoint1 = MinAddress(Range("B53:B150"))

And the function MinAddress:

Code:

Function MinAddress(ByRef ThisRange As Range) As String
    Dim cel As Range
    For Each cel In ThisRange
        If cel = Application.WorksheetFunction.Min(ThisRange) Then MinAddress = cel.Row
    Next cel
End Function

Thanks!

-Dave