+ Reply to Thread
Results 1 to 5 of 5

Adding Pictures From Drop Down List

Hybrid View

  1. #1
    Registered User
    Join Date
    12-10-2013
    Location
    Louisiana
    MS-Off Ver
    Excel 2010
    Posts
    13

    Adding Pictures From Drop Down List

    Hi All,

    I'm a beginner to VBA coding, I copy a code from excel forum to add pictures from a drop down list & it works well but I need to add an additional drop list box & picture to a different area on the spread sheet. I would appreciate any help someone could have on this matters - please see code below:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vCells As Variant
    
        On Error GoTo leave
        If Target.Column > 1 Then GoTo leave
        
        'list of addresses for cells with user selection lists
        'NOTE: the $ are necessary!
        vCells = Array("$A$92", "$A$94", "$A$96", "$A$98")
        
        'did user change one of the cells we are watching?
        For n = 0 To UBound(vCells)
            If Target.Address = vCells(n) Then
                'yes ... call the routine that shows picture
                Call showPic(Target)
                Exit For
            End If
        Next n
    
    leave:
    End Sub
    
    Private Sub showPic(vCell As Range)
    Dim oPic As Shape, oPic2 As Shape
    Dim ws As Worksheet
    Dim searchRng As Range
    Dim searchFor As String
    Dim matchRow As Long
    Dim picName As String
    
        Application.ScreenUpdating = False
    
        'if there is a picture related to this cell, delete it
        On Error Resume Next
        Set oPic = Me.Shapes("Pic" & vCell.Row)
        If Err Or oPic Is Nothing Then
            Err.Clear
        Else
            oPic.Delete
        End If
    
        On Error GoTo leave
        'read user's selection from list
        searchFor = vCell.Value
        'define worksheet containing list, picture names, and pictures
        Set ws = ThisWorkbook.Worksheets("Options")
        'search column A (column 1) for match to user's selection
        Set searchRng = ws.Columns(1)
        matchRow = 0
        On Error Resume Next
        matchRow = WorksheetFunction.Match(searchFor, searchRng, 0)
        If Err Or matchRow = 0 Then
            Err.Clear
            GoTo leave 'no match, exit
        End If
        
        On Error GoTo leave
        'if we got here, we have found a match for the user's selection
        'find name of picture we are looking for
        picName = ws.Range("B" & matchRow)
        
        For Each oPic In ws.Shapes
            If oPic.Name = picName Then
                oPic.Copy
                Me.Paste
                Set oPic2 = Me.Shapes(Selection.Name)
                oPic2.Name = "Pic" & vCell.Row
                oPic2.Top = vCell.Top
                oPic2.Left = Me.Cells(vCell.Row, 10).Left
                Exit For
            End If
        Next oPic
    
    leave:
        Application.ScreenUpdating = True
    End Sub
    Last edited by TDeRanger; 12-17-2013 at 05:23 AM. Reason: Add code tags, sry

  2. #2
    Registered User
    Join Date
    12-10-2013
    Location
    Louisiana
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Adding Pictures From Drop Down List

    Two days straight trying to solve my problem, I hate to bother someone else with it but please I
    need some help!!

    Please see attach file with call out boxes explaining what I'm trying to do - any help would be
    deeply appreciated
    Attached Files Attached Files

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Pictures From Drop Down List

    Hi TDeRanger

    I'm only guessing what you want on Sheet sGames...try the Code in the attached...
    Attached Files Attached Files
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Registered User
    Join Date
    12-10-2013
    Location
    Louisiana
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Adding Pictures From Drop Down List

    Excellent John!!
    My problem final solve - many thanks

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Adding Pictures From Drop Down List

    You're welcome...glad I could help. Thanks for the Rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Select Pictures In Drop Down List
    By GERZO in forum Excel - New Users/Basics
    Replies: 12
    Last Post: 02-18-2013, 02:12 PM
  2. Linking pictures from multiple drop down list
    By philips_cody in forum Excel General
    Replies: 0
    Last Post: 04-19-2012, 08:46 AM
  3. [SOLVED] linking pictures to a drop down list then changing
    By philips_cody in forum Excel General
    Replies: 2
    Last Post: 03-29-2012, 02:20 PM
  4. Excel 2007 : Link Pictures to Drop Down list
    By strat95 in forum Excel General
    Replies: 3
    Last Post: 12-09-2009, 08:45 PM
  5. [SOLVED] Pictures.Visible question (after adding many pictures, they stop disappearing)
    By Abe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2006, 01:35 PM

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