Silks URL

[eclipse]

When adding additional columns in Excel and adding Silks URL, how do you actually get it to display the silks image? Does it have to linked externally?

[osknows]

The code below will show the silks over a cell after the market changes. Just change columns 25/26 (columns Y/Z) to be whatever cell the URL is in and where you want the image to show.

Code: Select all

Option Explicit

Dim currentMarket As String
Dim imageImported As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngArray() As Variant
Dim i As Long
Const urlCol As Long = 25 'Column Y
Const picCol As Long = 26 'Column Z
Dim pic As object 'Dim pic As Picture
Dim shape As object 'Dim shape As shape

If Target.Columns.Count <> 16 Then Exit Sub

    Application.EnableEvents = False
    With Target.Parent
   
        rngArray = .Range("A1:BU55").Value2
       
        If rngArray(1, 1) <> currentMarket Then
            currentMarket = rngArray(1, 1)
            imageImported = False
           
            'Delete images
            For Each shape In .Shapes
                If Left(shape.Name, 6) = "Jockey" Then
                     shape.Delete
                End If
            Next shape
       
        End If
       
        If imageImported = False Then
            'Add images
            For i = 5 To Target.Rows.Count
                If InStr(1, rngArray(i, urlCol), "http://", vbTextCompare) > 0 Then
                    Set pic = .Pictures.Insert(rngArray(i, urlCol))
                    pic.ShapeRange.LockAspectRatio = msoFalse
                    pic.Width = .Cells(i, picCol).Width
                    pic.Height = .Cells(i, picCol).Height
                    pic.Top = .Cells(i, picCol).Top
                    pic.Left = .Cells(i, picCol).Left
                    pic.Name = "Jockey" & .Cells(i, picCol).Address
                    imageImported = True
                End If
            Next i
        End If
       
    End With
    Application.EnableEvents = True
   
End Sub

[eclipse]

Many thanks for that.