RefEdit ActiveX control

[Simon Shaw]

Hi,

I have a RefEdit control in my form, but I would like to force it to require
the selection of columns similar to using Page Setup where you select the
Columns to repeat on the left. Where the mouse cursor appears as a down
arrow, and no matter what you click, you select an entire column(s).

Is there a property of the RefEdit that I can change to force this? I
looked, but don't see anything obvious.

Thanks

Simon

 

[Jim Cone]

Simon,

RefEdit Controls are difficult to live and work with.
Give the following a try (note the RefEdit has been renamed).
'----------------------------------------------------------
Private Sub RefEditTest_Change()
On Error Resume Next
Dim objRng As Excel.Range
Set objRng = Range(RefEditTest.Text).EntireColumn
If Err.Number <> 0 Then Set objRng = Selection.EntireColumn
On Error GoTo NoRange
objRng.Select
Me.RefEditTest.Text = objRng.Address
Set objRng = Nothing
Exit Sub
NoRange:
Beep
Resume Next
End Sub

Private Sub UserForm_Initialize()
' Do not set the focus to the RefEdit control.
Me.RefEditTest.Text = Selection.Columns.Address
End Sub
'------------------------------------------------------

Regards,
Jim Cone
San Francisco, USA