Classes Archives - yoursumbuddy

RaiseEvent for Multiple UserForm Controls

Posted on November 5, 2017 by Doug Glancy

This Stack Overflow answer solves the problem: How can a form receive events from multiple instances of a WithEvents class? The simple answer is to use RaiseEvent but the problem is that when you create a collection of WithEvents classes, the RaiseEvent only works for the last class instance added to the collection. This post shows you how to connect each WithEvents control to a RaiseEvent using a delegate class and receive the event in the form. For example, you could have changes to any one of a group of textboxes trigger a change in another form control.

(When I came across the Stack Overflow post above I read the explanation but not the code. I let it bounce around my brain for a couple of days and then got the main point that you can just funnel the WithEvents control to the delegate when the WithEvents control fires. After that, writing the code was easy.)

Review: Create a Textbox WithEvents Class

In past posts I’ve discussed UserForm code that creates a collection of WithEvents classes to capture changes to all the textboxes:

Private cEvents As clsEvents
Public collControls As Collection

Private Sub UserForm_Activate()
Dim ctl As MSForms.Control

Set cDelegate = New clsDelegate
Set collControls = New Collection
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.TextBox Then
Set cEvents = New clsEvents
Set cEvents.cDelegate = cDelegate
Set cEvents.txtBox = ctl
collControls.Add cEvents
End If
Next ctl
End Sub

clsEvents looks like this:

Public WithEvents txtBox As MSForms.TextBox

Private Sub txtBox_Change()
debug.print "Textbox changed"
End Sub

That’s nice for creating a message, or opening a file, or some other action outside the form. But sometimes you want to receive the event in the form and use it as a trigger to change other form controls.

You can add an Event to clsEvents so that it can be raised in the txtBox_Change event and received by another class, such as a form, but again the problem is that only the last textbox added to collControls will raise the event. Previously, I’ve coded around that by passing a reference to the userform into clsEvents, and manipulated the form from the class. It works, but it’s dirty. Plus I think it creates a memory leak.

Modification to allow multiple RaiseEvents

The solution is to create another class, a delegate, that is also instantiated in the userform. The delegate has a single public control property that is set by any of the clsEvents instances, one at a time, as their txtBox_Change event fires. When the txtBox variable is passed to the cDelegate it raises its txtBox_Changed event, which is then received by the cDelegate_Change routine back in the form. This way there’s only one instance of cDelegate, but its txtBox variable refers to whichever clsEvents textbox has changed most recently.

Delegate Diagram

Here’s the code for clsDelegate:

Public Event txtBoxChanged(txtBox As MSForms.TextBox)

Public Sub PassControl(txtBox As MSForms.TextBox)
RaiseEvent txtBoxChanged(txtBox)
End Sub

So simple! And here’s the modified code for clsEvents:

Public WithEvents txtBox As MSForms.TextBox
Private m_cDelegate As clsDelegate

Public Property Set cDelegate(value As clsDelegate)
Set m_cDelegate = value
End Property

Private Sub txtBox_Change()
m_cDelegate.PassControl txtBox
End Sub

m_cDelegate holds the reference to the cDelegate instance that’s passed to clsEvents, so it can pass its txtBox instance to the delegate class. In the form code below you can see where cDelegate is instantiated and where that instance is passed to each cEvent instance:

Private cEvents As clsEvents
Public WithEvents cDelegate As clsDelegate
Public collControls As Collection

Private Sub UserForm_Activate()
Dim ctl As MSForms.Control

Set cDelegate = New clsDelegate
Set collControls = New Collection
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.TextBox Then
Set cEvents = New clsEvents
Set cEvents.cDelegate = cDelegate
Set cEvents.txtBox = ctl
collControls.Add cEvents
End If
Next ctl
End Sub

Private Sub cDelegate_txtBoxChanged(txtBox As MSForms.TextBox)
Me.Label1.Caption = txtBox.Name & " text length is " & Len(txtBox.Text) & "." & vbCrLf & "Text is: " & vbCrLf & txtBox.Text
End Sub

The last sub in the code is the receiving event. It just updates information about whatever textbox was changed last.

textbox changes

Download!
Here’s a sample workbook with the form and code. Let me know what you think!

Chart SelectionChange Event

Posted on July 3, 2016 by Doug Glancy

I’m working on a pivot chart and pivot table navigator. It lets you click around between pivots and their associated charts. It consists of a small form with a list of the active workbook’s pivot tables. You use this list to navigate to pivots and their charts. When you choose a different pivot or chart in Excel itself I want the selected item in the list to change. I can use the SelectionChange event and the Range.PivotTable property to determine which pivot table has been selected. But SelectionChange doesn’t fire when you click into a chart. After messing about for a bit I’ve come up with a chart SelectionChange event that fires on chart selection and also includes all the stuff in a normal SelectionChange event.

The crux of the solution can be found in this excellent Jon Peltier post describing Chart events. It turns out charts are one of the Excel objects that supports WithEvents:

Excel withevents objects

This lets me create a tiny chart class that I can instantiate for each chart in all open workbooks.

Here’s the clsChart class:

'clsChart

Public WithEvents cht As Excel.Chart
Public cFullSelectionChange As clsFullSelectionChange

Private Sub cht_Activate()
Set cFullSelectionChange.Chart_Activated = cht
End Sub

It’s got the WithEvents declaration that allows us to capture chart events. In this case we’re capturing the event that occurs when a charge is activated. When that happens it pokes the Chart_Activated property in the clsFullSelectionChange class. This is the “parent” class that creates an instance of clsChart for each chart in the workbook.

FullSelectionChange Class That Includes Charts

Here’s the code for the cFullSelectionChange class:

'cFullSelectionChange

Private cChart As clsChart
Public WithEvents app As Excel.Application
Private collCharts As Collection
Public Event PivotSelected(pvt As Excel.PivotTable)
Public Event ChartSelected(cht As Excel.Chart)
Public Event OtherSelected()

Private Sub Class_Initialize()
Dim Wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim chtObject As Excel.ChartObject
Dim cht As Excel.Chart

Set app = Application
Set collCharts = New Collection
For Each Wb In Application.Workbooks
For Each cht In Wb.Charts
Set cChart = New clsChart
Set cChart.cFullSelectionChange = Me
Set cChart.cht = cht
collCharts.Add cChart
Next cht
For Each ws In Wb.Worksheets
For Each chtObject In ws.ChartObjects
Set cChart = New clsChart
Set cChart.cFullSelectionChange = Me
Set cChart.cht = chtObject.Chart
collCharts.Add cChart
Next chtObject
Next ws
Next Wb
End Sub

Public Property Set Chart_Activated(cht As Excel.Chart)
RaiseEvent ChartSelected(cht)
End Property

Private Sub app_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim ActivePivot As Excel.PivotTable

On Error Resume Next
Set ActivePivot = ActiveCell.PivotTable
On Error GoTo 0
If Not ActivePivot Is Nothing Then
RaiseEvent PivotSelected(ActivePivot)
Else
RaiseEvent OtherSelected
End If
End Sub

The last sub in the class, app_SheetSelectionChange, is a regular old application-level SheetSelectionChange event. It checks whether the ActiveCell is in a pivot table. Note that I could have used Selection just as easily as Activecell. I included this event in this class so that all the selection logic would be in one place, rather than putting it in the UserForm itself as I’d normally do. You’ll see below that this keeps the UserForm code quite simple.

The second-to-last sub is the Chart_Activated Property that gets poked by the individual chart classes when a chart is activated. It receives the activated chart object from the chart class.

Both these last two subs raise events that are hooked in the UserForm. The events are defined at the top of the module. They simply make the pivot table or chart available in the userform, much like an Sh or Target parameter in a built-in event. If you aren’t familiar with raising custom events the info at the end of Chip Pearson’s event page is very helpful.

The class’s Initialize routine creates instances of the chart class – one for every standalone or embedded chart in all open workbooks. If this isn’t familiar, take a look at the same Chip Pearson page linked above, specifically the Declaring WithEvents Variables section.

The UserForm
Because most of the work gets done in the two classes, the UserForm code is simple and powerful. The form looks like this, with textboxes to show either the selected chart or pivot table, if either:

Userform in action

UserForm Code

Private WithEvents cFullSelectionChange As clsFullSelectionChange

Private Sub UserForm_Initialize()
Set cFullSelectionChange = New clsFullSelectionChange
End Sub

Private Sub cFullSelectionChange_ChartSelected(cht As Chart)
Me.txtActiveChart.Text = cht.Name
Me.txtActivePivot.Text = ""
End Sub

Private Sub cFullSelectionChange_PivotSelected(pvt As PivotTable)
Me.txtActivePivot.Text = pvt.Name
Me.txtActiveChart.Text = ""
End Sub

Private Sub cFullSelectionChange_OtherSelected()
Me.txtActivePivot.Text = ""
Me.txtActiveChart.Text = ""
End Sub

The top of the UserForm module has the WithEvents declaration of the cFullSelectionChange class. It’s declared WithEvents because the class raises the pivot and chart activation events that we want to capture in this form. The form’s Initialize sub simply instantiates the
cFullSelectionChange class. Below that are the three event modules for pivot, chart or other selection.

Summary
The form instantiates the cFullSelectionChange class, which in turn instantiates a cChart class for each chart in every open workbook. Whenever a chart is activated its cChart class passes this action up to the cFullSelectionChange class, which raises the selection event in the UserForm.

Download
Here’s a sample workbook with the form and code. Let me know what you think!

Dynamically Resize Form Controls With Anchors

Posted on August 1, 2015 by Doug Glancy

Every once in a while I work on a VB.Net project. The coolest was building an interface connecting an ArcGIS front-end to a SQL Server backend… but that’s another story. One thing I always enjoy about Visual Studio, besides the post-1900s IDE, is the forms. They have many fine features, like rich textboxes you’re allowed to use, data-connected listviews and, perhaps my favorite, dynamically resizable controls. The resizing behavior is set using left, right, top and bottom “anchors.” You set the anchors right in the Properties dialog box:

So of course, I decided to create anchors for my VBA forms. And I think I’ve succeeded:

My Form With Anchored Controls

The form above has two frames, a listbox, three textboxes and two commandbuttons. Their moorings are shown in this table:

How Do Anchors Work?

If you use only one anchor in a pair, like only the Left anchor, then the control moves when the form is resized, maintaining the same distance between the control and the left edge of its parent container. Its size doesn’t change. If you choose both Left and Right anchors then the control grows or shrinks horizontally to fit the parent container. It’s kind of like having left-justified, right-justified or distributed text in a cell:

In this imperfect analogy, the words are the controls and the cells are the parent containers. The same concept applies to Top and Bottom anchors.

Coding the Anchors

I used to do this kind of thing piecemeal by relating the position of one control relative to its form or another control:

It works, but it’s cumbersome and requires the use of things like a WIDTH_PADDING constant, an indication that I don’t quite know what I’m doing.

It took a while to figure out the logic for handling all the form’s controls no matter where the are on the form, what types of anchors they have and whether they’re inside another control or not. At first my formulas still looked a lot like the code above, attempting to accommodate the borders around parent controls and such.

The secret I found is to just relate the anchors to the original height and width of their parent control, whether that parent is the form itself or a frame within the form. Then you can just apply the change in width or height of the parent to the position and size of the child control:

The code to do this is in a class which you instantiate and populate from the form:

Public Sub ResizeControls()
Dim i As Long

For i = LBound(m_ControlsAnchorsAndVals) To UBound(m_ControlsAnchorsAndVals)
With m_ControlsAnchorsAndVals(i)
If .AnchorTop And .AnchorBottom Then
.ctl.Top = .StartingTop
.ctl.Height = Application.WorksheetFunction.Max(0, .StartingHeight + _
(.ctl.Parent.InsideHeight - .ParentStartingHeight))
ElseIf .AnchorTop And Not .AnchorBottom Then
.ctl.Top = .StartingTop
ElseIf Not .AnchorTop And .AnchorBottom Then
.ctl.Top = .StartingTop + (.ctl.Parent.InsideHeight - .ParentStartingHeight)
End If
If .AnchorLeft And .AnchorRight Then
.ctl.Left = .StartingLeft
.ctl.Width = Application.WorksheetFunction.Max(0, .StartingWidth + _
(.ctl.Parent.InsideWidth - .ParentStartingWidth))
ElseIf .AnchorLeft And Not .AnchorRight Then
.ctl.Left = .StartingLeft
ElseIf Not .AnchorLeft And .AnchorRight Then
.ctl.Left = .StartingLeft + (.ctl.Parent.InsideWidth - .ParentStartingWidth)
End If
End With
Next i
End Sub

m_ControlsAnchorsAndVals is an array of types, one element for each control. The type specifies which anchors apply to that control, the control’s original dimensions and its parent’s original dimensions:

Private Type ControlAnchorsAndValues
ctl As MSForms.Control
AnchorTop As Boolean
AnchorLeft As Boolean
AnchorBottom As Boolean
AnchorRight As Boolean
StartingTop As Double
StartingLeft As Double
StartingHeight As Double
StartingWidth As Double
ParentStartingHeight As Double
ParentStartingWidth As Double
End Type

Here’s the Userform code that fills the array of Types, instantiates the class and assigns the eight controls and their anchors:

Private Sub UserForm_Activate()
'We know how many controls we're dealing with
Dim ControlsAndAnchors(1 To 8) As ControlAndAnchors

'Chip Pearson code
MakeFormResizable Me, True
ShowMinimizeButton Me, False
ShowMaximizeButton Me, False

With ControlsAndAnchors(1)
Set .ctl = Me.Frame1
.AnchorTop = True
.AnchorLeft = True
.AnchorBottom = True
.AnchorRight = True
End With
With ControlsAndAnchors(2)
Set .ctl = Me.Frame2
.AnchorTop = True
.AnchorBottom = True
.AnchorRight = True
End With

'... etc

With ControlsAndAnchors(8)
Set .ctl = Me.CommandButton2
.AnchorBottom = True
.AnchorRight = True
End With

Set cFormResizing = New clsFormResizing
cFormResizing.Initialize Me, ControlsAndAnchors
End Sub

Add a little Chip Pearson form resizing code and you’re good to go.

Some Important or Perhaps Interesting Stuff to Know if You Try This

It’s important to add the controls to the array in order of their hierarchy. If you resize a control before its parent is resized it won’t work.
The WithEvents userform object seems to lack a Resize event. It does have a Layout event, which occurs whenever the form or any control on it is moved or resized. I could have worked with that, but instead I call the class’s ResizeControls subroutine from the form’s Resize event.
This project makes use of Chip Pearson’s excellent API form code, which allows you to resize, and add maximize and minimize buttons to, a form.
After finishing this I did a search and found that Andy Pope (of course!) did something like it ten years ago. He uses an enum, which is always fun, and has some different features, like setting a minimum control size. Unless I’m mistaken though, his code relates the change in control size or position only to the overall form, not to the control’s parent container. This can lead to oddness if you have two side-by-side frames containing controls.

Download

This download contains the code and form. It also has a copy of the table shown above that has the anchors listed for each control. I tied the table to the code so you can change the values of the anchors, run the form, and see how it behaves.

Be careful, or you might get something like this:

UserForm Event Class – Number Selector

Posted on October 6, 2014 by Doug Glancy

In my world, when a form has a spinbutton, it’s got a textbox. Together they make a little thing I like to call a Number Selector. This handy control lets you pick digits by clicking the spinbutton, typing in the textbox, and even using the up and down keys. Whichever you do, the code keeps the textbox and spinbutton in sync. If you try to type anything non-numeric in the textbox it’s ignored. If you type a number outside the Min or Max of the spinbutton, it reverts to the minimum or the maximum. I find this all quite cool, and fun to code.

A Basic Number Selector

There’s lots of ways to combine a spinbutton and textbox into a number selector. Here’s a basic version of how I code it:

Private wsActive As Excel.Worksheet

Private Sub UserForm_Activate()
Set wsActive = ActiveSheet
Me.spnRowNum.Min = 1
Me.spnRowNum.Max = wsActive.Rows.Count
End Sub

Private Sub spnRowNum_Change()
Me.txtRowNum.Value = Me.spnRowNum.Value
End Sub

Private Sub txtRowNum_Change()
If IsNumeric(Me.txtRowNum.Value) Then
If Me.txtRowNum.Value < Me.spnRowNum.Min Then
Me.txtRowNum.Value = Me.spnRowNum.Min
ElseIf Me.txtRowNum.Value > Me.spnRowNum.Max Then
Me.txtRowNum.Value = Me.spnRowNum.Max
End If
Me.spnRowNum = Me.txtRowNum.Value
Else
Me.txtRowNum.Value = Me.spnRowNum.Value
End If
End Sub

Spinning the button or typing in the textbox selects a row between 1 and the sheet’s last row:

A Number Selector Class

Continuing the recent theme of of userform control classes, this post is about a number selector class. “Classifying” the control (sorry) eliminates a ton of duplicated code in your form, similar to what was done in the UserForm Event Handler Class – Multiple Controls post.

Besides the basic features discussed above, my number selector has:

Accelerator keys. Clicking the Shift, Ctrl and Alt keys increases the spinbutton’s SmallChange property. The accelators are cumulative and don’t care which of these keys were pressed, just how many. For example, if you set accelerators of 2, 3 and 5 and press Shift and Ctrl (or Alt and Ctrl), the increment is increased by a factor of six.
A Change event. You capture this event in the calling form, just like you would for Worksheet_Change and other built-in events. One limitation is these only fire for the first class instance in an array or collection of instances. Depending on what you’re doing that might not matter anyways, like in the form below.

Here’s the code for the class. Hopefully the comments get at the tricky stuff. I’ll say more about it below:

Private WithEvents spnSelector As MSForms.SpinButton
Private WithEvents txtSelector As MSForms.TextBox
Private m_Value As Long
Private m_Min As Long
Private m_Max As Long
Private m_Increment As Long
Private m_CurrentIncrement As Long
Private m_Accelerators(1 To 3) As Long
Private m_Executed As Boolean

'call this event in your userform
Event NumberSelectorChanged()

Public Sub Execute()
'call this code when all properties are set
If spnSelector Is Nothing Or txtSelector Is Nothing Then
Err.Raise 9998, , _
"Set the Group before other properties" & _
"and confirm that it contains a TextBox and and SpinButton"
End If
spnSelector.SmallChange = m_Increment
spnSelector.Min = m_Min
spnSelector.Max = m_Max
If m_Value < m_Min Or m_Value > m_Max Then
m_Value = m_Min
End If
spnSelector.Value = m_Value
txtSelector.Value = m_Value
m_Executed = True
End Sub

Public Property Let Group(grp As MSForms.Frame)
'Note: this property must be set first
'It gets the frame on the UserForm that contains the spinbutton and textbox
Dim ctl As MSForms.Control

For Each ctl In grp.Controls
If TypeOf ctl Is MSForms.SpinButton Then
Set spnSelector = ctl
ElseIf TypeOf ctl Is MSForms.TextBox Then
Set txtSelector = ctl
End If
Next ctl
End Property

Public Property Let Value(PassedValue As Long)
m_Value = PassedValue
'don't want to reference controls until Execute sub run
'or you'll get a runtime error
If m_Executed Then
RaiseEvent NumberSelectorChanged
spnSelector.Value = m_Value
txtSelector.Value = m_Value
End If
End Property

Public Property Get Value() As Long
Value = m_Value
End Property

Public Property Let Min(PassedMin As Long)
m_Min = PassedMin
End Property

Public Property Let Max(PassedMax As Long)
m_Max = PassedMax
End Property

Public Property Let Increment(Optional acc1 As Long = 1, Optional acc2 As Long = 1, Optional acc3 As Long = 1, PassedIncrement As Long)
'properties can have parameters, so this property includes the accelators,
'along with the Increment (SmallChange).
'PassedIncrement is the only required one
m_Increment = PassedIncrement
m_CurrentIncrement = m_Increment
m_Accelerators(1) = acc1
m_Accelerators(2) = acc2
m_Accelerators(3) = acc3
End Property

'Acc1, etc., included to match the Let definition, otherwise won't compile
Property Get Increment(Optional acc1 As Long = 1, Optional acc2 As Long = 1, Optional acc3 As Long = 1) As Long
Increment = m_CurrentIncrement
End Property

Private Sub spnSelector_Change()
Me.Value = spnSelector.Value
End Sub

Private Sub txtselector_Change()
With txtSelector
Select Case .Value
'Allow a single negative sign or an empty string,
'but no processing required
Case "-", ""
Exit Sub
End Select
'whole numbers only
If IsNumeric(.Value) And InStr(.Value, ".") = 0 Then
If .Value < m_Min Then
.Value = m_Min
ElseIf .Value > m_Max Then
.Value = m_Max
End If
Me.Value = CStr(.Value)
Else
.Value = m_Value
End If
End With
Me.Value = txtSelector.Value
End Sub

'The next four routines capture accelerators (Shift, Ctrl, Alt)
'if pressed while textbox or spinbutton is active

Private Sub spnSelector_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
SetCurrentIncrement Shift
End Sub

Private Sub spnSelector_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
SetCurrentIncrement Shift
End Sub

Private Sub txtSelector_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyDown Then
KeyCode = vbNull
txtSelector.Value = txtSelector.Value - m_CurrentIncrement
ElseIf KeyCode = vbKeyUp Then
KeyCode = vbNull
txtSelector.Value = txtSelector.Value + m_CurrentIncrement
End If
SetCurrentIncrement Shift
End Sub

Private Sub txtSelector_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
SetCurrentIncrement Shift
End Sub

Private Sub SetCurrentIncrement(ByVal ShiftMask As Integer)
'Uses bitwise AND's against the Shift enum to determine if the
'Shift, Ctrl or Alt keys were pressed, and
'multiplies the m_Increment by the corresponding accelerator
'accelerators are cumulative

Dim IncrementLevel As Long
IncrementLevel = _
(((ShiftMask And 1) = 1) * -1) + _
(((ShiftMask And 2) = 2) * -1) + _
(((ShiftMask And 4) = 4) * -1)

m_CurrentIncrement = m_Increment * _
IIf(IncrementLevel >= 1, m_Accelerators(1), 1) * _
IIf(IncrementLevel >= 2, m_Accelerators(2), 1) * _
IIf(IncrementLevel >= 3, m_Accelerators(3), 1)
spnSelector.SmallChange = m_CurrentIncrement
End Sub

Because this class has quite a few properties, the logic for the order in which they must be assigned is a bit clunky. For instance, if you try to assign the Value, Min or Max before you’ve passed the controls to the class, it won’t work. So I created an Execute method – a Sub within the query – that assigns the values from the class-level private variables to the spinbutton and textbox.

There’s also some Err.Raise code try to deal with bad initialization. But I don’t try to handle form-specific issues – I want it to be portable – so if you assign a min of zero and use it to pick row numbers, you’ll get a runtime error.

As mentioned in the comments, the Increment property has three accelerator parameters. I’d never used parameters with a property before. You have to list the “real” one last, and the preceding ones can be optional. If you have a matching Get property, it must include the same parameters, or you’ll get a compile error.

I also added some KeyDown logic for the textbox part of the control. Normally, if you’re in a textbox the down arrow takes you to the next control. I wanted it to act the same as the up and down keys do when the spinbutton is active, so I cancel the KeyDown event if the down or up arrows are pressed, and instead add or subtract the correct amount to the value in the textbox.

A Form With Three Class Instances

The UserForm for this utilization of the class has three number selectors. The first selects rows, the next selects columns. The third increments the seconds from midnight last night. Each number selector is framed by a … frame. They’re named grpRowSelector, grpColSelector and grpSecondsFromNow respectively (I think of them as Groups, hence the “grp”). It doesn’t matter what the spinbutton and textbox are called, as the class just checks for those controls inside the frame, which is passed to the class in UserForm_Activate.

You can see what I mean about simple code in the form. There’s just the initialization of the classes and the Change event for each one> Note that the this number selector has accelerators of 60, 60 and 24, so you can increment by seconds, minutes, hours or days:

Private WithEvents cRowSelector As clsNumberSelector
Private WithEvents cColumnSelector As clsNumberSelector
Private WithEvents cSecondsFromNow As clsNumberSelector

Private Sub UserForm_Activate()
Dim MaxSeconds As Long

Set cRowSelector = New clsNumberSelector
With cRowSelector
.Group = Me.grpRowSelector
.Min = 1
.Max = ActiveSheet.Rows.Count
.Value = 1
‘properties can have parameters
.Increment(acc1:=5, acc2:=10, acc3:=100) = 1
‘Needed to confirm that properties entered correctly
.Execute
End With

‘see comments for class instance above
Set cColumnSelector = New clsNumberSelector
With cColumnSelector
.Group = Me.grpColumnSelector
.Min = 1
.Max = ActiveSheet.Columns.Count
.Value = 1
.Increment(2, 5, 10) = 1
.Execute
End With

‘see comments for class instance above
Set cSecondsFromNow = New clsNumberSelector
With cSecondsFromNow
MaxSeconds = (10# * 24 * 60 * 60) ‘# to make it a Long
.Group = Me.grpSecondsFromNow
.Min = -MaxSeconds
.Max = MaxSeconds
.Value = 0
.Increment(60, 60, 24) = 1
.Execute
End With
End Sub

‘custom events raised in clsNumberSelector
Private Sub cRowSelector_NumberSelectorChanged()
ActiveSheet.Rows(cRowSelector.Value).Select
Me.txtIncrementValue = cRowSelector.Increment
End Sub

Private Sub cColumnSelector_NumberSelectorChanged()
ActiveSheet.Columns(cColumnSelector.Value).Select
Me.txtIncrementValue = cColumnSelector.Increment
End Sub

Private Sub cSecondsFromNow_NumberSelectorChanged()
Me.txtDateAndTime.Value = Format(Date + cSecondsFromNow.Value / (24# * 60 * 60), “yyyy-mm-dd hh:mm:ss”)
Me.txtIncrementValue = cSecondsFromNow.Increment
End Sub

Download

What you’d like to try this code, but don’t feel like copying and pasting and dragging controls around? I understand. Check this out.

P.S. If you want to read a great post about class properties, you can do no better than this one by Tushar Mehta on DDOE.

UserForm Event Class – Validating Controls

Posted on September 9, 2014 by Doug Glancy

At the end of my last post I said I’d be back with an actual use for a multiple-control event class. And here I am! It seems to me that validating controls in a userform is a very good use for this type of class. Not having the validation mixed in could really unclutter the main form and clarify things. So let’s look at a real-life application that I’ve just updated to this kind of control validation. It’s probably full of bugs, but what the heck.

The application is an addin for splitting worksheets into separate workbooks based on one or two columns. The process starts with a form where users choose the row with the headers and the column(s) to split on. The userform opens with the active row selected and the OK button disabled. When users select a primary column the OK button is activated. If they clear the row textbox, pick a secondary column without a primary column, or pick the same column for both, the OK button is disabled and an error message shows. This functionality is all handled in a multiple-control WithEvents class:

As you can see, there’s a textbox at the bottom to display the validation error message. In all three cases, in addition to showing the message, the OK button is disabled. My hope is that this provides a user-friendly experience: guiding them and letting them try different things without wasting their time pushing “OK” when it’s really not.

The class with the validation looks a lot like the multiple-control class from the last post. Just like there, every control’s click or change event calls the same procedure. In this case it’s a validation procedure, called “CheckReadyState.” It runs a few tests to make sure the various controls are in harmony. If so, the form’s “OK” button is enabled. If not, it’s disabled and a validation error message is shown. Here’s the whole class:

Private WithEvents txt As MSForms.TextBox
Private WithEvents lst As MSForms.ListBox
Private WithEvents chk As MSForms.CheckBox
Private WithEvents spn As MSForms.SpinButton
Private m_PassedControl As MSForms.Control
Private m_ParentForm As MSForms.UserForm

Property Set ctl(PassedControl As MSForms.Control)
Set m_PassedControl = PassedControl
Select Case True
Case TypeOf PassedControl Is MSForms.TextBox
Set txt = PassedControl
Case TypeOf PassedControl Is MSForms.ListBox
Set lst = PassedControl
Case TypeOf PassedControl Is MSForms.CheckBox
Set chk = PassedControl
Case TypeOf PassedControl Is MSForms.SpinButton
Set spn = PassedControl
End Select

Set m_ParentForm = GetParentForm(PassedControl)
End Property

Private Sub chk_Click()
CheckReadyState
End Sub

Private Sub lst_Change()
CheckReadyState
End Sub

Private Sub spn_Change()
CheckReadyState
End Sub

Private Sub txt_Change()
CheckReadyState
End Sub

Private Sub CheckReadyState()
Dim HelpMessage As String
Dim ReadyState As Boolean

ReadyState = True
With m_ParentForm
If .txtHeaderRowNum = "" Then
HelpMessage = "Pick a Header Row"
ReadyState = False
ElseIf .lstChooser2.ListIndex <> -1 And .lstChooser1.ListIndex = -1 Then
HelpMessage = "No Primary Column Picked"
ReadyState = False
ElseIf (.lstChooser1.ListIndex = .lstChooser2.ListIndex) And .lstChooser1.ListIndex >= 0 Then
HelpMessage = "Duplicate Columns"
ReadyState = False
Else
'If we got this far then there's no validation errors,
'so if they've selected at least a primary column we're ready for the OK button
ReadyState = .lstChooser1.ListIndex <> -1
End If
.cmdOk.Enabled = ReadyState
SetHelpMessage HelpMessage
End With
End Sub

Function GetParentForm(ctl As MSForms.Control) As MSForms.UserForm
Dim ParentForm As Object

Set ParentForm = ctl.Parent
Do Until TypeOf ParentForm Is MSForms.UserForm And Not TypeOf ParentForm Is MSForms.Frame
Set ParentForm = ParentForm.Parent
Loop
Set GetParentForm = ParentForm
End Function

Sub SetHelpMessage(HelpMessage As String)
m_ParentForm.lblError.Caption = HelpMessage
End Sub

The basic philosophy here is that a change to any control on the form, or, more accurately, any control having a WithEvents type in the public declarations of this class, triggers the validation routine. It doesn’t matter which control triggers it, the same things are validated. That distinguishes this class’s function, I hope, from the type of checking that syncs the spinbutton/textbox combo, for example what to do if the user tries to spin below zero. It also distinguishes the class functions from what happens when the OK button is clicked. Both those are handled within the userform itself.

Of course, the class does refer to individual controls on the userform as part of the validation, such as checking whether the same column was selected for both listboxes. Note that you can refer to the controls with something like “ParentForm.lstChooser1” although there’s no IntelliSense for them.

I also messed around with a different way to refer to the parent form within the class. As Ross commented on an older post, passing the form to the class seems kind of clunky. I came up with a new clunky way in the “GetParentForm” function. It starts with the calling control and recursively checks parent controls until it climbs to the userform level. As mentioned in my last post, frames are seen as both frame and userform controls when using TypeOf, hence the double check in the “Do Until” line. I could have just checked the control’s TypeName, as mentioned in the last post, but with a form TypeName doesn’t return “UserForm.” It returns “frmWorksheetSplitter” or whatever you called it.

This is all kind of experimental on my part, and I’m sure parts of it could be made more object-oriented or otherwise improved, so let me know. I do like having the validation off in it’s own world, instead of mixing it in with the rest of the form code.

The userform is similar to the one in A Flexible VBA Chooser Form. To see how it works, and how I populate the listboxes, and any number of wondrous things, download the workbook.

UserForm Event Class – Multiple Control Types

Posted on August 31, 2014 by Doug Glancy

I’ve been fooling around with UserForms lately and have a couple of posts in mind. This one describes a UserForm event class that handles more than one type of form control.

There’s good explanations on the web for creating arrays of userform controls that handle all the events for a certain type of control, like a TextBox. That way, for example, you don’t have to duplicate the click event for each TextBox. In this post I create a single event-handler class for multiple types of controls: CheckBoxes, ComboBoxes, OptionButtons, and the like. This way you don’t need to create a separate class for each type of control – one class can handle them all.

(If the idea of an array of event-handler classes is new to you, please click the link above. John Walkenbach has a nice example too. The very brief explanation is that you create an array of classes, one for each control, which then handle whatever control events you specify in the class. Note that I use a collection instead of an array. That’s just how I learned it.)

The class in this example, called “clsMultipleControls,” handles the click or change events for CheckBoxes, ComboBoxes, ListBoxes, OptionButton, SpinButtons and TextBoxes. It has one property, called “PassedControl,” with an associated class-level variable, “m_PassedControl.” When m_Passed_Control is set, the code determines its type and assigns it to the appropriate WithEvents control. So, if the passed control is a TextBox the “txt” variable is set to m_Passed_Control. The last routine just prints the control’s name, as a stand-in for the real work that your class could do.

Private m_PassedControl As MSForms.Control
Private WithEvents chk As MSForms.CheckBox
Private WithEvents cbo As MSForms.ComboBox
Private WithEvents lst As MSForms.ListBox
Private WithEvents opt As MSForms.OptionButton
Private WithEvents spn As MSForms.SpinButton
Private WithEvents txt As MSForms.TextBox

Property Set ctl(PassedControl As MSForms.Control)
Set m_PassedControl = PassedControl

Select Case TypeName(PassedControl)
Case "CheckBox"
Set chk = PassedControl
Case "ComboBox"
Set cbo = PassedControl
Case "ListBox"
Set lst = PassedControl
Case "OptionButton"
Set opt = PassedControl
Case "SpinButton"
Set spn = PassedControl
Case "TextBox"
Set txt = PassedControl
End Select
End Property

Private Sub cbo_Change()
PrintControlName
End Sub

Private Sub chk_Click()
PrintControlName
End Sub

Private Sub lst_Change()
PrintControlName
End Sub

Private Sub opt_Click()
PrintControlName
End Sub

Private Sub spn_Change()
PrintControlName
End Sub

Private Sub txt_Change()
PrintControlName
End Sub

Sub PrintControlName()
Debug.Print m_PassedControl.Name
End Sub

The event routines above are just a sample. You can use as many events as are supported by that type of control. For example, a ComboBox supports the click, DropButtonClick, KeyUp and Mousedown events, among others. One limitation is that WithEvents controls don’t support Exit and Enter (and some other) events, as those are actually generated at the Userform level. To see which events are supported by a certain type of control, use the two dropdowns at the top of the class module:

One thing to note above is that I used the “TypeName” function rather than something like

TypeOf ctl Is MSForms.ComboBox

. One reason is that you can’t seem to use TypeOf in a Select Case statement. The second is that some controls return True for multiple types. For example, the OptionButton passes both “Is MSForms.OptionButton” and “Is MSForms.CheckBox.” I assume this is because the OptionButton is based on the Checkbox.

Here’s the initialization code in the UserForm. As promised, it’s very simple.

Public collControls As Collection
Private cMultipleControls As clsMultipleControls

Private Sub UserForm_Activate()
Dim ctl As MSForms.Control

Set collControls = New Collection
For Each ctl In Me.Controls
Set cMultipleControls = New clsMultipleControls
Set cMultipleControls.ctl = ctl
collControls.Add cMultipleControls
Next ctl
End Sub

This code establishes a collection of instances of clsMultipleControls, one for each control in the form. Each class instance has an instantiated m_Passed_Control variable, and no more than one instantiated WithEvents control. I say “no more than one” because with this setup, although a CommandButton would generate a class, there’s no WithEvents CommandButton variable, so it wouldn’t be identified in the class’s

Set ctl

subroutine.

I suppose this might be seen as inefficient, stuffing all these possibilities into a single class. But I like the way it simplifies the form coding and the flexibility of the class. What do you think?

In the next post, I’ll expand this class a bit and demonstrate what I think is a nice use for this type of class. I’ll also show a situation where TypeOf is required (I think), the problem that creates and the solution I came up with.

Meanwhile, here’s a downloadable workbook with the UserForm and class.

UserForm Event Handler Class – Multiple Controls

Posted on October 14, 2012 by Doug Glancy

Down through the ages, VBA programmers have asked, “Do I really need a click event handler for each button on my form, even if they all do the same thing?” The answer, of course, is “no.” You can use a class to create an array of event handlers for the controls. In this post, I’ll expand on that concept to groups of checkboxes that work together in a “group/member” relationship.

I’ve been working on a form with groups of checkboxes that perform pretty much the same action. All the checkboxes in a row are controlled by a “group” switch. Conversely, the group switch turns on or off, or goes to that grayed-out “Null” position, based on the state of its “member” checkboxes. Just like this worksheet header/footer preview form. In this case the group switches are the “Headers” and “Footers” checkboxes, with the member checkboxes to the right.

This form uses a collection of classes, one for each of the six member controls. Each class instance contains that single member control, along with a collection of all the member controls in the same row, and the row’s group checkbox. The class contains two event handlers: one for the member checkbox, and one for the group checkbox. To be able to create the event handlers, these two controls are declared using the WithEvents keyword.

The class looks like this:

'clsHeadFooterCheckboxes

Public WithEvents GroupCheckbox As MSForms.CheckBox
Public WithEvents MemberCheckbox As MSForms.CheckBox
Public collmemberCheckboxes As Collection
Public ParentForm As MSForms.UserForm

Private Sub MemberCheckbox_Click()
Dim ctl As MSForms.Control
Dim CheckedCheckboxCount As Long

'Avoid endless control click loops
If MemberCheckbox.Enabled Then
'count the number of checked "member" controls
For Each ctl In collMemberCheckboxes
If ctl = True Then
CheckedCheckboxCount = CheckedCheckboxCount + 1
End If
Next ctl

With GroupCheckbox
'Also avoid endless control click loops
.Enabled = False
'set the state of the group based on whether
'all, no, or some members are checked
.TripleState = False
Select Case CheckedCheckboxCount
Case 0
.Value = False
Case collmemberCheckboxes.Count
.Value = True
Case Else
.TripleState = True
.Value = Null
End Select
.Enabled = True
End With
End If
SetTextBoxVisibility

End Sub

Private Sub GroupCheckbox_Click()
Dim ctl As MSForms.Control

'turn members on or off depending on group state
With GroupCheckbox
'TripleState is only true when set by members
'We don't want it to be available when clicking group
.TripleState = False
For Each ctl In collmemberCheckboxes
'Avoid endless control click loops
ctl.Enabled = False
ctl.Value = .Value
ctl.Enabled = True
Next ctl
End With
SetTextBoxVisibility

End Sub

Sub SetTextBoxVisibility()
'Set the textboxes paired to the member controls visibility
ParentForm.Controls(Replace(memberCheckbox.Name, "chk", "txt")).Visible = memberCheckbox.Value
End Sub

While writing this code, I solved a problem that stumped me in the past: how to avoid looping of events when a pair of controls each triggers a change in the other. Application.EnableEvents doesn’t apply to userform controls, so you typically create some kind of EventsEnabled boolean variable. This is easy enough when only one control has a change event, but I’ve never been able to get it to work when two controls are affected by each other’s Change or Click events. This project was even more confusing, because events are triggered in three separate class instances, one for each member control in a row!

My solution was inspired by recent experience with VB.Net, where you can simply add and remove event handlers within your code. If you don’t want to trigger events, just unlink the control from its event handler, and add it back when you’re done. Obviously you can’t do that in VBA, but I realized I could disable a control before performing an action that would normally trigger its event. I did this in the MemberCheckbox_Click event. In the other direction it’s a little different. In the GroupCheckbox_Click event I disable the member checkbox and then check its state in the MemberCheckbox_Click event. This acts like an across-all-class-instances global variable that is tested in the groupCheckbox_Click event. I think. At any rate, it works.

Another tricky part was managing the group checkbox’s TripleState property. It only gets turned on in the MemberCheckbox_Click event, and only when some, but not all, of the member checkboxes are checked. This allows us to show a “grayed out” group checkbox. TripleState gets turned back off in the group checkbox’s click event, so when you are clicking it the only possibilities are checked or not checked.

This class is pretty flexible. You can add rows, or checkboxes within rows, and it works correctly. Just be sure to add the controls within the appropriate group and follow the naming pattern of the existing controls.

The userform code looks like this:

Private cHeadFooterCheckboxes As New clsHeadFooterCheckboxes
Private collCheckBoxClasses As Collection
Private WithEvents ThisBook As Excel.Workbook

Private Sub UserForm_Initialize()

Set ThisBook = ThisWorkbook
InitializeClasses
SetWorksheetCombo
SetDisplayTextBoxes
End Sub

Sub InitializeClasses()
Dim ctl As MSForms.Control
Dim RowName As String

Set collCheckBoxClasses = New Collection
'For each group control
For Each ctl In Me.grpgroupControls.Controls
RowName = Replace(ctl.Name, "chkAll", "")
InitializeRowClasses RowName
Next ctl
End Sub

Sub InitializeRowClasses(RowType As String)
Dim collRowmembers As Collection
Dim ctl As MSForms.Control

Set collRowmembers = New Collection
For Each ctl In Me.grpmemberControls.Controls
'If it's a checkbox in the row being processed
If InStr(ctl.Name, RowType) > 0 Then
collRowmembers.Add ctl, ctl.Name
End If
Next
'Create a class for each member control in the row
For Each ctl In collRowmembers
Set cHeadFooterCheckboxes = New clsHeadFooterCheckboxes
'initialize the class with the
'control, other members and group
With cHeadFooterCheckboxes
Set .memberCheckbox = ctl
Set .groupCheckbox = Me.Controls("chkAll" & RowType)
Set .collmemberCheckboxes = collRowmembers
Set .ParentForm = Me
End With
'add the class to the collection
collCheckBoxClasses.Add cHeadFooterCheckboxes
Next ctl
End Sub

End Sub

Private Sub cboWorksheets_Change()
If Me.cboWorksheets.Enabled Then
SetDisplayTextBoxes
End If
End Sub

Private Sub ThisBook_SheetActivate(ByVal Sh As Object)
SetWorksheetCombo
End Sub

There’s other code in the userform that handles the worksheet combobox. You can download a sample workbook to see it all in action.