Towards the end of the last post I showed a form I made to copy query properties from one table to another. That userform has 20 controls and, as always before I post something for your enjoyment, I tried to make is as user-friendly as possible.
So I set the default and cancel properties to the appropriate buttons and arrange the tab order of all the controls. When that’s done I assign accelerator keys to some of the controls. The accelerator property specifies a letter or other key, which when pressed along with the Alt key, activates that control.
In the example above, a helpful but peevish developer has specified the % key as the accelerator, perhaps for the first time in history.
I enjoy the fiddly work of setting tab orders and accelerators. I’d enjoy it more if the tab order dialog weren’t so hard to read.
It’s also hard to tell which controls have which accelerators and whether you’ve already used a certain letter.
So I wrote a bit of code that takes a userform as an argument and prints the relevant control properties to a newly-minted worksheet.
Dim ControlsCount As Long
Dim i As Long
Dim ctl As msforms.Control
Dim ControlName As String
Dim ControlTabIndex As Long
Dim ControlCaption As String
Dim ControlAccelator As String
Dim ControlProperties() As Variant
Dim ws As Excel.Worksheet
Const TableHeaders As String = "TabIndex,Name,Caption,Accerator,Count"
ControlsCount = frm.Controls.Count
ReDim ControlProperties(1 To ControlsCount, 1 To 4)
For i = 1 To ControlsCount
Set ctl = frm.Controls(i - 1)
ControlName = ctl.Name
ControlTabIndex = ctl.TabIndex
ControlCaption = ""
ControlAccelator = ""
'some controls don't have the next two properties
On Error Resume Next
ControlCaption = ctl.Caption
ControlAccelator = ctl.Accelerator
On Error GoTo 0
ControlProperties(i, 1) = ControlTabIndex
ControlProperties(i, 2) = ControlName
ControlProperties(i, 3) = ControlCaption
ControlProperties(i, 4) = ControlAccelator
Next i
Set ws = Workbooks.Add.Worksheets(1)
With ws
.Range("A1:E1") = Split(TableHeaders, ",")
.Range("A2").Resize(ControlsCount, 4) = ControlProperties
With .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes)
.Name = "tblControlProperties"
.ListColumns("Count").DataBodyRange.FormulaR1C1 = "=COUNTIF([Accerator],[@Accerator])"
With .Sort
.SortFields.Add Key:=Range("tblControlProperties[TabIndex]")
.Header = xlYes
.Apply
End With
.Range.Columns.AutoFit
End With
'Want to close without prompt to save
.Parent.Saved = True
End With
End Sub
The code loops through a form’s controls and ultimately adds them all to an array which is dumped into a worksheet created in the code.
You’d call it like this:
ListUserFormAccelerators frmCopyTableQuery
End Sub
And the result looks like this (click on it to open in its own, larger, window):
You may notice that some of the accelerators are doubled above. Each pair is for a label, followed by a textbox with no accelerator and then by a checkbox, which has the second occurrence of that acceelator. There’s two things going on here. The first is that if a control isn’t a Tab Stop, like a label, then the accelerator will take you to the next control, in this case the textbox. The second is that I wanted the user to be taken to the associated checkbox if they hit the accelerator again.
I can’t figure out how to call this code from another project. I messed around with Application.VBE.VBProjects, but can’t get it to work. Another nice thing would be the inverse of this code, a routine that would apply the tab order and accelerators from the worksheet to the userform.