How To Return the cells referenced by the user using RefEdit control of user form in Excel?

In this blog post, we are going to see how to return the cells referenced by the user using RefEdit control of user form in Excel. Let’s get into this article!! Get an official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Example

• Firstly, you need to create a sample data with name and login hour of team member.

Sample data

• In the Excel Worksheet, you have to go to the Developer Tab.
• Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic

• Now, you have to add RefEdit in user form, and click on RefEdit control on toolbox.

Add RefEdit

• After that, you have to copy and paste the code given below.

Option Explicit
Sub running()
UserForm1.Show
End Sub

'Add below code in "Ok" button on userform
Option Explicit
Private Sub CommandButton1_Click()
Dim SelectRange As Range
Dim Address1 As String
On Error GoTo Last
'Get the address from the RefEdit control
Address1 = RefEdit1.Value
'Set the SelectRange Range object to the range specified in the RefEdit control
Set SelectRange = Range(Address1)
'Highlight the selected range in yellow color
SelectRange.Interior.Color = RGB(255, 255, 0)
'Unload the userform.
Unload Me
Last:
End Sub

• You need to save the code by selecting it and then close the window.

Save the Code

• Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
• Then, you need to choose the Macros option in the Code section.

Choose Macro option

• Now, you have to make sure that your macro name is selected and click the *Run * button.

Run the Code

• After running the macro, for highlighting the cell , you have to click on the Highlighter button , and it will open the user form.

UserForm1

• Then, you need to select the range using RefEdit and click on dash sign for selecting cells.
• Now, for selecting adjacent cells , you have to press Shift key while moving to other cell.
• For selecting non-adjacent cells , you need to press Control key while selecting different cells and selected cells will be surrounded by dotted lines and cell address will appear in the box.

Select the range

• Finally, selected cells will be highlighted in yellow color on pressing the Ok button in Excel.

Output

Verdict

In the above post, you can learn the simple steps ** ** on how to return the cells referenced by the user using RefEdit control of user form in Excel. Kindly, share your feedback in the below comment section. Thanks for visiting Geek Excel. Keep Learning!

Keep Reading:

• Simple Shortcut to Move to the Next Control in MS Excel 365!!
• Easy Shortcut to Move to the Previous Control in Excel 365!!
• How to Create a Form for Search and Print through VBA in Microsoft Excel?
• Control Word from Excel using VBA in Microsoft Excel 2010
• How To Control Excel from Word using VBA in Microsoft Excel 2010?