Specify Click Action for Dynamic Text on UserForm

[Jonathan Harrison]

I have a VBA userform with a grid of numbers, each of which needs its own "click" action. For example:

[0 1 1 4 0 1 0]
[2 0 0 0 0 2 0]

These numbers are updated through other VBA functions. How can I create click actions for each of these numbers? Right now this data is created as lables on the form, but that can be changed if needed. Ideally, clicking each number would call the same function, just passing the lable name to the function.

 

[Norie]

You could use a class to handle the click events of the labels, this is a very simple example of that.

Add a new class module, Class1.

Add this code to the class module.

Option Explicit

Public WithEvents lbl As MSForms.Label

Private Sub lbl_Click()
    MsgBox lbl.Caption
End Sub

Add a new, blank userform and put this code in it's module.

Option Explicit

Dim lbls(1 To 14) As Class1

Private Sub UserForm_Initialize()
Dim ctl As MSForms.Label
Dim I As Long
Dim lngTop As Long
Dim lngLeft As Long

    lngLeft = 5
    lngTop = 5
    
    For I = 1 To 14
    
        Set ctl = Me.Controls.Add("Forms.Label.1", "Label" & I, True)
        
        ctl.Height = 10
        ctl.Left = lngLeft
        ctl.Top = lngTop
        ctl.Width = 6
        
        Set lbls(I) = New Class1
        
        Set lbls(I).lbl = ctl
        
        ctl.Caption = Int(Rnd() * 7)
        
        lngLeft = lngLeft + ctl.Width + 2
        
        If I = 7 Then
            lngTop = lngTop + ctl.Height + 2
            lngLeft = 5
        End If
        
    Next I
    
End Sub

Now run the userform and see what happens when you click on one of the labels.

Like I said very simple.

 

[Jonathan Harrison]

Works like a charm. I should be able to adapt this to do what I need. Thanks for the quick, thorough reply!

 

[Jonathan Harrison]

These labels need to be able to change and the grid isn't always the same size. How can I remove all the old labels when I update the data so that old data isn't retained?

Example:
1st round of data [0 1 2 3 4]
2nd round of data [5 6 7] <--This is how it should look
2nd round of data [5 6 7 3 4] <--This is how it actually looks

 

[Norie]

In the example the labels were dynamically created at runtime in the userform Initialize event.

So I don't see why you couldn't remove them and create another set of labels.

I've not tested that though, bit early, but if it is possible then you should be able to repeat for as many 'rounds' are needed.

Alternatively, the form could be unloaded and then reloaded for each round.

Obviously the code would need to be altered so the no of labels isn't hard-coded.

How are you generating the data?

 

[Jonathan Harrison]

The data comes from a terminal session. It comes into a string that is then split into an array (one element per line of terminal output).

I've tried to use a button with this code but get the error below:

Private Sub btnScratch_Click()
Dim ctrlX As MSForms.Control
For Each ctrlX In Me.Controls
    If TypeOf ctrlX Is MSForms.Label Then
        Me.Controls.Remove ctrlX.name
    End If
Next ctrlX

End Sub

ERROR:
Run-time error '444':

Could not delete the controls. This method can't be used in this context.

 

[Jonathan Harrison]

It looks like the error was from the script tying to remove labels that were created in design mode before it got to the ones create at runtime. The runtime labels have a different font so I was able to fix the issue by checking the font type before attempting to remove to weed out the ones created in design. This probably isn't the "correct" way to fix it, but it works for me.

Private Sub btnScratch_Click()
Dim ctrlX As MSForms.Control
For Each ctrlX In Me.Controls
    If TypeOf ctrlX Is MSForms.Label And ctrlX.font.name <> "Tahoma" Then
        Me.Controls.Remove ctrlX.name
    End If
Next ctrlX

End Sub