Text Box in Excel

[Alltorc]

Hi there.

How do I add a text box into excel when you click on a specific cell.

What I want to accomplish is that when a user clicks on a certain cell a text box pops up and he is forced to enter a message into the box before it disappears. I want to the message to be displayed on the sheet once he/she has typed it.

Any idea's

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim s As String
If Target.Address(False, False) = "A1" Then
    Do While s = ""
        s = InputBox("Enter some text")
    Loop
    Target.Value = s
End If
End Sub

Change A1 to suit.

 

[Alltorc]

Hi there VoG

Thank you so much for this. Is there anyway to have the text appear in another block besides the targeted address.. So if they click in the cell the text appears in another row?

 

[VoG]

Sure, change E5 to suit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim s As String
If Target.Address(False, False) = "A1" Then
    Do While s = ""
        s = InputBox("Enter some text")
    Loop
    Range("E5").Value = s
End If
End Sub

 

[chuckchuckit]

You could modify one line of VOG's code to read:

Target.Offset(1,2).Value = s

That would move the text answer over 1 row and 2 columns. You could use variables in place of the (1, 2). Here it is modified VOG's code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim s As String
If Target.Address(False, False) = "A1" Then
    Do While s = ""
        s = InputBox("Enter some text")
    Loop
    Target.Offset(1, 2).Value = s
End If
End Sub