Controls (Control Toolbox): RefEdit Control doesn&#

Relman · Aug 13th 2003

This should be dead simple:

1. Put a RefEdit Control on a worksheet (from the Control Toolbox).
2. Add a Button beneath it.
3. Name cell D1 "AddressLine" (or anything you like).
4. Code the button like this:

Private Sub CommandButton1_Click()
Range("AddressLine").Value = RefEdit1.Value
End Sub

5. Select the RefEdit box, drag over cells A1 to A10, click the button and D1 reads "Sheet1!$A$1:$A$10".

Only it doesn't work. The RefEdit doesn't collapse when you click its button, nothing appears in it when you drag over or select any cells, and D1 stays blank.

The only time it works is if you type something in the RefEdit box, like "BLABBER", then press the button, D1 reads 'BLABBER'

What am I doing wrong?

**royUK** · Aug 13th 2003

If you try this the refedit should allow you to select the range you want

Code

Sub CommandButton1_Click()
	
	          Dim rng as Range
	          Dim ref as String
	
	          'Get the address or reference, from the RefEdit control.
	          ref = RefEdit1.Value
	
	          'Set the Range (ref) object to the range specified in the
	          'RefEdit control.
	          Set rng = Range(ref)
    'now some code for the selected range eg turn font to Italic and Bold


rng.Font.Bold = True
rng.Font.Italic = True

Display More

Relman · Aug 13th 2003

Thanks for replying Roy, but sadly it didn't work.

I don't seem to be able to get anything into the RefEdit unless I type or paste it in; in that respect it's no better than a text box!

The attached worksheet shows the effect I'm trying to get. It uses a UserForm with a RefEdit on it which works perfectly!

Maybe RefEdit controls only work on UserForms??

Relman · Aug 15th 2003

Bump

Relman · Aug 15th 2003

I've put a Forms Button on the sheet and assigned this macro to it:

Code

Public Sub GetRange()
Range("AddressLine").Value = Selection.Address
Range("B7").Select
End Sub

which works just fine! :bouncy:

Now, if I only knew the ColorIndex values and what colours they turn out like, I could fill in the selected range like this:

Code

Public Sub GetRange()
Range("AddressLine").Value = Selection.Address
Range(Selection.Address).Interior.ColorIndex = 4 'Or the value for tan/Light Blue or whatever
Range("B7").Select
End Sub

Any suggestions for a table of ColorIndex values?

Once again, Thanks for helping!

**royUK** · Aug 15th 2003

Here's a quick tip - use the Macro Recorder to colour a cell the colour that you want and then check the Index number in the recorded macro.

Relman · Aug 15th 2003

Good one Roy! TVM

I also found this great site:

http://www.mvps.org/dmcritchie/excel/colors.htm

with tables of all the colours in all the versions; check it out!

Thanks again guys!

Chris.

Controls (Control Toolbox): RefEdit Control doesn&#

Participate now!

Share