Excel VBA: KeyPress Events should trigger a new userform

Question

I am working on a program with a userform with many textboxes:

(I hope everyone understands German)

The event of the user trying to typing in something in the yellow textboxes should trigger to show a new userform:

This userform contains a bigger textbox with more space to type in a big text. After pressing the "Speichern" Button the Second Userform unloads and the text appears in the original textbox in the first userform. Therefor I have a class module to call the Keypress event which calls the second userform.

My problem: I want the first key pressed down, which triggers the event, to be seen in the second userform textbox.

Example: I select the third yellow textbox. I want to type in "This is a comment". By pressing down the first key "T", the new Userform opens already with the "T" in its textbox, so I can continue to write the rest of my text, and not type in the "T" again.

Til now: I have a working code to trigger the event by keypress. Therefor I am working with a class modul and a userform initialize sub. Unfortunately the textbox does not show already the first key which triggered the event.

My class module:

Public WithEvents TextBoxEvents As msforms.TextBox
'referring to all textboxes(addmaterialuserform) ~~> restriction in Addmaterialuserform / Userform-Initialize Sub

Private Sub TextBoxEvents_KeyPress(ByVal Keyascii As msforms.ReturnInteger)
'Keypress-event start by clicking any button on keyboard
'for more information https://docs.microsoft.com/de-de/office/vba/api/access.textbox.keypress

Keyascii = 0    'return value ~~> 0 for no return value
UserForm2.CommentBoxUserform.Value = TextBoxEvents.Value     'if small textbox already owns a string it'll be showed in userform (big) textbox
DataBaseSheet.Cells(3, 3).Value = TextBoxEvents.Tag     'saves tag (name) of active comment box to fill up later with string of big textbox

userform2.Show

End Sub

Userform Sub:

Private Sub UserForm_Initialize()
'all comment-textboxes have same properties to find in TextBoxClass
'when pressed any keyboard button the addcommentuserform will be opened to have a much bigger textbox to enter comment

    Dim myTBs()     As New TextBoxClass     'reference to TextBoxclass for Userform-initialize sub
    Dim i           As Integer
    Dim objControl  As Control

    For Each objControl In Me.Controls
         If TypeOf objControl Is msforms.TextBox Then
            Select Case objControl.Name     'selects only comment-textboxes by referring to their name
                Case "C1", "C2", "C3", "C4", "C5", "C6", "C7", "C8", "C9", "C11", "C12", "C13", "C14", "C15", "C16" '<~~ Include only these"
                    i = i + 1
                    ReDim Preserve myTBs(1 To i)    'call textboxclass
                    Set myTBs(i).TextBoxEvents = objControl
            End Select
        End If
    Next objControl
    Set objControl = Nothing

End Sub

No doubt there is an answer, but as a simple workaround perhaps you could the double click event instead? — SJR
What about one click event? Many people will use this program that's why I want it to work as intuitive as possible. — J.schmidt
I don't find it very intuitive that a new form is opened when I start to type. I would let the user enter it's comment it the "small" textbox if he wants and put a small button (eg with a magnifier glass) next to it to display the comment form. — FunThomas
Up to you. Personally I think that might be too easy to get false positives if you know what I mean. Do you really need a second userform at all? — SJR
@FunThomas it was an order of my boss to make it that way, he likes it to be really minimalistic, so he'd prefer no extra buttons... — J.schmidt

EvR EvR · Accepted Answer · 2019-01-15T13:22:15

Use the value of Keyascii but before you set it to 0:

Private Sub TextBoxEvents_KeyPress(ByVal Keyascii As msforms.ReturnInteger)

Userform2.CommentBoxUserform.Value = TextBoxEvents.Value & Chr(Keyascii)
Keyascii = 0    'return value ~~> 0 for no return value
DataBaseSheet.Cells(3, 3).Value = TextBoxEvents.Tag     'saves tag (name) of active comment box to fill up later with string of big textbox

userform2.Show 

End Sub

Excel VBA: KeyPress Events should trigger a new userform

1 Answers