CODEWITHSUNDEEP
vbaexceldynamiclabel
yuro
yuro

Reputation: 2209

How to define a label dynamically in VBA?

I have a Userform with a listview. In this listview one column is defined with checkboxes. So, when users click on checkboxes in several rows then I want to display the selected IDs in a caption of a label, e.g.: "You are selected: 848, 12133, 414"

How can I achieve this?

Upvotes: 0

Views: 1299

Answers (1)

Sun
Sun

Reputation: 762

EDIT: I just saw that you are talking about a listview...

Here is a modified code snippet from AlphaFrog on ExcelForum:

Private Sub Listview1_ItemCheck(ByVal Item As MSComctlLib.ListItem)

Dim si As MSComctlLib.ListSubItem

    For Each si In Item.ListSubItems
        si.ForeColor = IIf(Item.Checked, vbBlue, vbBlack)
    Next

End Sub

What he does is change the Fontcolour depending on the status of the Checkboxes (Item.Checked).

You can use the logic of my original reply below to get your desired results.

Original reply:

Here is an example with 3 checkboxes, I'll only be posting the code for Checkbox1 as well as the initialisation. The code in Checkbox1_Click goes one-to-one into Checkbox2_Click and Checkbox3_Click.

Dim cnt As Integer
Dim checked() As Integer
Dim tmp As Integer

Private Sub CheckBox1_Click()

    cnt = 0

    ReDim checked(1 To 3)

    tmp = 1

    For i = 1 To 3
        If UserForm1.Controls("Checkbox" & i).Value = True Then
            checked(tmp) = i
            cnt = cnt + 1
            tmp = tmp + 1
        End If
    Next i

    Select Case cnt

        Case 0
        Label1.Caption = ""

        Case 1
        Label1.Caption = "You have selected: " & UserForm1.Controls("CheckBox" & checked(tmp - 1)).Caption

        Case Is > 1
        Label1.Caption = "You have selected: " & UserForm1.Controls("CheckBox" & checked(1)).Caption
        For t = 2 To tmp - 1
            Label1.Caption = Label1.Caption & ", " & UserForm1.Controls("CheckBox" & checked(t)).Caption
        Next t

    End Select

End Sub

'same code here for Checkbox2 and 3


Private Sub UserForm_Initialize()

    For i = 1 To 3
        UserForm1.Controls("CheckBox" & i).Value = False
    Next i

    Label1.Caption = ""

End Sub

Proof of concept:

enter image description here

Upvotes: 1

Related Questions