How to declare custom event in userform using vba

Question

I have userform which collects some user input. Now what I'm trying to do, is to declare some event to throw from userform when OK button is clicked. I'm new to vba so I don't know how to do it. Any code or link to tutorial would be greatly appreciated.

Load UserForm1
UserForm1.Show
//here I want to capture UserForm1 OK button's click event and read the data

From Google: word.mvps.org/faqs/userforms/CreateAUserForm.htm — Fionnuala
– Fionnuala, Commented Oct 22, 2012 at 8:58
it's not what I'm looking for. I do have a userform, I want to capture it's command button's click event outside of userform. — Michael
– Michael, Commented Oct 22, 2012 at 9:40
I don't think it's possible, but you could capture the event within the form code then call a sub or function in a module outside the form. — Olle Sjögren
– Olle Sjögren, Commented Oct 22, 2012 at 9:42

Community · Accepted Answer · 2020-06-20 09:12:55Z

In child-form declare event and raise it at the certain moment:

Public Event clickOnChild(ByVal inputText As String)

RaiseEvent clickOnChild(Me.TextBox1.Value)

In a custom class module, worksheet class module or other user form you can catch the event. However you can't catch event in standard module because WithEvents variable are valid in object module only. To catch your event in e.g. other user form declare WithEvents variable of type childUserForm and add event-handler where the event will be catched and handled:

Private WithEvents childForm As childUserForm

Private Sub childForm_clickOnChild(ByVal inputText As String)

Complete example:

Child user form:

Option Explicit

Public Event clickOnChild(ByVal inputText As String)

Private Sub CommandButton1_Click()
  RaiseEvent clickOnChild(Me.TextBox1.Value)
End Sub

Parent user form:

Option Explicit

Private WithEvents childForm As childUserForm

Private Sub CommandButton1_Click()
  childForm.Show
End Sub

Private Sub childForm_clickOnChild(ByVal inputText As String)
  MsgBox "Input in child form was: " & inputText
End Sub

Private Sub UserForm_Initialize()
  Set childForm = New childUserForm
End Sub

Olle Sjögren · Accepted Answer · 2012-10-22 11:41:34Z

As I said in a comment, I don't think what you want to do is possible, but I thought of the following workarounds:

If your user input is very simple, like just entering a string, a messagbox could work:

Dim sUserInput As Variant

sUserInput = InputBox("Please enter something useful.", "Title", "Default")

Debug.Print "sUserInput=" & sUserInput

If you need the form to capture user input, making it modal and then exposing a value through a public method might work.

In the form:

Option Explicit

Private msFormString As String

Private Sub CommandButton1_Click()
    msFormString = "Someone clicked on Button 1!"

    '***** Note: if you use Unload Me, the string
    '***** is unloaded with the form...
    Me.Hide
End Sub

Public Function GetFormString() As String
    GetFormString = msFormString
End Function

The calling code:

Load UserForm1
Call UserForm1.Show(vbModal)
Debug.Print "Value from UserForm1: " & UserForm1.GetFormString

Note: The function could return an object, class or array if you need to pass more data back.

Collectives™ on Stack Overflow

How to declare custom event in userform using vba

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related