Controls (Control Toolbox): RefEdit Control doesn&#

  • 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?

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

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

  • 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??

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

  • 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!

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!