UserForm - initialize objects

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.


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.


trading suspended
Fig 1a: CurrentRegion - column vector with cell B3 as 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).


trading suspended
Fig 1b: CurrentRegion - 3 row by 3 column array with ActiveCell (blank) at element(2,2)

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.


trading suspended
Fig 1c: CurrentRegion - is 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

xlf alert 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
xlf alert 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.


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.


trading suspended
Fig 3: CurrentRegion - column vector with cell B3 as ActiveCell. 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.


xlf tip Compatible with a UserForm in vbModeless operation - TextBox with ReduceStyleDropButton


 

trading suspended
Fig 4: Range Selectors compared - RefEdit (left) and TextBox-InputBox (right)