Which is the easiest way to input text?

[gebli]

Hi.

I'm building a simple worksheet which will be used be people with zero expertise on Excel.
Fill in cells with basic information should work ok for them: name, address, dates, etc.
A column should contain comments and ideally they should be able to type in text as in notepad. I know that I could pre-format the column for auto wrapping, but while they type, they text will not auto-wrap until they hit enter...
Is there any way to make a cell work as, for example, this text box in which I'm writing this post?
I don't know if a more simple solution is available but I was thinking in having a vba button that would open a tiny notepad (like the Excel comments feature).
BTW, the comments feature would almost work if I could only make the text end up in a cell rather than in a comment popup.

Thanks.

 

[sykes]

Hi, and Welcome to Mr Excel !!

Not much time to give a detailed solution, but you mentioned VBA.........

Insert a userform into the project, put a textbox onto the userform, and a commandbutton.
right-click the worksheet, selct "View code" and make the sheet's "selection_change" event holder look thus:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then UserForm1.Show
End Sub

Change the target column number (above) to that of the column in the sheet which holds the data you wish to manipulate.

Set the textbox multiline and wordwrap properties to TRUE.

Go back to your userform in the VBA project, right_click, select "View Code", and paste over the UserForm_click and End Sub lines (to clear them off) the following:

Private Sub CommandButton1_Click()
ActiveCell.Value = Me.TextBox1.Value
Unload Me
End Sub


Private Sub UserForm_Initialize()
If ActiveCell.Value = "" Then Exit Sub
Me.TextBox1.Value = ActiveCell.Value & " "
End Sub

You now have control over the text, and can format the range of cells to suit.

Quick answer, and hope it works!

 

[MickG]

Hi, Here's another option Using the InputBox Function.
Right Click sheet Tab , Select "View Code", VB Window appears.
Paste Code into empty window.

To run code double click any cell on sheet "Input box" appears.
Write message in box, Click OK, Message appears in cell selected.

Private [COLOR=navy]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR=navy]As[/COLOR] Range, Cancel [COLOR=navy]As[/COLOR] Boolean)
[COLOR=navy]Dim[/COLOR] Message, Title, Default, MyText
Message = "Enter Cell Message"
Title = "Target Cell Message"
MyText = InputBox(Message, Title)
[COLOR=navy]With[/COLOR] Target
    .Value = MyText
    .WrapText = True
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[gebli]

Thank you both! I'll check two options.