Need RichText control in Excel (VBA)

[ErikJan]

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?

[HansV]

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.

[ErikJan]

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)

[HansV]

That should work - if you copy and paste a cell, you copy its formatting and its comment (including formatting) too.

[ErikJan]

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

[HansV]

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...

[Jan Karel Pieterse]

I'd take a different approach. Give the editable cells a specific style called "Edit"and add this code to the worksheet in question:

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

Now you can doubleclick to edit the cell and right-click to edit the comment.

[HansV]

If you don't mind losing the capability to undo, that is a much more elegant solution!

[prlaba]

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?

[HansV]

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.

[prlaba]

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

[HansV]

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!

[prlaba]

I ended up solving this using a "brute force" approach. Here's the basic method I wrote:

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

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

[HansV]

Thanks for posting your solution!