UserForm - initialize objects
In this module:
- UserForm: Initialize a RefEdit control
- UserForm: Initialize a TextBox control with DropButtonStyle
1. RefEdit object
Initialize a RefEdit object with the address of the CurrentRegion.
Example
Objects used in this example (with selected properties)
The code is contained in the DemoRefEdit (UserForm) module.
- UserForm
- Caption:
xlf: Initialize RefEdit object
- Name:
DemoRefEdit
- Caption:
- Label:
- Name:
Label1
- Caption:
InRange:
- Name:
- RefEdit:
- Name:
RefEdit1
- Name:
- CommandButton
- Name:
cmdClose
- Caption:
Close
- Name:
The code for the UserForm Initialize event is in code1 lines 1 to 8. The RefEdit text property is set to the Address of the ActiveCell.CurrentRegion.
Code 1: Sub
UserForm_Initialize
initializes the UserForm
Private Sub UserForm_Initialize() With ActiveCell.CurrentRegion ' Check for current region with dimension greater then 1 by 1 If .Rows.Count > 1 Or .Columns.Count > 1 Then RefEdit1.Text = .Address End If End With End Sub Private Sub cmdClose_Click() Unload DemoRefEdit End Sub
Three cases are illustrated. In figure 1a the ActiveCell is in a column vector of values. The CurrentRegion extends from cell B1 to cell B5. Note: when the UserForm is active, the Excel name box display the address of the home cell of the CurrentRegion, not the address of the ActiveCell.

In figure 1b, although the ActiveCell is blank, the CurrentRegion, as defined is the range A10:C12. Again, the Excel name box displays the address of the CurrentRegion home cell (A10).

The ActiveCell, and adjacent rows and columns are blank in figure 1c. In this case there is no current region for the 1 by 1 array of the ActiveCell, so code 1 lines 4 to 6 returns blank.

Selecting the CurrentRegion
To extend the selection to the current region, include the code Range(.Address).Select
statement (see code 2 line 8).
Code 2: Sub
UserForm_Initialize
initializes the UserForm and selects the CurrentRegion
Private Sub UserForm_Initialize() With ActiveCell.CurrentRegion ' Check for current region with dimension greater than 1 by 1 If .Rows.Count > 1 Or .Columns.Count > 1 Then RefEdit1.Text = .Address ' Select the CurrentRegion Range(.Address).Select End If End With End Sub
Modality! - a RefEdit control will only function on a vbModal UserForm. Switching to Modeless causes Excel to freeze, and the session needs to be Ended via the Windows Task Manager
For vbModeless operation - use a custom TextBox - see Section 2
2. TextBox object with DropButtonStyle
2.1 Imitate the RefEdit control
Initialize a TextBox object with the address of the CurrentRegion.
Example
Objects used in this example (with selected properties)
The code is contained in the DemoRefText (UserForm) module.
- UserForm
- Caption:
xlf: Initialize RefText control
- Name:
DemoRefText
- Caption:
- Label:
- Name:
Label1
- Caption:
InRange:
- Name:
- TextBox:
- Name:
txtInRange
- Name:
- CommandButton
- Name:
cmdClose
- Caption:
Close
- Name:
The code for the UserForm Initialize event is in code3 lines 1 to 12. The TextBox text property is set to the Address of the ActiveCell.CurrentRegion in line 8.
Code 3: Sub
UserForm_Initialize
initializes the UserForm
Private Sub UserForm_Initialize() ' Attach a ReduceStyleDropButton to the TextBox txtInRange.DropButtonStyle = fmDropButtonStyleReduce txtInRange.ShowDropButtonWhen = fmShowDropButtonWhenAlways If ActiveCell.CurrentRegion.Rows.Count > 1 Or ActiveCell.CurrentRegion.Columns.Count > 1 Then txtInRange.Text = ActiveCell.CurrentRegion.Address Range(ActiveCell.CurrentRegion.Address).Select End If End Sub Private Sub cmdClose_Click() Unload DemoRefText End Sub
In figure 3 the ActiveCell was set to B3, in a column vector of values. The CurrentRegion extends from cell B1 to cell B5 the same as the figure 1a example. In this case, however, Code 3 line 9 selects the CurrentRegion.

Whilst the procedures in code 3 selects the current region, it does not allow the user to select a change to the range. To do this, we need to imitate the RefEdit range selector.
2.2 Imitate the RefEdit range selector
Activating the range selector involves coding the Click event for the TextBox DropDown icon (see code 4). The Click event calls the InputBox method, and this allows the user to select the desired range (code 4 line 5).
Code 4: Sub
txtInRange_DropButtonClick
codes the range selector button on the right of the custom TextBox
Private Sub txtInRange_DropButtonClick() ' The click event for the ReduceStyleDropButton Dim InRange As Range On Error Resume Next Set InRange = Application.InputBox("Select range", "xlf DemoRefText", txtInRange.Text, Type:=8) txtInRange.Text = InRange.Address On Error GoTo 0 End Sub
The RefEdit range selector and the TextBox range selector are compared in figure 4.
Compatible with a UserForm in vbModeless operation - TextBox with ReduceStyleDropButton

- This example was developed in Excel 2016 64 bit.
- Download the question: xlf-userform-initialize.xlsm [29 KB]
- Revised: Friday 24th of February 2023 - 02:37 PM, Pacific Time (PT)