Adding Text that self adjusts in a Cell
EE Pros,
I have a Worksheet that has a place to put notes in next to specific Statements. This occurs in Column K. I have copied some code that I had in another workbook that allows for text to be typed into the cell(s) (in this case, Column K and it will auto space the inputted text to adjust the cell to display as much text as you type in (i.e. the Row expands as with a "wrap text" command). However, here's what is different. When you do a carriage return, it recognizes it in the cell (i.e. avoiding the Alt. CR command so typical of Excel). This is why I included the other code.....so you could see what it does that works in the other WS.
Hope you will take a look/see.
Thank you in advance,
B.
Add-Text-to-Cell.xlsm
I have a Worksheet that has a place to put notes in next to specific Statements. This occurs in Column K. I have copied some code that I had in another workbook that allows for text to be typed into the cell(s) (in this case, Column K and it will auto space the inputted text to adjust the cell to display as much text as you type in (i.e. the Row expands as with a "wrap text" command). However, here's what is different. When you do a carriage return, it recognizes it in the cell (i.e. avoiding the Alt. CR command so typical of Excel). This is why I included the other code.....so you could see what it does that works in the other WS.
Hope you will take a look/see.
Thank you in advance,
B.
Add-Text-to-Cell.xlsm
Martin Liss
A password is needed for the code. Can you attach a non-password protected version of supply the password?
ASKER
Martin,
So sorry! Password is "password".
B.
So sorry! Password is "password".
B.
I don't see any code that refers to column "K". If I'm mistaken then please post that code.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you want to add the substitution of ALT + Enter for a regular Enter while the user is typing in column K, you can do that with the following Worksheet_Change sub.
As written, if the user is working with a single cell in column K and makes the value something other than a blank, the Worksheet_Change sub adds a linefeed character after the data already entered, then uses SendKeys to put you back in edit mode. When you are done entering text, use the Tab or arrow key to select a cell in a different column. Because you may only have one Worksheet_Change sub in a worksheet code pane, I also included a statement to autofit the row, as previously suggested by Martin Liss.
As written, if the user is working with a single cell in column K and makes the value something other than a blank, the Worksheet_Change sub adds a linefeed character after the data already entered, then uses SendKeys to put you back in edit mode. When you are done entering text, use the Tab or arrow key to select a cell in a different column. Because you may only have one Worksheet_Change sub in a worksheet code pane, I also included a statement to autofit the row, as previously suggested by Martin Liss.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range
Set targ = Me.Columns("K")
If Intersect(targ, Target).Cells.Count = Target.Cells.Count Then
If (Target.Cells.Count = 1) And (Target.Value <> "") Then
If Not Intersect(ActiveCell, targ) Is Nothing Then
Application.EnableEvents = False
Target.Select
Target.Value = Target.Value & vbLf
Application.SendKeys "{F2}"
Target.EntireRow.AutoFit
Application.EnableEvents = True
End If
End If
End If
End SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Guys, thank you very much. Martin thank you for jumping in so quickly and Brad, thanks for hanging with this to get it just right. Works perfectly now.
Again, much thanks,
J.
Again, much thanks,
J.
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015