How to Update GUI/UserForm elements with Application Level Events?

Problem:

I want to update a ComboBox/TextBox when I open a workbook or click a cell in a different Workbook. I came to wanting to pass a function to the class to execute. Because it seems the most plausible way. VBA is just really confusing me there.

Why? I'm writing a macro with a GUI that needs an Information-Workbook(Info-WB) to be open to summarize a selected range of sheets in another workbook (S-WB). And I want to display in realtime whether or not the Info-WB is open and then the GUI Elements should react to selecting the sheets(and cells in it) in S-WB like with a pipette tool.

What I tried:

I read this which seems like a solution but I don't quite get it: https://stackoverflow.com/questions/53123046/implementing-a-change-event-to-check-for-changes-to-textbox-values-and-enabling

I think this is what I need: https://stackoverflow.com/questions/19860901/update-form-with-events https://bettersolutions.com/vba/events/excel-application-level-events.htm I get that I need the to glue events to a class:

Public WithEvents oProgressFormClassInstance As clsProgressForm
'The WithEvents here implies that whenever the specific object referenced by this variable
'throws an event, it will trigger the event handler for that event in this class (form)

But how do I actually use the class with events as the class VBA creates for me when I build a menu using the IDE?

I've also found a question on this site pointing me to using Application.Run inside the class that has the eventhandler. So lastly I was thinking of saving the UI elements and function name as variable in that class and pass it to the event this way. But the way I learned programming (with lua) it just seems way too obfuscated and hacky.

updater class:

Public WithEvents appevent As Application

'Implements ComboBox '...one can dream

Public funtionName As String
Public uiElement As Variant

Private Sub appevent_WorkbookOpen(ByVal Wb As Workbook)
    Application.Run updateSomeIUStuff
End Sub

Private Sub Class_Initialize()

    Set Me.appevent = Application
End Sub

EDIT: Added "Set" to "Me.appevent = Application"

Code in the UserForm:

Private Sub UserForm_Initialize()
    
    Dim uUpdater As Variant
    Set uUpdater = New updater

End Sub

Sub updateSomeIUStuff()
    
    MsgBox "If Mommy's Purse Didn't Belong In The Microwave, Why Did It Fit?"
    'ComboBox.doStuff()

End Sub

Now for some reason VBA sais: Object-variable or With-block-variable not defined and points me to "Me.appevent = Application"

This just looks wrong to me on top of not working. Why is Microsoft doing this to me?

Solution

Minimal Example of what you need.

This is the code inside the UserForm1. I added a TextBox (TextBox1) to the UI. The listener or in your case the uUpdater is a member/field of UserForm1. Inside the Initialize sub of UserForm1 you simply need to instanciate the listener/uUpdater.

Private listener As EventListener

Private Sub UserForm_Initialize()
    Set listener = New EventListener
End Sub


Public Sub UpdateSomeStuff()
    MsgBox "Some Info"
End Sub

This is the code inside EventListener. You don't need to use Application.Run. You can simply call the function as shown in my example.

Public WithEvents appevent As Application


Private Sub appevent_WorkbookOpen(ByVal Wb As Workbook)
    UserForm1.UpdateSomeStuff
End Sub

Private Sub Class_Initialize()
    Set appevent = Application
End Sub

Actually you don't need a separate class if you just want to listen to events

In this example UserForm1 listens to the application events.

Private WithEvents app As Application

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    UpdateSomeStuff
End Sub

Private Sub UserForm_Initialize()
    Set app = Application
End Sub


Public Sub UpdateSomeStuff()
    MsgBox "Some Info"
End Sub