VBA / Class Module Help

[PhilPeel]

Hi

Struggling with something that hopefully someone can assist with.

I have a form that loads data from an Excel workbook, then dynamically creates labels using this data.

I need to be able to click on the labels to then perform an action.

ie if I click on label 1 I want to be able to open file 1, if I click on label 2 open file 2 etc..

this is my code

Option Explicit
Dim MaxLines As Integer
Dim LC As Integer
Dim JobNo(200) As MSForms.Control
Private Sub UserForm_Initialize()

Call LoadData

Call Update

End Sub
Sub LoadData()
    
    Workbooks.Open FileName:="s:\Work\Order Log.xlsx"
    
    LC = 1
    Do
    
        LC = LC + 1
        
    Loop Until Cells(LC, 1) = ""
    
    MaxLines = LC - 1

    Call Draw
   
    LC = 1
    
    Do
    
            JobNo(LC).Caption = Cells(LC, 1)
    
            LC = LC + 1

    Loop Until LC > MaxLines

    ActiveWorkbook.Close savechanges:=False

End Sub
Private Sub Update()

LC = 1
    
    Do
        
        With JobNo(LC)
            .Top = LC * 20
            .BorderStyle = 1
            .Height = 18
            .Left = 0
            .SpecialEffect = fmSpecialEffectRaised
            .BackColor = &HC0C0C0
        End With
        
        LC = LC + 1

    Loop Until LC > MaxLines

End Sub

Private Sub Draw()

LC = 1

    Do
        Set JobNo(LC) = Me.Controls.Add("Forms.Label.1")
        
        LC = LC + 1
        
    Loop Until LC > MaxLines
    
End Sub

Can anyone show me how to create a class so that I can achieve this?

thanks in advance....

Phil

 

[PhilPeel]

Wouldn't it be simpler to use a listbox or combobox?

yes - but I need to be able to apply colours to the individual items so I don't think this is an option

Phil