Results 1 to 5 of 5

How to simplify Target.Address?

Threaded View

  1. #1
    Registered User
    Join Date
    04-21-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    How to simplify Target.Address?

    Hi everyone,

    I am following this forum for long time and found many times solution.
    Now is time for my question. I could not find answer anywhere.

    I have got list of products in column DK and pictures names in column DL (where picture name is =DK&".jpg")
    All the pictures are stored in P:\General\GADD\PICTURES\

    Pictures are displaying in comment window.
    Everything is working great, but I have got more than 400 lines of products.

    How simplify my code?
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        
            If Target.Address = "DK2" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL2").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
            If Target.Address = "DK3" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL3").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
            If Target.Address = "DK4" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL4").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
            If Target.Address = "DK5" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL5").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
            If Target.Address = "DK6" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL6").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
            If Target.Address = "DK7" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL7").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
            If Target.Address = "DK8" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL8").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
            If Target.Address = "DK9" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL9").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
            If Target.Address = "DK10" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL10").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
            If Target.Address = "DK11" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL11").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
            If Target.Address = "DK12" Then
        NewPic = "P:\General\GADD\PICTURES\" & Range("DL12").Value
        Target.Comment.Shape.Fill.UserPicture NewPic
        End If
        
    End Sub

    Thanks for any reply
    Last edited by Leith Ross; 04-21-2012 at 03:53 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1