HTML in Cells II
While I was working on this newsgroup post to copy formatted text from Word to Excel while preserving formatting and maintaining line breaks, I stumbled on a way to enter HTML in cells.
becomes
with this macro
Private Sub Worksheet_Change(ByVal Target As Range)
Dim objData As DataObject
Dim sHTML As String
Dim sSelAdd As String
Application.EnableEvents = False
If Target.Cells.Count = 1 Then
If LCase(Left(Target.Text, 6)) = "<html>" Then
Set objData = New DataObject
sHTML = Target.Text
sHTML = Replace(sHTML, "<html>", "<html><style>br{mso-data-placement:same-cell;}</style>")
objData.SetText sHTML
objData.PutInClipboard
sSelAdd = Selection.Address
Target.Select
Me.PasteSpecial "Unicode Text"
Me.Range(sSelAdd).Select
End If
End If
Application.EnableEvents = True
End Sub
Dim objData As DataObject
Dim sHTML As String
Dim sSelAdd As String
Application.EnableEvents = False
If Target.Cells.Count = 1 Then
If LCase(Left(Target.Text, 6)) = "<html>" Then
Set objData = New DataObject
sHTML = Target.Text
sHTML = Replace(sHTML, "<html>", "<html><style>br{mso-data-placement:same-cell;}</style>")
objData.SetText sHTML
objData.PutInClipboard
sSelAdd = Selection.Address
Target.Select
Me.PasteSpecial "Unicode Text"
Me.Range(sSelAdd).Select
End If
End If
Application.EnableEvents = True
End Sub
I haven't quite got the line break part worked out here. You may notice that I try to put a style tag in the string before I put it in the clipboard. This doesn't seem to have any effect. The style tag worked with the aforementioned newsgroup post, but didn't work in this situation. In this case, text after a br tag is moved to the cell below, when I would prefer that it act like an Alt+Enter and create a new line in the same cell. I'm not quite sure why there's a difference.