questions about ranges and refedit.

[mattrix]

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?

[Leith Ross]

Hello mattrix,

The RefEdit control returns a string representing the Range selected. To convert this string to a usable Range object, you need to use Evaluate. Here is an example that will convert the string into a Range object and also assign the object variable Wks to the worksheet the Range belongs to.



Private Sub CommandButton1_Click()

Dim Rng As Range
Dim Wks As Worksheet

Set Rng = Evaluate(RefEdit1.Value).Resize(ColumnSize:=1)
Set Wks = Rng.Parent

End Sub

[snb]

Use:


Sub M_snb()
Set r01 = Application.InputBox("Select a range", "snb", , , , , , 8)
End Sub

[mattrix]

snb, I like the type checking. I decided to use a form as it was a good idea to let the user see all their selections before they committed. I would have to rethink the design to use this, and I quite possibly will.

Leith,
The resize didn't achieve what I wanted, I need to change the range to the column that contains the original ranges first column on the sheet that contains it.

Also what is evaluate a member of, when no explicit parent is given?

[snb]

Like so ?


Sub M_snb()
c00 = "Your previous selections " & Replace(" A1:D10 G4:K12 A30:Z40 AA14:AF90 ", " ", vbLf)
Set r01 = Application.InputBox(c00, "Select a Range", , , , , , 8)
End Sub