asked on

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!

Saqib Husain

ActiveCell.Characters(Start:=11, Length:=8).Font.FontStyle = "Bold"

codequest

ASKER

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.

Patrick Matthews

codequest,

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

Patrick

Saqib Husain

Supposing the cell has a 20 character string then the command will bold characters 11 through 18

Saqib Husain

Sorry ignore my last post. Misunderstood your comment.

krishnakrkc

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

krishnakrkc

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

codequest

ASKER

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.

codequest

ASKER

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.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

codequest

ASKER

Yes, but your qualifications weren't "SUPER Genius" in Excel.... :-)

Hmmm. Sigh. Reality sinks in. Darn!

OK, thanks for the answer!

Patrick Matthews