Identify Which characters are selected

I'd like to build a macro that I can put in a button that would do this:

for only selected characters in a cell (i.e. partial cell content is selected), apply formatting:  color, bold, etc.

The idea is to build a "quick format" custom toolbar, with which a user (myself in this case) could select a word or a phrase in a cell, and then click on a button in the custome toolbar (hovering near the point of action) and have the word or phrase turn red or bold (or both, or green, etc.)   I want to reduce the mouse movements:   go up to the formatting tool bar, click on color menu carefully, select color carefully.   Instead, just select the text and click on the formatting treatment I want.

I don't see how to identify which characters in a cell are the selected characters.   The rest I can figure out from recorded macros.  (I wouldn't complain about a complete solution :-0)

Any help with this would be appreciated.

Thanks!
LVL 2
codequestAsked:
Who is Participating?
 
Patrick MatthewsCommented:
>>Maybe it's not possible?  Code processor seems to be dead when the cursor is inside a cell.  

That is exactly what I wrote in http:#a35344786

Patrick
0
 
Saqib Husain, SyedEngineerCommented:
ActiveCell.Characters(Start:=11, Length:=8).Font.FontStyle = "Bold"
0
 
codequestAuthor Commented:
Thanks for the input.   That shows how to set the values.  I need to know what the "Start: " and "Length:  " values would be, based on what I select in the cell.

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Patrick MatthewsCommented:
codequest,

What you are asking for cannot be done in VBA: you cannot call code while a cell is in edit mode.

Patrick
0
 
Saqib Husain, SyedEngineerCommented:
Supposing the cell has a 20 character string then the command will bold characters 11 through 18
0
 
Saqib Husain, SyedEngineerCommented:
Sorry ignore my last post. Misunderstood your comment.
0
 
krishnakrkcCommented:
Hi,

Try this.

Right click any cell > select the word to format.


Kris
'In Workbook Module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("Cell").Reset
    On Error GoTo 0
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    Dim cmdBBtn         As CommandBarButton
    Dim TextToFormat    As String
    Dim x, i            As Long
    
    On Error Resume Next
    Application.CommandBars("Cell").Reset
    If Len(ActiveCell.Value) = 0 Then Exit Sub
    
    TextToFormat = ActiveCell.Text
    
    Application.CommandBars("Cell").Reset
    With Application.CommandBars("Cell").Controls
        With .Add
            .BeginGroup = True
            .Caption = "Reset Active Cell"
            .OnAction = "ResetActiveCell"
        End With
        With .Add
            .Caption = "Format Everything"
            .OnAction = "FormatEverything"
        End With
        x = Split(TextToFormat, " ")
        For i = 0 To UBound(x)
            With .Add
                .Caption = x(i)
                .OnAction = "BoldAndColor"
                .Tag = i + 1
            End With
        Next
    End With
    On Error GoTo 0

End Sub

Open in new window

'In a standard module


Sub BoldAndColor()
    
    Dim aCell   As Range
    Dim Pos     As Long
    Dim l       As Long
    Dim i       As Long, x
    Dim txt     As String
    Dim cTag    As Long
    
    
    Set aCell = ActiveCell
    x = Split(aCell.Text, " ")
    
    cTag = CLng(Application.CommandBars.ActionControl.Tag)
    
    txt = x(cTag - 1)
    
    Pos = InStr(1, aCell.Text, txt, 1)
    l = Len(txt)
    
    With aCell.Characters(Pos, l)
        .Font.Bold = True
        .Font.Color = 255
    End With
    
End Sub

Sub ResetActiveCell()
    With ActiveCell
        .Font.Bold = False
        .Font.Color = 0
    End With
End Sub
Sub FormatEverything()
    With ActiveCell
        .Font.Bold = True
        .Font.Color = 255
    End With
End Sub

Open in new window

0
 
krishnakrkcCommented:
Hi,

Replace the BoldAndColor macro with the following


Kris
Sub BoldAndColor()
    
    Dim aCell   As Range
    Dim Pos     As Long
    Dim l       As Long
    Dim i       As Long, x
    Dim txt     As String
    Dim cTag    As Long
    
    
    Set aCell = ActiveCell
    x = Split(aCell.Text, " ")
    
    cTag = CLng(Application.CommandBars.ActionControl.Tag)
    
    txt = x(cTag - 1)
    
    For i = 0 To cTag - 1
        Pos = Pos + Len(x(i)) + 1
    Next
    l = Len(txt)
    
    With aCell.Characters(Pos - l, l)
        .Font.Bold = True
        .Font.Color = 255
    End With
    
End Sub

Open in new window

0
 
codequestAuthor Commented:
Thanks for the input.   Impressive piece of code.  I got it to work, mostly.  However, it's not quite what I was hoping for.

I'd like to do a standard double-left click on the cell, sweep-select some text, and hit a button to change the formatting.

Maybe it's not possible?  Code processor seems to be dead when the cursor is inside a cell.  

If it can't be done, for some known reason, that would be good to confirm, too.
0
 
codequestAuthor Commented:
The other issue with the approach above is that it assumes one formatting style. I'm looking for bold-red, bold-black, not bold-red, not-bold black, strikethrough, and gray.

So the right click menu item approach, it would seem, would be overly busy, and remove the gain in reduced guesture.
0
 
codequestAuthor Commented:
Yes, but your qualifications weren't "SUPER Genius" in Excel.... :-)

Hmmm.    Sigh.    Reality sinks in.   Darn!

OK, thanks for the answer!
0
 
Patrick MatthewsCommented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.