RichTextBox in Excel

I never needed a tool like RichTextBox in the Excel. But I've found out a lot of people want it. If you click on this link, you can see that this question will appear relatively frequently. But you can also see that this control is not supported in the Excel. You can find some tutorials and maybe some of them will work. Don't know it, haven't tried. So I don't judge.

I had a bit of free time at the end of 2013 and I got an idea. I used Microsoft Web Browser control to create my own RichTextBox and suprise - it's working.

There are two ways in which you may want to use a RichTextBox. Either you only want to show a formatted text in a UserForm or you may want to edit some text in a UserForm.

Formatted text in UserForm

Requires at least some knowledge of HTML language.

Insert a UserForm named UserForm1 into your project.
Insert a WebBrowser named WebBrowser1 to UserForm1. If you can't find the WebBrowser control, click the right button on the Toolbox, select Additional Controls... and find Microsoft Web Browser.
Set dimensions of UserForm1 and WebBrowser1 as you need. You can do it manually or with a piece of code.

Open the Userform1 code module and paste this code:

Private Sub UserForm_Initialize()
  Dim sDefHTML As String
  sDefHTML = "<html>"
  sDefHTML = sDefHTML & "<head>"
  sDefHTML = sDefHTML & "<style>"
  sDefHTML = sDefHTML & "body {overflow-x:hidden;overflow-y:auto;margin:0;padding:2 5;font-family:'tahoma';font-size:8pt;}"
  sDefHTML = sDefHTML & "p {margin:0;padding:0;}"
  sDefHTML = sDefHTML & "</style>"
  sDefHTML = sDefHTML & "<head></head>"
  sDefHTML = sDefHTML & "<body></body>"
  sDefHTML = sDefHTML & "</html>"
  
  With WebBrowser1
  .Offline = True
  .Silent = True
  .RegisterAsBrowser = False
  .RegisterAsDropTarget = False
  .MenuBar = False
  .Toolbar = 0
  
  .Navigate "about:blank"
  .Document.Write sDefHTML
  End With 'WebBrowser1
  
  WebBrowser1.Document.Body.innerHTML = "<b><font color=""red"">Hello</font> <font color=""blue"">world</font> !!!</b>"
End Sub

Run.

Scrollbars state
The best way how to set the state of scrollbars is to use css. Into the body selector enter overflow property for both scrollbars or overflow-x for horizontal and overflow-y for vertical scrollbar. To these properties asign one of the following values:
visible - the scrollbars are not added (default value),
auto - the scrollbars are added only when necessary,
hidden - the scrollbars are not added,
scroll - the scrollbar are always added.

If this way is not working, you can try set the scrollbars state in the <body> tag. The scroll attribute can have one of the following values:
auto - the scrollbars are added only when necessary,
no - the scrollbars are not added,
yes - the scrollbars are always added (default value).
In this case you have to write something like:

sDefHTML = sDefHTML & "<body scroll='auto'></body>"

No settings of the scrollbars mean the horizontal scrollbar will be allways displayed and the vertical scrollbar only when necessary.

How to change a value
There are several ways how to change a displayed text. .Document.Body.innerHTML is one of them. It always works with the entire displayed contents of WebBrowser similar to a classic textbox.

Insert a new CommandButton named CommandButton1 to UserForm1 with this code:

Private Sub CommandButton1_Click()
  WebBrowser1.Document.Body.innerHTML = "<b>Oh yeah, it works great !!!</b>"
End Sub

show the UserForm1, click the CommandButton1 and watch changing value.

How to read a value
For reading a formatted value (with HTML) use .Document.Body.innerHTML again.

For getting a plain text from WebBrowser I wrote this function:

Private Function fncGetText()
  Dim sRetValue As String
  
  With WebBrowser1.Document
  Call .ExecCommand("SelectAll")
  sRetValue = .Selection.createRange.Text
  .Body.innerHTML = .Body.innerHTML
  End With 'WebBrowser1.Document
  
  fncGetText = sRetValue
End Function

Replace the code for CommandButton1 with

Private Sub CommandButton1_Click()
  Dim sFormattedText As String
  sFormattedText = WebBrowser1.Document.Body.innerHTML
  Dim sPlainText As String
  sPlainText = fncGetText
  
  MsgBox sFormattedText
  MsgBox sPlainText
End Sub

show the UserForm1, click the CommandButton1 and watch returned values.

Simple text editing

You can use the WebBrowser for simple text editing. The magic is in these two words: contentEditable and ExecuteCommands.
Unfortunately ExecuteCommand doesn't support the font size in points only in number from 1 to 7 and this is the problem waiting for the solution ;).

You can download the workbook with RichTextBox here
The code is not locked so you can study or change it. I'll be glad to any reactions.

Updates

2017-03-04 - btnPaste_Click changed - paste text into a single cell.

5 comments:

Anonymous14/10/16 18:35
I've change little bit your classes source code to manage multiple instances of RichTextBoxes.

All run perfectly unless Font ForeColor (Back not on Excel).

Also if HTML code contains BR or P create data on next cell... It would be better to have an option to indicates on ONE cell (transform BR or P in Car(10) ?)

Where I can sent the new code...?

patrick.toledano@free.fr
Enrico31/3/17 11:29
I found your video on youtube and downloaded the file. If tried richtextbox in your file and works great, so i exported the userform and modules and imported them into one file that i use for work but when i try to launch userform there is and error "method 'ListCount' from object '_CommandBarComboBox' failed".
I look your code but i don't understand the problem. Do you know now where could be the problem?

Thanks, Enrico
Anonymous23/11/23 12:36
It dosent work in excel 365 :/

Stránky

Translate

Featured post

Working with two monitors

2015-02-02