Excel VBA - Text from TextBox in the active cell

Question

I would like to build a simple UserForm with a TextBox and a CommandButton, which opens itself by clicking inside a empty cell in a special column (for example in the column A, the cell A1) inside the worksheet. When I enter a text and hit the button, the entered text should be pasted in the active cell (A1).

The building of the UserForm is not the problem, but the actions which are behind it.

Do you guys have an idea how to solve this? Or even better pieces of code?

Thanks in advance!

Answer 1

Here is a piece of code that will run when you click a cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("A1")) Is Nothing Then
            'name of userform .Show
        End If
    End If
End Sub

Where the code has a comment you need the name oof your userform and then .Show

Entering the text into, lets say A1 as you suggested, would be:

Private Sub CommandButton1_Click()

TextValue = TextBox1.Text

[A1].Value = TextValue

End Sub

Change the CommandButton1 and TextBox1 to whatever you named them(if you named them, which you should)

If you need anything clarified just ask