skearse
05-31-2013, 08:03 AM
Hi all. First post, please go easy on me. I looked through old threads and tried searching but didn't come up with anything that seemed to do what I'm looking for. Anyway, I've got a spreadsheet macro that I'm using to create a control chart (line chart) from experimental data. I found some code from Databison that I've modified to handle the actual charting; I'm now working on tweaking the inputs to the macro a little bit. As is, the macro uses an InputBox to prompt the user to choose the range for the data values and then a second one for the labels:
InputBox Function
Public Function GetRange(box_message As String) As Range
Set GetRange = Nothing
On Error Resume Next
Set GetRange = Application.InputBox(box_message, "Select Range", Selection.Address, , , , , 8)
End Function
...
Function call in macro
Sub make_actives_control_chart()
Dim data_values As Range
Dim chart_labels As Range
Dim range_selected_before As Range
'Data values selection by user
Set data_values = GetRange("Select the data values to be plotted." & Chr(13) & "(Press select a single column)")
'Data labels selection by user
Set chart_labels = GetRange("Select the labels for the plotted data." & Chr(13) & "(Press ESC if no labels available)")
...
What I'm trying to do is to automate the selection of the labels range. The workbook has ~30 worksheets, one for each different product, and there are a varying number of row entries in each sheet depending on the number of samples-so, for instance, one product may have 32 samples while another only ten. Each time a sample is taken, a new row is added to the bottom of the corresponding worksheet. Ideally, the InputBox would be used for the values, but the code would then use the row numbers from that range and then get the labels from another column using those row numbers (ie, the user selects values from T14:T35 to plot, the macro then assigns the label range to be D14:D35 as since column D has the run number (ID) for the sample.
Sorry to be so long winded, hope I've made it at least somewhat clear, and thanks all in advance.
InputBox Function
Public Function GetRange(box_message As String) As Range
Set GetRange = Nothing
On Error Resume Next
Set GetRange = Application.InputBox(box_message, "Select Range", Selection.Address, , , , , 8)
End Function
...
Function call in macro
Sub make_actives_control_chart()
Dim data_values As Range
Dim chart_labels As Range
Dim range_selected_before As Range
'Data values selection by user
Set data_values = GetRange("Select the data values to be plotted." & Chr(13) & "(Press select a single column)")
'Data labels selection by user
Set chart_labels = GetRange("Select the labels for the plotted data." & Chr(13) & "(Press ESC if no labels available)")
...
What I'm trying to do is to automate the selection of the labels range. The workbook has ~30 worksheets, one for each different product, and there are a varying number of row entries in each sheet depending on the number of samples-so, for instance, one product may have 32 samples while another only ten. Each time a sample is taken, a new row is added to the bottom of the corresponding worksheet. Ideally, the InputBox would be used for the values, but the code would then use the row numbers from that range and then get the labels from another column using those row numbers (ie, the user selects values from T14:T35 to plot, the macro then assigns the label range to be D14:D35 as since column D has the run number (ID) for the sample.
Sorry to be so long winded, hope I've made it at least somewhat clear, and thanks all in advance.