mattrix
09-30-2019, 11:10 PM
I'm working on a procedure, and I need the user to give me a few ranges to work with.
So I have set up a form using RefEdit controls. Trouble is the user can enter all sorts of garbage into RefEdit. I have tried to validate at the point of entry but have tied myself in knots trying to get this to work with RefEdit's, so I have settled for this code.
'InterpForm
Private xVals As Range
Private yVals As Range
Private outRange As Range
...
Private Sub CommandButton1_Click()
'stuff the tests, just check we at least have ranges,
On Error GoTo NotRange:
Set xVals = Range(RefEdit1.Value).Columns(1)
Set yVals = Range(RefEdit2.Value).Columns(1)
Set outRange = Range(RefEdit3.Value).Columns(1)
On Error GoTo 0
MsgBox (xVals.Address & "," & yVals.Address & "," & outRange.Address)
' Can I minimize/hide the form whilst interp does its stuff?
' Call Interp(xVals, yVals, outRange)
Exit Sub
NotRange:
'clear refedits and start again
Call CommandButton2_Click
RefEdit1.SetFocus
End Sub
' End InterpForm
But I've got a few problems,
firstly the "range(" function.
Apparently this has an implied ActiveSheet but RefEdit will include a sheet reference in its value. So if the active sheet is Sheet1 I could get:
set xVals = Worksheets("Sheet1").range("sheet2!c:c")
this does not seem correct, sheet2 is not in sheet1. What should I make the "range(" explicit to?
Secondly, I intended the parameters to interp to be complete single columns (A1:A & maxRow, what is the xl constant for maxRow?) on whichever sheet is appropriate and let interp sort it out from there, but that is not what I get. How do I get the complete column without losing the sheet info?
So I have set up a form using RefEdit controls. Trouble is the user can enter all sorts of garbage into RefEdit. I have tried to validate at the point of entry but have tied myself in knots trying to get this to work with RefEdit's, so I have settled for this code.
'InterpForm
Private xVals As Range
Private yVals As Range
Private outRange As Range
...
Private Sub CommandButton1_Click()
'stuff the tests, just check we at least have ranges,
On Error GoTo NotRange:
Set xVals = Range(RefEdit1.Value).Columns(1)
Set yVals = Range(RefEdit2.Value).Columns(1)
Set outRange = Range(RefEdit3.Value).Columns(1)
On Error GoTo 0
MsgBox (xVals.Address & "," & yVals.Address & "," & outRange.Address)
' Can I minimize/hide the form whilst interp does its stuff?
' Call Interp(xVals, yVals, outRange)
Exit Sub
NotRange:
'clear refedits and start again
Call CommandButton2_Click
RefEdit1.SetFocus
End Sub
' End InterpForm
But I've got a few problems,
firstly the "range(" function.
Apparently this has an implied ActiveSheet but RefEdit will include a sheet reference in its value. So if the active sheet is Sheet1 I could get:
set xVals = Worksheets("Sheet1").range("sheet2!c:c")
this does not seem correct, sheet2 is not in sheet1. What should I make the "range(" explicit to?
Secondly, I intended the parameters to interp to be complete single columns (A1:A & maxRow, what is the xl constant for maxRow?) on whichever sheet is appropriate and let interp sort it out from there, but that is not what I get. How do I get the complete column without losing the sheet info?