RefEdit

[SteveDB1]

hi all.
I posted in another post yesterday regarding my first user form.
The one response I received in return said that in order to allow the user
to select a range, I needed to use the RefEdit control.
We recently purchased John Walkenbach's Power Programming book for Excel
2007, and in reading the discussion on refedit, I'm unclear on the syntax I
need to use to make my textboxes work.

On pages 452/453 of his book is the discussion for RefEdit.
I get a type mismatch error when I use Set, and no error when I use Let. But
it doesn't allow me to select a range, either way.


Either way it still does not allow me to select a range.
I then did some searching on the MS KB, and found an old discussion on this
topic-- from 2000, 2002. http://support.microsoft.com/kb/213776
It has the code for a command_Click button-
Dim SelRange as Range
Dim Addr as String

'Get the address, or reference, from the RefEdit control.
Addr = RefEdit1.Value

'Set the SelRange Range object to the range specified in the
'RefEdit control.
Set SelRange = Range(Addr)

This too does not allow for a range selection.

At this point, I'm just frustrated.
If I require a RefEdit to select a range, how am I suppose to state it?


Thank you.
SteveB.

 

[Jim Rech]

I just slapped a refedit and a command button on a userform. I attached
this code to the button:

Private Sub CommandButton1_Click()
Dim Rg As Range
Set Rg = Range(RefEdit1.Text)
MsgBox Rg.Address
End Sub

I run the userform and click on the refedit. Then I select a range in the
current sheet and click the command button. The message box shows me the
range I selected. You must be skipping some step. Maybe the selecting in
the sheet?

--
Jim
| hi all.
| I posted in another post yesterday regarding my first user form.
| The one response I received in return said that in order to allow the user
| to select a range, I needed to use the RefEdit control.
| We recently purchased John Walkenbach's Power Programming book for Excel
| 2007, and in reading the discussion on refedit, I'm unclear on the syntax
I
| need to use to make my textboxes work.
|
| On pages 452/453 of his book is the discussion for RefEdit.
| I get a type mismatch error when I use Set, and no error when I use Let.
But
| it doesn't allow me to select a range, either way.
|
|
| Either way it still does not allow me to select a range.
| I then did some searching on the MS KB, and found an old discussion on
this
| topic-- from 2000, 2002. http://support.microsoft.com/kb/213776
| It has the code for a command_Click button-
| Dim SelRange as Range
| Dim Addr as String
|
| 'Get the address, or reference, from the RefEdit control.
| Addr = RefEdit1.Value
|
| 'Set the SelRange Range object to the range specified in the
| 'RefEdit control.
| Set SelRange = Range(Addr)
|
| This too does not allow for a range selection.
|
| At this point, I'm just frustrated.
| If I require a RefEdit to select a range, how am I suppose to state it?
|
|
| Thank you.
| SteveB.
|

 

[SteveDB1]

Jim,
Thank you for the response.
My missing some code wouldn't surprise me at all at this point.
I just tried yours and it works fine.


Allow me to ask the following.

RefEdit is supposed to work for doing text boxes as well as command buttons,
correct?

Perhaps I've just been looking at this wrong, but with my system at work,
it's not allowing me to place a RefEdit control.
I've looked in my c:\program files\office\office12 directory, and the
refedit.dll does in fact exist.
What I've been trying to do with my user for thus far is to use a textbox
for my range input, and call to refedit code.
I even thought that perhaps my placement of a textbox would prevent the
placement of a refedit box, but I just tried it a moment ago here at home and
it worked exactly as you stated.
Thanks.

 

[Jim Rech]

RefEdit is supposed to work for doing text boxes as well as command
Sorry, Steve, but I don't know what you mean by "work for doing" .
RefEdits, Text Boxes and Command Buttons are three different controls,
totally independent. A Refedit allows text and like a text box, but it also
lets you select a range in Excel.

I don't know why you can't add a refedit to a userform at work. I mean, if
the refedit appears on Toolbox in the VBE you should be able to add it to a
userform. If it doesn't appear on the Toolbox then you have to right-click
the Toolbox and select Additional Controls. It's in the list as
Refedit.Ctrl.

 

[SteveDB1]

Hi Jim,
Sorry for the ambiguity in my statement.
What I had done was to use text boxes for my range input. I learned
yesterday that they do not work for range inputs. I then attempted to insert
a RefEdit box, and I received an error stating that my attempt to do so was
not an acceptable operation.
My question was leaning towards mixing the textbox with the RefEdit because
of the error I received.
The RefEdit icon was indeed loaded on my toolbox in VBE.
I then thought that perhaps the process I used was corrupted, or performed
inaccurately, and tried some variations which also did not work.
I'll pick this up again on Monday, to see what I did wrong once I get back
to the office. It's working on my system here at home, so I'll lay good odds
at this point that I just did something wrong.
As such, I'll post again on Monday t let you know my results-- one way or
another.
Thanks.
Best.

 

[Jim Rech]

I learned yesterday that they do not work for range inputs.

Well, a user can't select a range in the sheet but he can type an entry.
You'd have to validate that is was a true range.

Sub Test()
MsgBox TextIsARg("A1")
End Sub

Function TextIsARg(StrRg As String) As Boolean
Dim TempRg As Range
On Error Resume Next
Set TempRg = Range(StrRg)
TextIsARg = (Err.Number = 0)
End Function

 

[SteveDB1]

Morning Jim,
Ok, back in the office now.
I did a bit of digging, and found something out that suprised me.
It appears that my RefEdit icon on my tool box was a faux icon with no
actual connection to RefEdit-- or any actual device.
While I'm unclear as to how that can occur, I found the actual RefEdit, and
placed it on my toolbox, and then removed the original one.

I am now back in business, and will be applying your code to my user form.
I'll let you know if I have any more questions.

Thanks for the clarifications.
SteveB.