refedit control in vba

Posted by alfrerdo sanchez on January 23, 2002 8:10 AM

Hi all!

Can anybody tell me how can I pass a cell address obtained via refedit control in userform to a sub in VBA?
The sub now works ok, but inputbox doesn't looks like the user have to put a range in instead of value. If I use refedit control, it shows navigation button, so the user knows it shall to be an address. Hope I made it clear. Thank you in advance.

Posted by DK on January 23, 2002 8:38 AM

Hi, like this:-

Private Sub CommandButton1_Click()
YourSub Range(Me.RefEdit1)
End Sub

Sub YourSub(AnyRange As Range)
'Do whatever
End Sub

You may be interested to know that you can use an inputbox which will allow the user to select a range with the mouse i.e.

Sub UseApplicationInputBox()
Dim rngeGetARange As Range
On Error Resume Next
Set rngeGetARange = Application.InputBox("Please select range...", "Select", , , , , , 8)

If rngeGetARange Is Nothing Then Exit Sub

rngeGetARange.Value = "Hello!"
End Sub

HTH,
D

Posted by alfredo sanchez on January 23, 2002 8:54 AM

Thank you very much... Just one more thing>>>

HI DK: thank you for your answer...

I've tried your sub example, maybe I'm wrong, but does the "8" in your example means "type=8"?, If so, maybe my version of excel doesn't fully support VBA, 'cause I have tried it before and it never shows the navigation option.( as a matter of fact, your example inputbox looks exactly the same as mine, without navigation button)
I haven't tried userform example, but I'll let you know...

Thank You Very Much.

Posted by DK on January 23, 2002 9:12 AM

Re: Thank you very much... Just one more thing>>>

Alfredo,

The 8 does indeed mean Type=8. You have to use the Application.Inputbox if you want to use this (Excel's own inputbox). If you omit the application bit then you will just be using a standard VB inputbox. I'm using Excel 2000 but I'm sure that this was also available in Excel 97.

Regards,
D