Today we will read on this page how to use userform and range in Excel VBA program?

You can use the RefEdit control in Excel VBA to get a boundary from a user. User We are going to create a minimum color value of the range stored in the RefEdit control.

Download the demo excel file 

To create this user, follow these steps.

• Open the Visual Basic Editor. If Project Explorer is not visible, click View, Project Explorer.
• Click Insert, User. If the toolbox does not appear automatically, click View, Toolbox. Your screen should be given below.
• Add labels, RefEdit controls and command buttons. Once this is completed, the result should correspond to the user’s image shown earlier. For example, create a RefEdit control by clicking RefEdit from the toolbox. Next, you can drag the RefEdit control over the user’s correction.

Note: If your toolbox does not have a RefEdit control, then set a reference to the RefEdit control. Click Tools, References, and check the Ref Edit control.

• You can change the captions of names and controls. Names are used in Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of controls, but this is not necessary here because in this example we have only a few controls. To change the caption of a userform, label, and command button, click the View, Properties window, and click each control.
• To display the userform, place a command button on your worksheet and add the following code line:

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

Now we are going to create a sub-user. When you use the show method for Userform, this sub will be executed automatically.

Open the Visual Basic Editor -> In Project Explorer, right click on UserForm1 and then choose Userform from the View Code -> Left drop-down list. Choose Initial from the correct drop-down list. Add the following code lines:

Private Sub UserForm_Initialize()

Sheet1.Cells.Font.Color = vbBlack
UserForm1.RefEdit1.Text = Selection.Address

End Sub

We have now created the first part of Userform. Although it looks neat already, nothing will happen when we click the command button on the userform.

In Project Explorer, double click on UserForm1 -> Double click on Go button -> Add the following code lines:

Private Sub CommandButton1_Click()

Dim addr As String, rng, cell As Range, minimum As Double

addr = RefEdit1.Value
Set rng = Range(addr)
minimum = WorksheetFunction.Min(rng)

For Each cell In rng
    If cell.Value = minimum Then cell.Font.Color = vbRed
Next cell

End Sub

First, we obtain the address from the RefEdit control and store it in the string variable joiner. Next, we set rng to the range specified in the RefEdit control. Next, we use the worksheet function Min to find the minimum value in the range. Finally, we color the minimum value using a loop.

Double click on the Cancel button -> Add the following code line:

Private Sub CommandButton2_Click()

Unload Me

End Sub

Userform and Ranges in Excel VBA