Way to select a range using the mouse on a chart?

omahadivision · #1 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

Astacus · #2 12-20-2012, 03:15 PM

How about if you have the user just select the range before running the macro, and then, instead of 'ThisRange', use 'Selection' in the macro?

omahadivision · #3 12-20-2012, 05:10 PM

I thought about that, but because the data is over 1600 rows, hand-selecting can be rather difficult.

Astacus · #4 12-21-2012, 11:46 PM

I suspect I may be missing your problem. You do want the user to select it, right? If so, there are other ways to select areas, such as using Shift with arrow keys and PgDn, or entering "B53:B150" in the address box.

But if you want to do it in VBA, you can do that with:
Range("B53:B150").Select

omahadivision · #5 12-22-2012, 11:34 AM

Sorry for not being more specific. My goal is to select the range directly from the chart as opposed to the datapoints. I have written a module that detects the minimum in any range but I want the range to be determined by clicking on or dragging a rectangle on the actual chart. For instance, in the attached graph, I would want to figure out the minimum peak around 1400 wavenumbers by selecting the range around the peak. I have it set up so that a user can type in a range and it will calculate the minimum, but it would be cooler if they could just click on the chart.

Thanks!

Test.png

Astacus · #6 12-29-2012, 01:23 AM

Sorry, I now see that you actually explained that pretty clearly in your OP. Yes, that would be neat if you could do that! It seems that it may be possible in Office ≥ 2010, which I currently don't have. Check out http://msdn.microsoft.com/en-us/libr.../ff837379.aspx; there are two events that you could use: MouseDown and MouseUp.

omahadivision · #7 12-29-2012, 09:21 AM

Thanks! I'll look into that, but as I also need stuff to work with Office 2007, it may take a few years! For now I'll just have users select a range from an input box like you suggested.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Making VBA select chart and then export as picture	omahadivision	Excel Programming	2	10-28-2012 07:53 PM
Mouse Over Animation	dslocum	PowerPoint	4	03-23-2012 09:20 AM
Can't select or edit chart data	itguy	PowerPoint	2	09-19-2010 10:13 AM
Converting manual chart to bar chart?	aligahk06	Excel	0	07-03-2010 12:23 PM
Select a range in one one workbook while working in other workbook	Slow&Steady	Excel	1	02-21-2010 03:34 AM