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

 

[Kyle123]

Like this:
Class eventHandler

Public WithEvents lbl As MSForms.Label

Private Sub lbl_Click()
    MsgBox lbl.Caption
End Sub

Then in your userform:

Dim JobNo(1 To 200) As eventHandler
'...
Private Sub Draw()
    
    Dim e As eventHandler
    Dim lbl As MSForms.Label
    
    LC = 1
    Do
        Set lbl = Me.Controls.Add("Forms.Label.1")
        Set e = New eventHandler
        Set e.lbl = lbl
        Set JobNo(LC) = eventHandler
        
        LC = LC + 1
        
    Loop Until LC > MaxLines
    
End Sub

 

[PhilPeel]

Hi Kyle...

thanks for the reply.

I've created the Class Module and copied your code into it.. and renamed it to eventHandler - is this correct?

When I run the module I get "compile error - variable not defined" and "eventhandler" in Sub Draw is highlighted... what am I doing wrong???

thanks

 

[Kyle123]

It's really hard to say without seeing all your code

 

[PhilPeel]

hi.. the entire code I have is in the first post....
thanks
Phil

 

[Kyle123]

Not with the changes it isn't

 

[PhilPeel]

Not with the changes it isn't

sorry.. this is what I now have:-

Userform3:-

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()
    
    Dim e As eventHandler
    Dim lbl As MSForms.Label
    
    
    
    LC = 1
    
    Do
        
        Set lbl = Me.Controls.Add("Forms.Label.1")
        Set e = New eventHandler
        Set e.lbl = lbl
        Set JobNo(LC) = eventHandler
        LC = LC + 1
    
    Loop Until LC > MaxLines
    
End Sub

then I have a class module named eventHandler

Public WithEvents lbl As MSForms.Label

Private Sub lbl_Click()
    MsgBox lbl.Caption
End Sub

cheers

 

[Norie]

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

 

[Kyle123]

That can't be all your code, you're missing a load of declarations, for the array, line count etc..

Could you post it all please?

 

[PhilPeel]

That can't be all your code, you're missing a load of declarations, for the array, line count etc..

Could you post it all please?

missed the declarations from the top...

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()
    
    Dim e As eventHandler
    Dim lbl As MSForms.Label
    
    
    
    LC = 1
    
    Do
        
        Set lbl = Me.Controls.Add("Forms.Label.1")
        Set e = New eventHandler
        Set e.lbl = lbl
        Set JobNo(LC) = eventHandler
        LC = LC + 1
    
    Loop Until LC > MaxLines
    
End Sub

Class module:-

Public WithEvents lbl As MSForms.Label

Private Sub lbl_Click()
    MsgBox lbl.Caption
End Sub

thanks