Code for UserForm1 not working - won't revert to single cell

[Gossamerga]

Hi - I know enough to be dangerous - this code gives me a userform1 in two columns on my spreadsheet that launches a "text box" to enter multi lines of text but does not return to single cell when you hit save. I know it can be done because the code is directly from another sheet where it works. What am I doing wrong? Is it some item line maybe in the userform section?

Any suggestions? I have attached the worksheet I am messing with. Thanks.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Only run if a cell in columns 7 (G), 8 (H) are double-clicked.
If Not Intersect(Target, Union(Columns(7), Columns(8))) Is Nothing Then
' Set the form's field to the cell contents
UserForm1.txtValue.Value = Target.Value
' Show the form
UserForm1.Show
' Cancel editing the cell
Cancel = True
End If
End Sub

[ptmuldoon]

Because you are already making sure you double clicked the appropriate target. Why not just set the activecell to the value of text input.

Please Login or Register  to view this content.

[Gossamerga]

Thank you for the reply and the suggestion. I incorporated your suggestion and although it runs I did not get the result as intended. Ialso went back to the original file and exported the userform that I know works and then imported it to my new spreadsheet and I still did not get what I need. I am not sure what my next step should be at this point.

[ptmuldoon]

am I correct that you are trying to the following?

1. Double click the cell, which opens the userform.
2. Type in the textbox of the userform.
3. When the pressing save, write the text from the userform back to the cell?

That small change I had shared did that. Did you update the correct onclick event?

[Gossamerga]

I apologize for not asking the right questions. - here is my goal and I attached a picture of what I am trying to do with code that works versus my copy that only works half way:

1-Doubleclick on any cell in Columns 7(G) and 8(H) in my worksheet and open userform.
2 - Type as many lines of text as needed, save the text in the userform by clicking on the save button.
3- After clicking on the save button in the userform I do not want all the text entered back to the cell. I only want to show the first few words of the user entered text from the userform and not all the text that was entered.

See example:
example.PNG

[ptmuldoon]

Can you post the existing code or file you have that is doing this?

Excel is storing the full userform text someplace in the file.

Is the actual cell file showing the entire text when you press F2 or look in the cell content? That could be done with basic formatting to only show the first few words and to not wrap the text.

Or its possible the code is placing the value of the UserForm text to another location in the workbook and then writing only the XX characters back to the active cell.

[Gossamerga]

I attached two files - one that works and one that doesn't. The only thing I changed in the code was the target columns for the userform (file that works K,L,M to the file that does not work G,H). I am driving myself nuts. I have looked at formatting, coding (in VBA editor) and just don't know why it works one way and not the other.

[ptmuldoon]

In the file that Works, the entire text of the userform is being written to the cell. Its just not 'showing' because of the row height and formatting.

Look in the actual cell and Press F2 or make the row height bigger and you will see the full text.

[Gossamerga]

I'm an idiot. THANK YOU.