Excel VBA copy Cell contents to a InkEdit text box and keep for formatting including colour/bold etc

I have a sheet where some cells have multicolored text and is in bold/underline/italic.

I need to be able to pull the cell contents and display the information on a form keeping the same formatting.

I have come across InkEdit control which supports RichText but I am unable to copy from cell to this box.

Help please

The problem seems to be that the Excel Object Model deeply buries the RTF formatting of the contents of a cell and provides no easy method to extract it.

Here is a kludge which seems to sort of work:

Sub CopyRichText(source As Range, target As InkEdit)
    Dim i As Long, n As Long
    target.Text = source.Text
    n = Len(target.Text)
    For i = 1 To n
        target.SelStart = i - 1
        target.SelLength = 1
        target.SelBold = source.Characters(i, 1).Font.Bold
        target.SelColor = source.Characters(i, 1).Font.Color
        target.SelFontName = source.Characters(i, 1).Font.FontStyle
        target.SelFontSize = source.Characters(i, 1).Font.Size
        target.SelItalic = source.Characters(i, 1).Font.Italic
        'target.SelUnderline = source.Characters(i, 1).Font.Underline '-- doesn't work as expected!
    Next i
    target.SelStart = n
    target.SelLength = 0
End Sub

Used like this:

Private Sub UserForm_Initialize()
    CopyRichText Range("A1"), Me.InkEdit1
End Sub

For example, in A1 I have:

Then when I show the userform it looks like:

There seems to be an outright bug in the inkedit's SelUnderline method. Uncomment that line to see what I mean. Perhaps there is some workaround.

I suspect that the above is somewhat fragile. I haven't tested it all that much. If it works for you (perhaps suitably tweaked) -- good. If not, I suspect that there is a deep-magic approach using the clipboard. InkEdit controls don't have paste methods -- but it does have an Hwnd method which sounds like it could provide a target for a Window's paste.

Excel VBA copy Cell contents to a InkEdit text box and keep , Excel VBA copy Cell contents to a InkEdit text box and keep for formatting including colour/bold etc - excel. Excel VBA copy Cell contents to a InkEdit text box and keep for formatting including colour/bold etc . Posted by: admin April 10, 2020 Leave a comment

The InkEdit control supports pasting of rich text, so all you really need to do is copy the Range, then paste it into the control. Since the control exposes it's .hWnd, all you need to do is use the SendMessage API function to send a WM_PASTE message:

'UserForm1
Option Explicit

Private Declare Function SendMessage Lib "user32" Alias _
            "SendMessageW" (ByVal hwnd As Long, ByVal wMsg As Long, _
            ByVal wParam As Long, lParam As Any) As Long

Private Const WM_PASTE = &H302

Private Sub UserForm_Initialize()
    RangeToInkEdit ActiveSheet.Cells(1, 1), InkEdit1
    Application.CutCopyMode = False
End Sub

Sub RangeToInkEdit(source As Range, target As InkEdit)
    source.Copy
    SendMessage InkEdit1.hwnd, WM_PASTE, 0&, 0&
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Note that this also has a slight issue similar to @JohnColeman's method - it doesn't do that great of a job at picking up the colors. This appears to be a problem that Excel has in the encoding of RTF that it sends to the clipboard rather than an issue with the InkEdit control itself (you can confirm that by copying and pasting into WordPad, which is basically an RTF editor). Some colors work, others don't - all of them will be reduced in color depth to basically the closest color that RTF supports.

Excel vba underline text in textbox, Excel VBA copy Cell contents to a InkEdit text box and keep for formatting including colour/bold etc. I have a sheet where some cells have multicolored text and  In this article you’ll learn, how to copy and paste cell content along with the cell formatting in Microsoft Excel. Sometimes, we want to copy and paste content along with the formatting. In Excel, we can make use of CTRL+C (copy command) and then paste at the destination.

Excel vba change font color for part of text, Excel VBA copy Cell contents to a InkEdit text box and keep for formatting including colour/bold etc -. Get link the problem seems excel object model buries rtf formatting of contents of cell , provides no easy method extract it. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more . Paste cell content to a textbox including text formats. Ask Question Asked 3 years, 11 months ago. Active 3 years, 11 months ago. Vi

Do you need to be able to edit the text? If not, then I would try copying the range and pasting it as a picture in the UserForm. Check Stephen Bullen's PastePicture example for code.

How to write text msg with bold in vba, Send email with specified bold/size/color/underline text format with VBA code Dec 12, VBA and am having trouble getting things like the font name, size, bold etc text in Word with all the formatting then paste this into the InkEdit text box. The value of this How to write Excel VBA macro to format differently cell text in 2  Code: Cells(1, 3).Copy Cells(1, 1) Cells(1, 1).Value = Cells(1, 3).Value However, I kinda suspect you were just providing us with an oversimplified example to ask the question. If you just want to copy formats from one range to another it looks like this

Excel vba bold text in string, Change font color for a part of text in cell · excel vba. of text found in certain cells. bold text, change font, color and size, underline, etc. That way you can write text in Word with all the formatting then paste this into the InkEdit text box. Any copy, reuse, or modification of the content should be sufficiently  In excel, I am trying to copy text from one cell to another cell in another sheet. The source cell contains formatted text (bold,underlined,different colors). But when I copy the text using VBA to the other cell, the formatting is lost. I know it is because excel is copying only the text value.

Vb bold text, How to make text appear bold using VBA Macro in Excel It would be a mail The MsgBox function is one of the most commonly used functions within Access. VB Copy. VBA Font Color property is used to change the font color of excel cells using text in Word with all the formatting then paste this into the InkEdit text box. Hi, i have an excel workbook with a worksheet and a textbox (insert/shapes) and in that textbox i have some formatted text that prints in to the textbox with VBA code. Some text are normal, some bold and some colored. Now i am trying to copy and paste this text in to another textbox and keep the formatting of the text but it will not work. I mark the text, ctrl + c. Then right click in the

Vba putinclipboard not working windows 10, Excel-VBA: create named Range in row until end of cell content. Click that to highlight the cells, then you can copy them and paste back into excel. using VBA and am having trouble getting things like the font name, size, bold etc attributes right. text in Word with all the formatting then paste this into the InkEdit text box. Re: VBA code to copy data from text box and input into a cell in another worksheet Hi, Right Click the TextBox in Design Mode, Select "View Code" and Paste code into VB Window. Please Login or Register to view this content.

In this article three basic text formatting tools will be explained: italics, bold and underline. The text in the textbox will be bolded with theThis Bold Text Generator can is data, data doesnt have bold or italic etc. com Bold is "read-​only" in VB. Bold Property of a Range/Cell in Excel using VBA. text to speech software for  Wrap Text. This example formats cell A1 so that the text wraps within the cell. Range("A1").WrapText = True Shrink To Fit. This example causes text in row one to automatically shrink to fit in the available column width. Rows(1).ShrinkToFit = True Merge Cells. This example merge range A1:A4 to a large one. Range("A1:A4").MergeCells = True Right

Comments
  • This doesn't seem to be a standard Active-X control. Why not give a link to it, and its documentation.
  • Its the InkEdit Control don't why I said InkPad This is one of the standard libraries that you can find in Tools->References
  • I see. There is a commercial product called InkPad, and it seemed possible that this was some sort of third-party control related to that. Perhaps you should edit the question.
  • @user3447974 My First Bounty!!! Yeah Baby!! Thanks!
  • Thank you, I appreciate the resolution you found and it does work but its very slow to update the control probably the control itself, tried with just 1, my project would require 20, need to find another way round this.
  • If you want to display the formatted contents of a cell (but not edit the contents in the userform) you could look into copying the cell as a picture, loading into a temp file, and then putting it into an image control. Unfortunately, it doesn't seem possible to (at runtime) load images directly from the clipboard.
  • I hardly ever use the With statement but I think you might use it here to squeeze some extra speed. With source.Characters(i, 1).Font
  • @SMeaden That is a good recommendation. Perhaps it could address some of OP's speed concerns, although with better answers now available it is probably moot.
  • I can't believe that you posted a similar answer to mine less than a hour before me...lol. I do a couple of different things with it; or I wouldn't have posted it. Nice answer.Great minds think a like!!!
  • This is quite nice. I wish that I could have split the bounty between you and @ThomasInzina since you have similar answers that were almost simultaneous. You both confirmed my hunch that some sort of deep magic involving hWnd is possible.
  • @JohnColeman - Only shallow magic - if I could figure out how Word keeps the color formatting and a rich text box doesn't, that would be the deep magic...