How to Use Refedit in VBA

Learning how to use the “RefEdit” control can make your Visual Basic for Applications (VBA) more flexible. RefEdit is an ActiveX control that acts like the reference edit boxes built into Microsoft Excel. VBA is a computer programming language that can be used with any of the Microsoft Office applications to extend their functionality. The most common use of a “RefEdit” control is to select a cell or a range of cells to format them or obtain their values.

Things You'll Need

  • Microsoft Office Excel


    • 1

      Launch Microsoft Office Excel, click the “Developer” tab and click “Visual Basic” to launch the VBA Editor. Click the “Insert” menu and click “UserForm” to insert a new form to your project.

    • 2

      Click the “RefEdit” control from the “Toolbox” pane and click the form to add a new control. Add a “Command" button using the same technique.

    • 3

      Double-click the "Command" button to create a new click event. Add the following code inside the button click event to create two new variables:

      Dim rangeSelected As Range

          Dim ctrlAdd As String
    • 4

      Add the following code to get the value from the “RefEdit” control:

          ctrlAdd = RefEdit1.Value
    • 5

      Copy and paste the following code to change the background of the range selected to blue:

          Set rangeSelected = Range(ctrlAdd)
      rangeSelected.Interior.ColorIndex = 5
      Unload Me
    • 6

      Press the “F5” key to run the program and click the "underscore" button on the “RefEdit” control. Click any cell on your worksheet and click the "Command" button to change the background color of the cell.

Related Searches


  • Photo Credit Stockbyte/Stockbyte/Getty Images

Related Ads

Related Searches
View Blog Post

Geek Vs Geek: Windows 10: Should You Want Microsoft’s Next Contentious OS?