Auto-format Cell Based on Value with VBA

Question

I'm developing a database querying tool for my team and have had a small hiccup which I haven't been able to solve. This one's been bugging me for a while!

Problem: text of interest returned from the database is in Rich Text Format (with formatting tags). I'd like to have it either formatted (ideally) or in just plain text (not ideal). I know that Excel is capable of doing this, because all I have to do is click into the cell, and it formats automatically. I want to achieve this auto-formatting on the back end through VBA.

Example of queried text:

{\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcharset0 Tahoma;}}

{\*\generator Msftedit 5.41.21.2510;}\viewkind4\uc1\pard\lang1033\f0\fs16 Some text will be right here.\par   

}

Record Macro when I click into the cell: this does not provide a general solution. It shows all the formatting that was done, but this is specific to the cell.

ActiveCell.FormulaR1C1 = "Some text will be right here." & Chr(10) & "  "

This is not helpful to me.

I've tried a few things I've seen on the internet; none of which have worked. Specifically, selecting the cell I'd like to have formatted and throwing keystrokes (F2, Enter), Calculating cells, and maybe another solution or two.

What do y'all think?

score 0 · Accepted Answer · answered Mar 21 '20 at 18:09

Also I send keys as well keybd_event is more reliable than Sendkeys. This worked for me

Option Explicit

' https://stackoverflow.com/a/49065905/6600940
Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

' http://www.vbforums.com/showthread.php?277384-VB-Key-COnsts
Public Const VK_F2 = &H71
Public Const VK_RETURN = &HD

Sub ParseAllRange()
    Dim rngCell As Range
    Application.ScreenUpdating = False
    For Each rngCell In Range("B13:B19")
        rngCell.Select
        keybd_event VK_F2, 0, 0, 0
        keybd_event VK_RETURN, 0, 0, 0
        DoEvents
    Next
    Application.ScreenUpdating = True
End Sub

Auto-format Cell Based on Value with VBA

1 Answers1