Run macro on text selected while editing a cell

[Trevor_S]

When editing a cell's contents, you can "select" (highlight) some of the text within that cell. Is there an object in VBA that lets you reference the selected text within the active cell?

The reason is that I have a spreadsheet where some cells contain names. The names need to be replaced by initials to anomalyse the spreadsheet before the it is sent externally. I've written the macro at the end of this post, which goes through each cell in the selected range, replacing every word by its initial and an underscore. That's fine where the cell only contains names. But for cells that contain other text too, I want to be able to:
- Click into the cell with the mouse,
- Highlight the words within the text that I want to change,
- Run a macro that only converts the selected words into initials and underscores and leaves the other words unchanged?
There will be any number of names in the spreadsheet, so a simple find/replace wouldn't work.

Is this even possible? Or can you only reference cells, rather than selected text within them?

This is my macro for converting the contents of entire cells in a range:

For Each cell In Selection

Anon = Left(cell.Value, 1) & "_"
SpaceChar = 0
ErrorCheck = 0

Do
On Error GoTo ErrorHandler
SpaceChar = Application.WorksheetFunction.Search(" ", cell.Value, SpaceChar + 1)
On Error GoTo 0
If ErrorCheck = 1 Then Exit Do
Anon = Anon & Mid(cell.Value, SpaceChar, 2) & "_"
Loop

cell.Value = Anon

Next cell

Exit Sub

ErrorHandler:
ErrorCheck = 1
Resume Next

Thanks!

 

[Fluff]

As far as I know, you cannot run a macro whilst in edit mode on the sheet.

 

[Trevor_S]

Thanks, I thought that might be the case. I tried attaching a macro to a button and clicking on the button while in edit mode. The macro ran, but appeared to force Excel out of edit mode first.

In case it's of use to anyone else, the workaround that I've done is to copy and paste the column of cells into Word, run the macro below, then copy and paste the results back into Excel. Note that this won't work if any of the Excel cells contain a line feed character, as the contents will split into separate cells when pasted back into Excel.

The Word macro is:

Anon = Left(Selection.Text, 1) & "_"
Do
  SpaceChar = InStr(SpaceChar + 1, Selection.Text, " ")
  If SpaceChar = 0 Then Exit Do
  If Len(Selection.Text) = SpaceChar Then Exit Do
  Anon = Anon & Mid(Selection.Text, SpaceChar, 2) & "_"
Loop
Selection.Text = Anon

 

[ShouldntBeTinkering]

In case it's of use to anyone else, the workaround that I've done is to copy and paste the column of cells into Word, run the macro below, then copy and paste the results back into Excel. Note that this won't work if any of the Excel cells contain a line feed character, as the contents will split into separate cells when pasted back into Excel.

One improvement on this approach that I've used before is to replace (Word-side) each line break with a unique string of characters such as "$$$$$$", so that it can be handed back to Excel without splitting to separate cells. Once the data is back in Excel, each instance of the character string can be replaced with chr(10). Adds some extra steps but preserves the data a little better.