Search code examples
excelvbaformsclass

VBA Excel forms: how to call a sub inside a form, from a class module

In VBA for Excel I have a Public Sub inside a User Form, which I am trying to call from a Class Module. When I run it in normal way, I get a runtime error 91 "Objectvariable or blockvariable With is not set". When I then go into debug mode onto the position where the error occurs (inside the Class Module) and push F8 to progress a step, it does move to the next step and actually enters and executes the sub inside the user form. What am I doing wrong?

A few side notes:

  • the reason why I am using a Class Module is that the controls that I have on my user form are dynamically generated (the user form may show up to 30 times the same button that each time refers to a different 'record' (equivalent to one item line))
  • there is more code in the Class Module itself and that is working fine in manipulating controls on the user form
  • the Sub in the User Form is Public and when I type the name in the Class Module it auto-completes
  • The Public Sub in itself works also flawless when I call it from within the User Form

A simplified version of the code:

In the Class Module I have:

Private Sub MinEvents_click()
Dim MinArt As MSForms.TextBox


    'A piece of code that works fine

    Call ScanTool.CheckPickingCompleet

    'More code that works fine

End sub

In the user Form called 'ScanTool' I have:

Public Sub CheckPickingCompleet()

    If ArtScan.Text = Me.Controls("Label_AantalArtikelen_" & GescandArtikel) Then
    
        'Here I do the actions required
    
    ElseIf ArtScan.Text > Me.Controls("Label_AantalArtikelen_" & GescandArtikel) Then
    
        'And here I have other actions
    
    End If

End Sub

Thanks in advance for your help, for what is very likely a very basic oversight from my side.

Solution

  • With the help of Black cat (thanks) and some further experimenting I now have got it to work. I had to refer explicitly to the form in the form class and not using "Me". The code in the form class now reads

    Public Sub CheckPickingCompleet()

Set ThisForm = ScanTool

Set ArtScan = ThisForm.Controls("StuksGepickt_" & GescandArtikel)

If ArtScan.Text = ThisForm.Controls("Label_AantalArtikelen_" & GescandArtikel) Then

    'Here I do the nice stuff
    
End If

End Sub

    I have to do the SET inside the sub in the form class, not in the class module and I have to ommit the New. I assume that is because I do not want to create anew instance, as it is my existing instance that has all the dynamically created controls in it