How to Use Refedit in VBA

  • Print this article
Create a user interface with controls in Excel.
Create a user interface with controls in Excel.

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
Show More

Instructions

    • 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

References

  • Photo Credit Stockbyte/Stockbyte/Getty Images

Comments

Related Ads

Featured
View Mobile Site