Events 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 – 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.

SheetActivate Event Doesn’t Fire Between Multiple Windows

Posted on March 30, 2014 by Doug Glancy

In VBA you use the SheetActivate event to track when a user switches from one sheet to another. Sometimes I use it to control the state of menu items that I only want available when certain sheets are active. In the workbook below, I only want the “Add Color” button enabled when the “Colors” sheet is active.

The SheetActivate event works fine for this, most of the time, using code similar to this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'forces the button's getEnabled code (below) to run
g_Ribbon_Invalidate
End Sub

Public Sub cmdAddColor_getEnabled(control As IRibbonControl, ByRef returnedVal)
'the button is enabled only if "Colors" is the active sheet
returnedVal = ActiveSheet Is ThisWorkbook.Worksheets("Colors")
End Sub

Below, the Add Color button has been disabled after switching to another sheet, just like I want:

Recently I noticed this doesn’t necessarily work if the workbook has two or more windows. In that case, switching from one window to another doesn’t trigger the SheetActivate event, even if the second window has a different active sheet than the first. Below, I’ve switched from the “Colors” sheet in one window to the “No Colors Allowed!” sheet in the second window. The SheetActivate event hasn’t fired and the button is still enabled. It’s out of sync.

I guess it makes sense that SheetActivate wouldn’t fire. After all, within each window the active sheet is still the same. (Happily, the ActiveSheet property is still updated.)

In order to keep the button in sync, add a WindowActivate event to your code. Between it and the SheetActivate code, you’ll handle moves between sheets within the same window, and between windows to a different sheet:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
g_Ribbon_Invalidate
End Sub

Download!
You can check it out in this sample workbook.

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.

Userform Application-Level Events

Posted on August 10, 2012 by Doug Glancy

I’ve been fooling around with workbook and application-level events in UserForms. I’ve put them to good use in one or two projects, so thought I’d post about it. While thinking about the best way to present it, I ended up with a form that allows you to track all application-level events in an Excel instance. EventTracker looks like this (click the pic to for bigger one):

Warning: meandering post ahead

Before we get to that though, here’s how you can create a simple form that responds to all SheetSelectionChange events. First add a userform to a workbook in the Visual Basic Editor, then add a textbox to it. Paste this code into the form’s code module:

Private WithEvents app As Excel.Application

Private Sub UserForm_Initialize()
Set app = Application
End Sub

Private Sub app_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Me.TextBox1 = Target.Address(external:=True)
End Sub

The code creates a WithEvents Application variable which is used to track application-level events. In this case, we’ll track the SheetSelectionChange event, but you can select other ones by selecting “app” in the dropdown at the top left of the VBA editor and the event in the dropdown at the top right.

Before you run the form you need to set its ShowModal property to False (or there will be no application-level events to track). Or you could just add this code to the code above:

Private Sub UserForm_Activate()
Static Activating As Boolean
If Activating = False Then
Activating = True
Me.Hide
Me.Show vbModeless
End If
End Sub

I didn’t know the above would work until I tried it here. I don’t recommend it for any kind of serious coding, because of the static variable, but I like it. Basically the code only runs the first time UserForm_Activate runs. It hides the form and then shows it again modally, obviating the need to set the ShowModal property in design time or open the form from another module.

Now run the form and you’ll see that every SelectionChange event is reflected in Textbox1.

So, back to the EventTracker form. It has two listboxes, one that shows all the available events to track, and one that lists the events that have occurred. You can set the events to track to all or none, or just some. The listbox is set to MultiSelectExtended, which means you can use Shift and Control to select ranges. I also added code so that Ctrl-A selects the whole list. Plus there’s option buttons!

The listbox that tracks events gets resized as they are added. (The form is resizable as well.) It shows the event and its parameters, such as the name of the worksheet being activated. I changed the parameters slightly, separating out the “Target” argument for the SheetPivotTableUpdate event into a “Pivot” column. The code that adds a row to the recent events uses a paramarray, which is the first time I’ve ever used one.

Here are some of the things I learned while making, and running, the form:

1. Even when the “After pressing enter, move selection” option is off, the SheetSelectionChange event fires after you edit a cell and hit Enter. Who knew?

2. There are a bunch of new events in Excel 2010. I only included “WorkbookAfterSave,” “WorkbookNewChart” and “SheetPivotTableAfterValueChange” in this tool. The rest have to do with Pivot table OLAP cubes.

3. There’s still no BeforeReallyClosing event though.

4. The maximum number of items allowed in a listbox is subject to available resources. On my laptop that equated to 6,242,685, so I didn’t need code to handle overfilling the event tracking listbox.

5. I discovered Chip Pearson’s excellent form resizing API code and used it to set the form with a minimize button and the ability to be resized.

One bit of code that’s useful on its own resizes column widths in a listbox. It’s based on this Daily Dose of Excel post, which in turn was based on a very thorough post by Jan Karel Pieterse. I modified Dick’s code to include the headers in the resizing, and also to base the the resizing only on the listbox’s visible rows:

Private Sub SetLstRecentEventsColumnWidths()

'Uses a hidden label in the form to hold
'text from headers and visible rows and
'resize to the widest one for each column

Dim ColWidths As String
Dim MaxWidth As Double
Dim i As Long
Dim j As Long
Dim VisibleRowsCount As Long
Dim HeaderLabels As Collection
Dim HeaderLabelWidths As Double

'create the HeaderLabels collection
Set HeaderLabels = GetHeaderLabels
With Me.lstRecentEvents
'skip the code if no rows yet
If .TopIndex > -1 Then
VisibleRowsCount = Application.WorksheetFunction.Min((.ListCount - .TopIndex) - 1, .Height / lblHidden.Height)
For i = 0 To .ColumnCount - 1
'first get the header label width
Me.lblHidden.Caption = HeaderLabels(i + 1) & "MM"
MaxWidth = Application.WorksheetFunction.Max(Me.lblHidden.Width, MaxWidth)
'only want to resize for the visible rows
For j = .TopIndex To .TopIndex + VisibleRowsCount
Me.lblHidden.Caption = .Column(i, j) & "MM"
MaxWidth = Application.WorksheetFunction.Max(Me.lblHidden.Width, MaxWidth)
Next j
ColWidths = ColWidths & CLng(MaxWidth + 1) & ";"
HeaderLabels(i + 1).Left = HeaderLabels(1).Left + HeaderLabelWidths
HeaderLabels(i + 1).Width = MaxWidth
HeaderLabelWidths = HeaderLabelWidths + CLng(MaxWidth + 1)
MaxWidth = 0
Next i
.ColumnWidths = ColWidths
End If
End With
End Sub

Back to the EventTracker form again. As my father might say “The road is the destination.” Or is it “kindling is fire…?”

The downloadable zip file has a grid showing the events handled and a handy button to start Event Tracker. The workbook is .xlsm format, because if it was saved in .xls then the more recent events wouldn’t be available. You can still run it in XL 2003 as long as you’ve installed the compatibility pack. When running in an earlier version than 2010, events not supported in that version show up in the form module under “General,” rather than under “app.”