Press enter to start a new lin in a cell

[bwat]

Hi,

I have a merged cell - B61 (B61:H68) in which I want to type notes. I want to be able to hit enter and start a new line in the cell.

I know Alt-Enter does this, but end-users of this spreadsheet will not know this trick.

Is there VBA code I can use?

Thanks in advance......

 

[Jaafar Tribak]

Hi and welcome to the board.

workbook demo

This worked for me. The code also ensures that the formula bar doesn't extends down the worksheet which can be a real pain when the active cell contains a large amount of text.

Place the code in the worksheet module :

Option Explicit
 
[COLOR=seagreen][B]'change this range addr as needed.
'note: the code assumes the cells
'in the range "B61:H68" are merged.[/B][/COLOR]
Private Const MY_RANGE  As String = "B61:H68"
 
Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function FindWindowEx Lib "user32.dll" _
Alias "FindWindowExA" ( _
ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
 
Private Declare Function ShowWindow Lib "user32.dll" ( _
ByVal hwnd As Long, _
ByVal nCmdShow As Long) As Long

Private lApphwnd As Long
Private lFormulaBarhwnd As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    Static oPrevSelection As Range
    
    On Error Resume Next
    
    If Target.Address = _
    Evaluate(MY_RANGE).Address Then
    
        lApphwnd = FindWindow _
        ("XLMAIN", Application.Caption)
        lFormulaBarhwnd = FindWindowEx _
        (lApphwnd, 0, "EXCEL<", vbNullString)
        ShowWindow lFormulaBarhwnd, 0
        Call SetUpRange(Evaluate(MY_RANGE))
        Application.OnKey "~", Me.CodeName & ".PressAltEnter"
        Set oPrevSelection = Evaluate(MY_RANGE)
        
    ElseIf oPrevSelection.Address = _
    Evaluate(MY_RANGE).Address Then
    
        Application.OnKey "~"
        ShowWindow lFormulaBarhwnd, 1
        Set oPrevSelection = Target
        
    End If

End Sub
 
Private Sub PressAltEnter()
 
    SendKeys "{F2}", True
    SendKeys "%~", True
 
End Sub

Private Sub SetUpRange(Range As Range)
 
    With Range
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .MergeCells = True
    End With
 
End Sub

Regards.

 

[RoryA]

Wouldn't it be easier to just add a comment, or data validation message, to the cell telling them to use Alt+Enter?