VBA batch change single character in tooltip string

[Ironman]

Hi

Column L10:L49 contains a list of hyperlinks with tooltips.

The tooltips contain the following text string:

Go to Training 1981-1997 G1615

The 4 digits at the end are variable but the rest of the text is fixed.

I'd be grateful for some code I can run that will change the tooltip text so the G before the 4 digits is changed to an F.

Many thanks!

 

[Jeffrey Mahoney]

Something like this?

Sub EditScreenTip()
  Dim Cel As Range
  Dim Astr As String
  Dim X As Long
  
  For Each Cel In Selection
    On Error Resume Next
    Astr = Cel.Hyperlinks(1).ScreenTip
    If Astr <> "" Then
      For X = Len(Astr) To 1 Step -1
        If Mid(Astr, X, 1) = "G" Then
          Astr = Left(Astr, X - 1) & "F" & Mid(Astr, X + 1, 100)
          Cel.Hyperlinks(1).ScreenTip = Astr
          Exit For
        End If
      Next X
    End If
  Next Cel

End Sub

 

[Ironman]

Hi Jeff, thanks for helping me.

The code ran without any errors but it didn't amend any of the screentips - they're all still showing "Go to Training 1981-1997 Gxxxx"

 

[Jeffrey Mahoney]

Did you grab the code quickly. I edited it and posted another minute later.

 

[Ironman]

No, I only saw it about 5 mins ago.

 

[Jeffrey Mahoney]

Look for this line

Cel.Hyperlinks(1).ScreenTip

 

[Jeffrey Mahoney]

Cel.Hyperlinks(1).ScreenTip = Astr
Ugh, my phone!!

 

[Ironman]

Yes, that was in the code I copied.

 

[Jeffrey Mahoney]

Hmmm, it worked for me. Try this code. All errors were being suppressed. Are those cells locked?

Sub EditScreenTip()
  Dim Cel As Range
  Dim Astr As String
  Dim X As Long
  
  For Each Cel In Selection
    On Error Resume Next
    Astr = Cel.Hyperlinks(1).ScreenTip
         On Error Goto 0
    If Astr <> "" Then
      For X = Len(Astr) To 1 Step -1
        If Mid(Astr, X, 1) = "G" Then
          Astr = Left(Astr, X - 1) & "F" & Mid(Astr, X + 1, 100)
          Cel.Hyperlinks(1).ScreenTip = Astr
          Exit For
        End If
      Next X
    End If
  Next Cel

End Sub

 

[Ironman]

Aha, yes, the cells were locked and when I unchecked the boxes it works, that's brilliant, thanks a lot Jeff!

As a side point, I didn't know the cells were locked until I checked as I hadn't consciously locked them - I checked a load of random cells and the 'locked' box is checked - I guess that's the default?