Use a RefEdit to select a single cell

[JON JON]

Hello NG,

I wanted to use RefEdit in a userform to allow user to select just a single
cell (not a range). Is this possible? How? Please help

TIA

Jon-jon

 

[Frank Kabel]

Hi
AFAIK you can't restrict the selection BUT you can test the returned
result afterwards if it is only a 1*1 range

 

[Dave Peterson]

Or just use the top left cell of the first area in the range.

Option Explicit
Private Sub CommandButton1_Click()

Dim myCell As Range

Set myCell = Nothing
On Error Resume Next
Set myCell = Range(Me.RefEdit1.Value).Areas(1).Cells(1)
On Error GoTo 0

If myCell Is Nothing Then
MsgBox "please select a range"
Else
MsgBox myCell.Address
End If

End Sub

 

[JON JON]

Thank you, Dave and Frank

I follow Frank advise.

Warm regards,

Jon-jon