How to convert image URL paste action to IMAGE formula using VBA in Excel?

Question

Description:
I want to automatically convert pasted image URLs (format: https:\\xxx.com\xxx.jpg) into Excel's =IMAGE() formulas using VBA. Currently when I paste image URLs directly, they remain as plain text links. I need them to become displayed images through this transformation:

Original pasted content:
https:\\example.com\image.jpg

Desired result:
=IMAGE("https:\\example.com\image.jpg")

What I've tried:

1. Basic Worksheet_Change event triggers
2. String replacement using Target.Value manipulation

Issues:

• URLs don't automatically convert when pasted
• Manual processing works but defeats automation purpose
• Unclear how to handle different URL formats/cases

Code Attempt:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Left(Target.Value, 7) = "http://" Or Left(Target.Value, 8) = "https://" Then
        Application.EnableEvents = False
        Target.Formula = "=IMAGE(""" & Target.Value & """)"
        Application.EnableEvents = True
    End If
End Sub


1. Basic Worksheet_Change event triggers  
2. String replacement using `Target.Value` manipulation  

- URLs don't automatically convert when pasted  
- Manual processing works but defeats automation purpose  
- Unclear how to handle different URL formats/cases  

Answer 1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim url As String

    ' Ensure only one cell is changed
    If Target.Count > 1 Then Exit Sub
    
    ' Turn off events to prevent infinite loops
    Application.EnableEvents = False
    
    ' Only proceed if the cell contains non-error, non-empty text
    If Not IsError(Target.Value) Then
        If VarType(Target.Value) = vbString And Trim(Target.Value) <> "" Then
            url = Trim(Target.Value)
            ' Replace any backslashes with forward slashes
            url = Replace(url, "\", "/")
            
            ' Check if the text starts with a valid protocol (case-insensitive)
            If LCase(Left(url, 7)) = "http://" Or LCase(Left(url, 8)) = "https://" Then
                ' Avoid re-processing cells that already contain the IMAGE formula
                If Left(Target.Formula, 7) <> "=IMAGE(" Then
                    Target.Formula = "=IMAGE(""" & url & """)"
                End If
            End If
        End If
    End If
    
    ' Re-enable events
    Application.EnableEvents = True
End Sub