Excel VBA Textbox click event on Userform

Question

On a EXCEL Userform, I have several Textboxes. Their number can vary as they are created dynamically.

I want to achieve the following: When the user clicks on any of the Textboxes, I want to display a Msgbox, but only on this particular userform and only once for the first click.

Could you give me a pointer for documentation that would help me achieve this?

after googling on this, my code looks like this:

Userform: create a variable number of textboxes

Option Explicit

Dim oKlasseExcel() As Klasse1

Sub userform_initialize()

    Dim i As Long
    Dim k As Long

    k = InputBox("insert number")
    i = 0

    Do
        ReDim oKlasseExcel(0 To i)
        Set oKlasseExcel(i) = New Klasse1
        Set oKlasseExcel(i).objTextbox = Userform1.Controls.Add("Forms.Textbox.1", "Textbox" & CStr(i))

        With oKlasseExcel(i).objTextbox
            .Left = 30
            .Top = 75 + 25 * i
            .Width = 300
            .Height = 25
            .MultiLine = True
        End With

        i = i + 1

    Loop Until i = k

End Sub

class module:

Option Explicit

Public WithEvents objTextbox As MSForms.TextBox

Sub objTextbox_click()
    MsgBox objTextbox.Name & ": Changeereignis ausgelöst!"
End Sub

I think I have to create a class module probably, but I am totally new to this and I think I need a well-written example with some explanation comments, please. My code above does nothing when I click on a textbox.

I have no clue how to do this. Well... have you tried something? I suggest to use some kind of flags to know if an Textbox was clicked or not after capturing clicks. Please, complete the question with some code. — PaperBirdMaster
– PaperBirdMaster, Commented Mar 12, 2014 at 11:51

crazylane · Accepted Answer · 2014-03-12 12:20:22Z

1

If you don't want to create any additional data structures to capture clicks, maybe you can try changing some TextBox properties, for example: create a TextBox with WordWrap = False and after it was clicked change its value to True - then you could distinguish which were clicked or not.

answered Mar 12, 2014 at 12:20

crazylane

627 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Willi Fischer Over a year ago

My idea was to use a boolean variable that is set to true once the user has clicked on a textbox and that is checked before displaying the msgbox.

crazylane Over a year ago

I don't think this is a good idea, because number of textboxes changes and that implies different number of bool variables (one variable for each textbox) - would you create an additional variable every time a textbox is created?

Willi Fischer Over a year ago

can I not just use a global variable and set it accordingly once the msgbox has been displayed?

Collectives™ on Stack Overflow

Excel VBA Textbox click event on Userform

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related