Hello .


I'm tryng to have my Refedit control to do this.

the user will click on the control then will select
a1
on Exit the refedit text should change to Sheet1!$A$1:Sheet!$H:$15
(I tried to do it without luck.)

Also I need to make sure they only select one cell in column A.

Thanks in advance.

Cesar Zapata

I put a commandbutton (Ok like) on the form and then validated from the

Option Explicit
Private Sub CommandButton1_Click()

Dim testRng As Range

Set testRng = Nothing
On Error Resume Next
Set testRng = Range(Me.RefEdit1.Value)
On Error GoTo 0

If testRng Is Nothing Then
Me.RefEdit1.SetFocus
MsgBox "Please select a range"
Exit Sub
End If

If testRng.Parent.Name < ActiveSheet.Name Then
Me.RefEdit1.SetFocus
MsgBox "On this sheet!"
Exit Sub
End If

If testRng.Cells.Count 1 Then
Me.RefEdit1.SetFocus
MsgBox "One cell only!"
Exit Sub
End If

If testRng.Column < 1 Then
Me.RefEdit1.SetFocus
MsgBox "must be in column A"
Exit Sub
End If

MsgBox testRng.Address

End Sub

===
You may want to be a little more lenient. Just pick up the row number from
first cell in their selected range:

Option Explicit
Private Sub CommandButton1_Click()

Dim testRng As Range

Set testRng = Nothing
On Error Resume Next
Set testRng = Range(Me.RefEdit1.Value)
On Error GoTo 0

If testRng Is Nothing Then
Me.RefEdit1.SetFocus
MsgBox "Please select a range"
Exit Sub
End If

If testRng.Parent.Name < ActiveSheet.Name Then
Me.RefEdit1.SetFocus
MsgBox "On this sheet!"
Exit Sub
End If

Set testRng = ActiveSheet.Cells(testRng.Row, 1)

MsgBox testRng.Address

End Sub


Cesar Zapata wrote:

Hello .

I'm tryng to have my Refedit control to do this.

the user will click on the control then will select
a1
on Exit the refedit text should change to Sheet1!$A$1:Sheet!$H:$15
(I tried to do it without luck.)

Also I need to make sure they only select one cell in column A.

Thanks in advance.

Cesar Zapata

--

Dave Peterson