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.
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.
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.
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
Add the following code to get the value from the “RefEdit” control:
ctrlAdd = RefEdit1.Value
Copy and paste the following code to change the background of the range selected to blue:
Set rangeSelected = Range(ctrlAdd)
rangeSelected.Interior.ColorIndex = 5
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.
- Photo Credit Stockbyte/Stockbyte/Getty Images