Autocompletion and Icons

**Bowzerbro** · 07-15-2009, 03:27 AM

Supposebly impossible

**Andy Pope** · 07-15-2009, 06:21 AM

You can use event code behind the spreadsheet to cell changed cell content and insert image if required.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count = 1 Then
        If Not Union(Target, Range("B3:B10")) Is Nothing Then
            If StrComp(Target.Value, "HDD1", vbTextCompare) = 0 Then
                ActiveSheet.Shapes.AddPicture _
                            "c:\temp\temp.gif", _
                             True, True, Target.Left, Target.Top, _
                             Target.Width, Target.Height
            End If
        End If
    End If
    
End Sub

**Bowzerbro** · 08-03-2009, 02:52 AM

Originally Posted by Andy Pope

You can use event code behind the spreadsheet to cell changed cell content and insert image if required.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count = 1 Then
        If Not Union(Target, Range("B3:B10")) Is Nothing Then
            If StrComp(Target.Value, "HDD1", vbTextCompare) = 0 Then
                ActiveSheet.Shapes.AddPicture _
                            "c:\temp\temp.gif", _
                             True, True, Target.Left, Target.Top, _
                             Target.Width, Target.Height
            End If
        End If
    End If
    
End Sub

Sweet thanks, I'll try it out.
Is "case" possible in this code? To easier list more items. Or could I use a dynamic file-value so that it uses the entered text as file-name?
Ex. c:\temp\HDD1.gif would be used if [HDD1] was entered, and for all text entered within [] or maybe img[value]
I'm guessing I can figgure out how to use case in this senario, not sure if I can wrap my mind around the image location values, unless fixed for each case.
Thanks again,
Bb

**Andy Pope** · 08-03-2009, 04:11 AM

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strFilename As String
    
    If Target.Cells.Count = 1 Then
        If Not Union(Target, Range("B3:B10")) Is Nothing Then
            Select Case UCase(Target.Value)
            Case "HDD1"
                strFilename = "c:\temp\temp.gif"
            End Select
            
            If Len(strFilename) > 0 Then
                ActiveSheet.Shapes.AddPicture _
                            strFilename, _
                             True, True, Target.Left, Target.Top, _
                             Target.Width, Target.Height
            End If
        End If
    End If
    
End Sub