Stop spilling to next cell without Word Wrap

[tjtjjtjt]

I'm trying to stop the text I type into a cell from spilling over into the
next cell without using Word Wrap.
Searching through past messages I found that someone suggested putting =""
in the next cell to the right to make it look empty.
I was wondering if there was a way that would make it so I didn't have to
remember to type something in the next cell over.

tj

 

[Gord Dibben]

You could use event code to enter the space into the adjacent cell.

Example code..........

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in any cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = " "
'Note: "" won't cut it. You need " " to insert a <space>
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Using this method means you can never have data in the adjacent column B prior
to entering something in column A or it will get wiped out.

Gord Dibben Excel MVP

 

[tjtjjtjt]

Thanks

tj

You could use event code to enter the space into the adjacent cell.

Example code..........

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in any cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = " "
'Note: "" won't cut it. You need " " to insert a <space>
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Using this method means you can never have data in the adjacent column B prior
to entering something in column A or it will get wiped out.

Gord Dibben Excel MVP

 

[Dave Peterson]

but you could use a formula:
Excel.Range("B" & n).formula = "="""""

or even an apostrophe:
Excel.Range("B" & n).Value = "'"