[?] How to Clone Controls and their Event Code? [?] How to Return Name of Control to which Event Code refers?

[Default300]

Hi. To jump to specific queries re my current issue, see “Objective” and “Questions” headers below.

OVERALL AIM

I have coded a batch print operation for Excel.
It works fine, but now I want to display the list of pending print jobs to the user, and allow them to cancel individual jobs before proceeding.

I want to do this using:
a UserForm that has the appearance of a table;
with multiple rows of data, each one referring to a single job;
with each row / record / job having a unique 3 digit index;
with 7 columns / fields of read only data;
and with cancel / proceed button on each row.

PROGRESS SO FAR

I have created a UserForm with a single row of controls.
The read only data is displayed via 7 Label controls.

Cells in each column will be populated with data from a 2D array (held in a Variant variable).
The array is 1 based in both dimensions. (This seems to happen when populated directly from a Range.)
The array was originally populated from the values of a spreadsheet table.

There are 2 transparent OptionButtons at the end of each row.
There are 2 more Label controls beneath (in Z axis) the OptionButtons, which act as OptionButton captions.

The only active Controls are the 2 OptionButtons.
The Click Event of the “cancel” OptionButton triggers reformatting the row, highlighting rows / jobs which will be cancelled.
The Click Event of the “proceed” OptionButton reformatting the row again to the original appearance.
Values of the “cancel” OptionButtons will be examined when the batch print operation runs.

Controls are named as follows to make it easy to refer to them via the array indices or loop variables:

The 2 digit number that follows the C is the column number of the control.
The 3 digit number that follows the R is the row number of the control. (It will probably be the same as the Array Index, except for the padded zeroes.)
It’s at the end to make it easy to concatenate by appending the index to the root name.

lblDataC01R001
lblDataC02R001
lblDataC03R001
lblDataC04R001
lblDataC05R001
lblDataC06R001
lblDataC07R001
lblCancelC08R001
lblCancelC09R001
optCancelC08R001
optCancelC09R001

The GroupName of 2 OptionButtons in row 001 is grpOptionGroup001


OBJECTIVE

I would like to dynamically replicate this row (ie controls and associated code) as many times as there are “rows” in the first dimension of the array.

Currently, the maximum number of rows required is 51, but it could potentially increase to say 200.
Since the maximum number of rows that I can display on the form at any given time is about 15 to 20, I have contained the rows in a Frame, with a dynamically scaled ScrollBar. If necessary, I suppose this could be changed to a multipage control, though the scrolling frame option seems more scalable.


[Q1] QUESTION

How to Clone Controls and their Event Code?

What is the best way to replicate my “template” row of controls, retaining all properties (except name obviously and horizontal position); and all event code (which would refer to the clone not the template)?

IDEA (1)
I could copy and paste the individual controls, and reposition them correctly, using loops, variables and constants. (If adopting this method, I’d probably do the alignment by column rather than by row.)

IDEA (2)
Would I be better off wrapping all controls in the “row” in another frame (or some other grouping system), and copying and pasting that. An additional benefit of this method would be that (for the reformatting procedure) I could use a For Each loop to iterate through all controls in the Frame, instead of naming all 9 individually.

IDEA (3)
Should I create a “row” Class? If so, I’ll be way out of my comfort zone, though I have created classes in Java with effort.

With any of the methods above, is the Event code of each copied Control encapsulated?
That is, will it be pasted with them?
If not, what is the best way to duplicate it?
I’ve read a little about the CodeModule and CreateEventProc methods.


[Q2] QUESTION

Method to Return Name of Control to which Event Code refers?

Within an Event code procedure, is there a method that returns the name of the control to which the Event code refers?

If so it would enable me to write generic Event code that could be reproduced for each row without any need to edit.

It seems logical to me that the interpreter would know the control name, from the procedure name if nothing else.

I considered the “Me” keyword, but that refers to the UserForm, or sometimes the Frame.
The “Parent” keyword doesn’t seem to help.

Currently, I’m using “ActiveControl” property, via a function I found on the web.
Thanks and credit to Peter Thornton [ Excel VBA Containers vs ActiveControl ]

This works fine for OptionButtons, because they can obtain focus, but when I tried to use it with clickable Labels, it was useless. Also it probably would not work for events which do not trigger a focus change.


CODE (2x Events; 3x Functions)

[B]Private Sub optCancelC08R001_Click()[/B]
 
    Dim ctlActiveControl As MSForms.Control
    Set ctlActiveControl = GetActiveControl()
   
    '(i)    Cancel OptionButton = "Yes"
 
    Dim strRowNumber As String
    strRowNumber = (Format(Right(ctlActiveControl.Name, 3), "000"))
   
    RowCancellationFormattingOn (strRowNumber)
   
End Sub
 
 
[B]Private Sub optCancelC09R001_Click()[/B]
 
    Dim ctlActiveControl As MSForms.Control
    Set ctlActiveControl = GetActiveControl()
   
    '(i)    Cancel OptionButton = "No"
 
    Dim strRowNumber As String
    strRowNumber = (Format(Right(ctlActiveControl.Name, 3), "000"))
   
    RowCancellationFormattingOff (strRowNumber)
   
End Sub
 
 
[B]Public Function RowCancellationFormattingOn(strRowNumber As String) As Boolean[/B]
 
    '(i)    Cancel OptionButton = "Yes"
   
    Dim strRN As String
    strRN = strRowNumber
   
    Me.Controls("lblDataC01R" & strRN).BackColor = &H8080FF
    Me.Controls("lblDataC02R" & strRN).BackColor = &H8080FF
    Me.Controls("lblDataC03R" & strRN).BackColor = &H8080FF
    Me.Controls("lblDataC04R" & strRN).BackColor = &H8080FF
    Me.Controls("lblDataC05R" & strRN).BackColor = &H8080FF
    Me.Controls("lblDataC06R" & strRN).BackColor = &H8080FF
    Me.Controls("lblDataC07R" & strRN).BackColor = &H8080FF
    Me.Controls("lblCancelC08R" & strRN).BackColor = &H8080FF
    Me.Controls("lblCancelC09R" & strRN).BackColor = &H8080FF
   
    '(i)    Confirm Success of Function
   
    RowCancellationFormattingOn = True
   
End Function
 
 
[B]Public Function RowCancellationFormattingOff(strRowNumber As String) As Boolean[/B]
 
    '(i)    Cancel OptionButton = "No"
   
    Dim strRN As String
    strRN = strRowNumber
   
    Me.Controls("lblDataC01R" & strRN).BackColor = &HF1FCFE
    Me.Controls("lblDataC02R" & strRN).BackColor = &HF1FCFE
    Me.Controls("lblDataC03R" & strRN).BackColor = &HF1FCFE
    Me.Controls("lblDataC04R" & strRN).BackColor = &HF1FCFE
    Me.Controls("lblDataC05R" & strRN).BackColor = &HF1FCFE
    Me.Controls("lblDataC06R" & strRN).BackColor = &HF1FCFE
    Me.Controls("lblDataC07R" & strRN).BackColor = &HF1FCFE
    Me.Controls("lblCancelC08R" & strRN).BackColor = &HF1FCFE
    Me.Controls("lblCancelC09R" & strRN).BackColor = &HF1FCFE
   
    '(i)    Confirm Success of Function
   
    RowCancellationFormattingOff = True
   
End Function
 
 
[B]Private Function GetActiveControl() As MSForms.Control[/B]
 
    (i)    [Credit to Peter Thornton]
 
    Dim ctl As MSForms.Control
    Set ctl = Me.ActiveControl
   
    Select Case TypeName(ctl)
   
        Case "Frame"
       
            Set GetActiveControl = ctl.ActiveControl
           
        Case "MultiPage"
       
            Set GetActiveControl = ctl.Pages(ctl.Value).ActiveControl
            
        Case Else
       
            Set GetActiveControl = ctl
           
    End Select
   
End Function
 
 
[B]Public Sub Main()[/B]
 
    Dim objUserForm As Object
    Set objUserForm = New frmBatchPrintOrder
   
End Sub
 
 
[B]Private Sub UserForm_Initialize()[/B]
   
    With Me.fraData01
   
        .ScrollBars = fmScrollBarsVertical
        .ScrollHeight = .InsideHeight * 8.5
   
    End With
   
End Sub

PS: I know this is a verbose post. I hope that's not a problem. I reckon that those who want to know the crux of the issue can hop to the questions, but those who want to know the full picture (in case my approach is way off) have that info too. Also, it saves all the follow up questions. Lastly, if the approach so far is good, it may help others.

Cheers. TIA.

 

[Norie]

Wouldn't it be easier to use a ListBox?

 

[Default300]

Hi Norrie.

Thanks for your reply and suggestion. Sorry for not getting back to you till now. I logged out soon after I wrote the post last night.

I had considered using a ListBox. I presume you were suggesting a multicolumn, multiselect ListBox.

This would be a simple solution in some ways. For instance, the number of rows could easily be adjusted according to the dimensions of the array.

But I decided against it for the following reasons:

I would still need an OptionButton pair (or a CheckBox or ToggleButton) for each row.
These would still need to be generated as described in the OP, I think, so that loop would still have to be coded and run.
It would be very difficult to keep the OptionButtons aligned with the corresponding row of the ListBox.

My end users have very basic computer skills, and even the idea of multiselect using the CTRL key may be too complex for them. Also it could be frustrating even for an experienced user to try to multiselect from 200 rows without a mistake.

The highlighting of selected rows is limited to grey, whereas I can use red.

The format of each column has to be grossly the same (except for alignment I think), whereas I can format columns containing more important information differently.


To be honest, this particular dilemma could be solved by listing the information in a table on a new worksheet, with Yes/No list data validation substituting for the OptionButtons. But in my opinion the UserForm solution would be more elegant.


More importantly, I would really like to learn the answers to the questions asked, to learn the best technique for implementing it in the way described (unless it’s impossible of course). Because future forms will need other non read only controls (eg TextBoxes or those mentioned above).

Do you know the answers to any of those questions, or pointers to techniques for implementing as (or similar to) the way described?

Thanks for you time.

 

[Norie]

You do know you can display option buttons or checkboxes in a listbox.

 

[Default300]

Hi Norrie.

I wasn’t aware about that option with ListBoxes. My previous experience was of ComboBoxes which, as far as I know, do not have the MultiSelect option, or the CheckBox styling.

Thanks for the tip.

Having read and experimented a little, I’ve found that:
Setting the MultiSelect property to fmMultiSelectMulti means the user can toggle selection without using CTRL.
Setting the ListStyle property to fmListStyleOption adds a pseudo-CheckBox to the left of the leftmost column (although clicking anywhere on the row toggles both shading and checkmark on and off).
RowSource allows easy mapping of an array or worksheet table.
And setting certain ColumnWidths to 0 pt hides unwanted columns.

I haven’t found out how to retrieve the selected lines, but I believe it’s via the Selected property.


So, that would work. And would be very simple compared to what I’m proposing. The only remaining disadvantage is aesthetics and clarity.


Nonetheless, I’d really like to learn how to do it the other way, even if I don’t employ it as the solution to the immediate problem.

Do you (or does anyone else) know the answers to the questions I asked?


From searching other sites on the web, it appears that Controls cannot be copied and pasted at runtime. They have to be created using the Add method, and all non-standard properties have to be individually set.

Others reading this post may be interested to note that MZTools can copy and paste code with controls (at design time).

I may use this example: [ VBA - Adding controls dynamically into a Userform ] as a template for my own code, unless someone has a better suggestion.


I'll probably have follow up questions regarding the most efficient way to iterate through a collection, but I'll ask that when "I know what I don't know". I'm still at the stage where "I don't know what I don't know", if you get my drift.

I guess the gist of what I'll be asking is:

[Q3] QUESTION

Can a subset of a Collection of Objects be held in a DataStructure?

In other words, can I use a single For Each loop to select a subset of a collection of Controls (which match certain criteria), and hold that collection in a datastructure, for example an array, preferably by reference.

This would seem to be the most efficient way to apply properties that they have in common (such as ForeColor for all Labels, or Left position for all Labels in column 1).

Cheers.

 

[Norie]

You can easily add controls at run time, setting their properties when you add them.

To control their events you can use classs modules.

There is already a collection of controls for the userform, Controls.

You can loop through that and use the name, type, tag, whatever to identify if a control is part of some sort of 'group'.

If you use a class then then you'll probably end up with collections of the controls you are going to control with the class.