Problems with embedded RefEdit control on worksheet

[Kelvin Stott]

Hi,

I have embedded a RefEdit control directly in a worksheet, but it doesn't seem to work. Essentially I would like the user to select a range, or ranges, so that the program can store the address in a linked cell. However, the control doesn't seem to take in the selected range. I have tried toggling different combinations of the control's properties, but still no luck...

Any thoughts on how to fix this, please?

 

[Kelvin Stott]

Do I need to do something with this?

Private Sub DRange1_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, _
ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, _
ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)

DRange1.Value = Selection.Address 'Just a guess at entering something to make it work...

End Sub

 

[Kelvin Stott]

Having read through a number of similar posts without resolution, I'm starting to give up on this approach, so...

Is there any other (simple) way to get the address of a user-selected range into a cell, without creating a user form?

 

[Kelvin Stott]

Bump?

 

[Domenic]

It looks like the RefEdit control doesn't have the same functionality on the worksheet as it does on a UserForm. Maybe the Input method of the Application object can be used instead. The following macro displays an input box and allows the user to select a range. If the user chooses one, it enters its address in the specified cell. If the user cancels, it exits the sub.

[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
[COLOR=darkblue]Sub[/COLOR] test()
    [COLOR=darkblue]Dim[/COLOR] UserSel [COLOR=darkblue]As[/COLOR] Range
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] UserSel = Application.InputBox( _
        Prompt:="Please select a cell or range of cells...", _
        Title:="Range Selection", _
        Type:=8)
    [COLOR=darkblue]If[/COLOR] UserSel [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    
    Worksheets("Sheet2").Range("A2").Value = UserSel.Address [COLOR=#008000]'change the sheet name and cell reference accordingly[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[/FONT]

 

[Kelvin Stott]

Thank you so much, I will give this a try...