Need RichText control in Excel (VBA)
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Need RichText control in Excel (VBA)
I'm trying to provide a way for users to edit cell contents and cell comments for some cells on a protected sheet. My plan is to let the user double-click on a cell and then present a form with two Rich-text boxes that show the current content of the cell and its comment (if any) and then allow the user to edit and commit.
I can do most of this in VBA (2003) no problem, but how do I get a (actually two) rich text boxes on a form?
I can do most of this in VBA (2003) no problem, but how do I get a (actually two) rich text boxes on a form?
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need RichText control in Excel (VBA)
The Rich Textbox control does not come with MS Office - it is a VB6 control, so it may not be present on all PCs.
Moreover, Microsoft has disabled its use in Office applications because of security problems - see Problems occur when you use the Rich TextBox Control 6.0 in Office XP and in Office 2003. The suggested workaround is to use VB6 to wrap the rich textbox control in a container control, and to use this container control on your userforms. However, this would require you to distribute the container control to your end users.
Moreover, Microsoft has disabled its use in Office applications because of security problems - see Problems occur when you use the Rich TextBox Control 6.0 in Office XP and in Office 2003. The suggested workaround is to use VB6 to wrap the rich textbox control in a container control, and to use this container control on your userforms. However, this would require you to distribute the container control to your end users.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Need RichText control in Excel (VBA)
Right, so is there a creative way around this? If I think a bit myself I can imagine copying the text to a cell somewhere on a different sheet and allowing edits there. Then when the users click an OK button I can copy back the contents to the original cell. Clicking a Cancel button would simply revert back to the original cell with the contents unchanged.
Seems simple so am I missing something maybe?
Update: I can imaging someone might have built a control as well... I'm searching but can't find (yet)
Seems simple so am I missing something maybe?
Update: I can imaging someone might have built a control as well... I'm searching but can't find (yet)
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need RichText control in Excel (VBA)
That should work - if you copy and paste a cell, you copy its formatting and its comment (including formatting) too.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1258
- Joined: 03 Feb 2010, 19:59
- Location: Terneuzen, the Netherlands
Re: Need RichText control in Excel (VBA)
Yeah but I want to copy also the COMMENT incl formatting in a cell so the user can edit that too (as text in a cell, not as comment in that cell)... more complex. I was able to program all already but... with TextBox controls on my form only which mean 'simple text' only. If I'd only had simple formating functions... I would be done :-)
Again, is there no freeware control out there that implements this? I understand that then I 'd have to distribute this with my sheet but that might be worth it maybe
Again, is there no freeware control out there that implements this? I understand that then I 'd have to distribute this with my sheet but that might be worth it maybe
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need RichText control in Excel (VBA)
If you want to use a cell to edit a comment, you'd have to program the conversion in both directions - how to get the comment including formatting into the contents of a cell, and vice versa.
I don't know of any good free richtext controls. If you go searching for them, be careful - some offerings are not really free, others appear rather shady to me...
I don't know of any good free richtext controls. If you go searching for them, be careful - some offerings are not really free, others appear rather shady to me...
Best wishes,
Hans
Hans
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Need RichText control in Excel (VBA)
I'd take a different approach. Give the editable cells a specific style called "Edit"and add this code to the worksheet in question:
Now you can doubleclick to edit the cell and right-click to edit the comment.
Code: Select all
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Style = "Edit" Then
Me.Unprotect "test"
Else
Cancel = True
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Me.Protect "test"
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Style = "Edit" Then
Me.Unprotect "test"
Cancel = True
SendKeys "+{F2}"
End If
End Sub
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need RichText control in Excel (VBA)
If you don't mind losing the capability to undo, that is a much more elegant solution!
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 21 Oct 2010, 14:31
Re: Need RichText control in Excel (VBA)
I had the same problem (needing a Rich TextBox type control in Excel 2010).
I discovered a MS provided control called an Ink Edit control, which serves nicely as a RichTextBox control. You'll need to add it to your VBA controls (Tools->Additional Controls, then check the MS Ink Edit Control checkbox).
You'll need to build in your own 'format editor' for these controls. In my case, I provided the standard formatting keys Ctrl+B (bold), Ctrl+I (italic) and Ctrl+U (underline) by trapping the control's KeyPress event. The control behaves as you would expect: you can select all or a portion of the text in the control, then press Ctrl+B to bold (or unbold) the selected text. Or, if no text is selected, you can press Ctrl+B to enable bolding (or unbolding) for any new characters you type.
But here's where I'm stuck (and need your help!). I need to copy the formatted text from my Ink Edit control to a worksheet cell. The Ink Edit control provides two properties, Text and TextRTF, for retrieving the contents of the control. Text returns the control's unformatted text as a string; TextRTF returns, also as a string, the "RTF code" representing the formatted text in the control. Obviously, if I simply assign the control's Text property value to a worksheet cell, I lose any formatting (no bolding, no italics, etc.). If I assign the control's TextRTF property value, I end up with the control's RTF code, not the actual formatted text from the control.
I've played around with the DataObject object, to see if I could somehow copy/paste the control's formatted text to the clipboard and then paste it to a worksheet cell. No luck. The only (perverse) method that seems to work is to: 1) Select all the text in the Ink Edit control; 2) Copy the selected text to the clipboard; 3) Paste the clipboard into a Word document; 4) Copy the text from the Word document to the clipboard; 5) Paste the clipboard to the Worksheet cell.
There must be an easier way to do this that I'm overlooking!
Does anyone know how I can copy the formatted text from an Ink Edit control to a worksheet cell, so that all text formatting is preserved?
I discovered a MS provided control called an Ink Edit control, which serves nicely as a RichTextBox control. You'll need to add it to your VBA controls (Tools->Additional Controls, then check the MS Ink Edit Control checkbox).
You'll need to build in your own 'format editor' for these controls. In my case, I provided the standard formatting keys Ctrl+B (bold), Ctrl+I (italic) and Ctrl+U (underline) by trapping the control's KeyPress event. The control behaves as you would expect: you can select all or a portion of the text in the control, then press Ctrl+B to bold (or unbold) the selected text. Or, if no text is selected, you can press Ctrl+B to enable bolding (or unbolding) for any new characters you type.
But here's where I'm stuck (and need your help!). I need to copy the formatted text from my Ink Edit control to a worksheet cell. The Ink Edit control provides two properties, Text and TextRTF, for retrieving the contents of the control. Text returns the control's unformatted text as a string; TextRTF returns, also as a string, the "RTF code" representing the formatted text in the control. Obviously, if I simply assign the control's Text property value to a worksheet cell, I lose any formatting (no bolding, no italics, etc.). If I assign the control's TextRTF property value, I end up with the control's RTF code, not the actual formatted text from the control.
I've played around with the DataObject object, to see if I could somehow copy/paste the control's formatted text to the clipboard and then paste it to a worksheet cell. No luck. The only (perverse) method that seems to work is to: 1) Select all the text in the Ink Edit control; 2) Copy the selected text to the clipboard; 3) Paste the clipboard into a Word document; 4) Copy the text from the Word document to the clipboard; 5) Paste the clipboard to the Worksheet cell.
There must be an easier way to do this that I'm overlooking!
Does anyone know how I can copy the formatted text from an Ink Edit control to a worksheet cell, so that all text formatting is preserved?
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need RichText control in Excel (VBA)
Hi prlaba,
Welcome to Eileen's Lounge!
I don't know of a more straightforward solution. Excel doesn't "know" RTF, so you either need to parse the RTF source yourself, or to let an application that knows RTF, such as Microsoft Word, handle it. All threads about this subject that I was able to find in Google suggest the use of Word. You can do this using Automation to control a hidden instance of Word.
Welcome to Eileen's Lounge!
I don't know of a more straightforward solution. Excel doesn't "know" RTF, so you either need to parse the RTF source yourself, or to let an application that knows RTF, such as Microsoft Word, handle it. All threads about this subject that I was able to find in Google suggest the use of Word. You can do this using Automation to control a hidden instance of Word.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 21 Oct 2010, 14:31
Re: Need RichText control in Excel (VBA)
Thanks Hans, I was afraid that might be the answer. :-)
What's curious is that VBA does preserve text formatting when copying text between cells. For example, if I use VBA to copy a cell containing formatted text to another cell (using the Range.Copy method with the target cell as its argument), the target cell's text is also formatted.
You said you didn't think Excel "knows" RTF. If that's true, I don't understand how I'm able to copy text from a Word document and paste it into an Excel worksheet with all formatting preserved. If RTF is not the common format between the two, then what is?
I guess I'll head down the "Word as an intermediate clipboard" path, but still believing there must be an easier way to do this!
Paul
What's curious is that VBA does preserve text formatting when copying text between cells. For example, if I use VBA to copy a cell containing formatted text to another cell (using the Range.Copy method with the target cell as its argument), the target cell's text is also formatted.
You said you didn't think Excel "knows" RTF. If that's true, I don't understand how I'm able to copy text from a Word document and paste it into an Excel worksheet with all formatting preserved. If RTF is not the common format between the two, then what is?
I guess I'll head down the "Word as an intermediate clipboard" path, but still believing there must be an easier way to do this!
Paul
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Need RichText control in Excel (VBA)
Excel and Word have both been programmed to place formatted objects on the clipboard, and to paste those preserving some or all of the formatting. I don't think they use RTF for that - it might be HTML, or some internal Office format.
If you find a better way, please let us know - I'm sure others would benefit from it. Thanks in advance!
If you find a better way, please let us know - I'm sure others would benefit from it. Thanks in advance!
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 21 Oct 2010, 14:31
Re: Need RichText control in Excel (VBA)
I ended up solving this using a "brute force" approach. Here's the basic method I wrote:
Note that turning off screen updating is necessary for performance; otherwise, go get the paper while you're waiting for the method to complete.
Paul
Code: Select all
Public Sub CopyInkEditControlToCell(ByVal ieControl As InkEdlib.InkEdit, ByVal cell as Range)
'
' Copies the text within an Ink Edit control to a worksheet cell, preserving bold, italic and
' underline formatting.
'
' Args:
' ieControl An Ink Edit control on a form
' cell A single cell range in a worksheet
'
' Returns:
' None
'
' Remarks:
' No other formatting is preserved.
'character's position in a string
Dim pos As Integer
'turn off screen updating
Application.ScreenUpdating = False
'DEBUG: InkEdit control must not be null
Debug.Assert Not ieControl Is Nothing
'DEBUG: cell must be a single cell range
Debug.Assert cell.Count = 1
'clear the cell
cell.ClearContents
'clear any existing bold, italic & underline formatting in the cell
With cell.Font
.Bold = False
.Italic = False
.Underline = xlUnderlineStyleNone
End With
With ieControl
'copy the control's unformatted text to the cell
'(strip all <cr> chars, they mess up SelStart)
cell = Replace(.Text, vbCr, "")
'do for each character in cell's text
For pos = 1 To Len(cell)
'if a printable character
If Asc(Mid(cell, pos)) >= Asc(" ") Then
'select next char in InkEdit control
'(note: SelStart is 0-based)
.SelStart = pos - 1
.SelLength = 1
With cell.Characters(pos, 1).Font
'assign font's Bold property
.Bold = ieControl.SelBold
'assign font's Italic property
.Italic = ieControl.SelItalic
'assign font's Underline property
'(takes an xlUnderlineStyle constant)
.Underline = IIf(ieControl.SelUnderline, xlUnderlineStyleSingle, xlUnderlineStyleNone)
End With
End If
Next
End With
'turn on screen updating
Application.ScreenUpdating = True
End Sub
Paul
-
- Administrator
- Posts: 78596
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands