UserForm - initialize objects

In this module:

1. UserForm: Initialize a RefEdit control
2. 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
  
  
• Label:
  • Name: Label1
  • Caption: InRange:
  
  
• RefEdit:
  • Name: RefEdit1
  
  
• CommandButton
  • Name: cmdClose
  • Caption: Close

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.

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).

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

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
  
  
• Label:
  • Name: Label1
  • Caption: InRange:
  
  
• TextBox:
  • Name: txtInRange
  
  
• CommandButton
  • Name: cmdClose
  • Caption: Close

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.

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).

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.

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

Copyright © 2011 – 2023 ♦ Dr Ian O'Connor, CPA.   |   Privacy policy

Comment & Reply policy