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 Subshow 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 FunctionReplace 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 Subshow 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.
|
Tweet |
I've change little bit your classes source code to manage multiple instances of RichTextBoxes.
ReplyDeleteAll 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
Hi Patrick. Thanks for your comment.
DeleteYou can send me your code by mail to eLCHa@email.cz.
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".
ReplyDeleteI look your code but i don't understand the problem. Do you know now where could be the problem?
Thanks, Enrico
Hi Enrico.
DeleteUnfortunately, I don't. Tried export - import and it works fine. For sure, you can check the references (I doubt that this is causing the problem). Try it on other pc. You can send me your file by mail.
It dosent work in excel 365 :/
ReplyDeleteHi, I now also have Excel 365 and everything is working normally.”
Delete