locked
Attempting to use a Macro, or VBA to place a picture in a cell based on another cell's value RRS feed

  • Question

  • I have found several sites that cover this, but none of them really cut it for me, or at least don't explain it down to a level that I understand.  (I am a VB and Macro using noob, so please be gentle).

    I have a workbook with several sheets.  On Sheet1!, I have several tables that pull in status references from other sheets within the workbook.  On Sheet2!, I have a table that has four columns; Column1 = IconName, Column2 = Icon, Column3 = Image Name, Column4 = Reference location of icon (i.e. SheetName!$ColNum$RowNum, or Sheet2!$C$4, etc.).  I have also declared the values of each icon in the Name Manager, and each image is named appropriately within the sheet (Instead of Picture1, it is named GenUp, or something similar).

    On Sheet1! there is a table that has; title of item, state of the item, translation of state (for icon reference) and a blank cell for the icon to exist.

    I have one macro that sorts these statuses based on a predetermined sorting list within the sheet. 

    What I would like is to add to, or create an additional Macro, or VBA script (whichever works smoother), to place the appropriate icon in the blank cell for each of these tables.

    So, one table on Sheet1! looks like this:

    Icon	WorkItem	                                   Status	Report State
    	14005 - LOC Item - French	                   Planned	TeamXPlanned
    	13687 - Remove dependency                          In Progress	TeamXProgress
    	13927 - Remove dependency - LiveSession 	   In Progress	TeamXInProgress
    	13505 - Fw Upgrade - 1.9.1	                   Blocked	TeamXBlocked
    	13930 - Handle content version and updates	   Closed	TeamXClosed
    

    and the table on Sheet2! looks like this:

    Lookup	    Icon	ImageName	Reference To Icon
    GenUp		        GenUp	        IconSets!$C$3
    GenEqual		GenEqual	IconSets!$C$4
    GenDown		        GenDown	        IconSets!$C$5
    GenPlanned		GenPlanned	IconSets!$C$6
    GenInProgress		GenInProgress	IconSets!$C$7
    GenBlocked		GenBlocked	IconSets!$C$8
    

    (obviously, I cannot paste the icon in this format).

    Can someone help out on this?

    Wednesday, July 19, 2017 8:17 PM

All replies

  • Hi NmaticPwrHammer,

    I find that your explanation is little confusing.

    you had mentioned that ,"What I would like is to add to, or create an additional Macro, or VBA script (whichever works smoother), to place the appropriate icon in the blank cell for each of these tables."

    I don't know what kind of icon you are using.

    here below you can see example of adding icons to worksheet may help you.

    Option Explicit
    
    Sub TestAddIconSet()
      Dim i As Integer
      Dim rng As Range
      For i = 1 To 20
        ' Set up ranges
        Set rng = SetupRange(i)
        Select Case i
          Case 1
            SetUpIconSet rng, xl3Arrows
          Case 2
            SetUpIconSet rng, xl3ArrowsGray
          Case 3
            SetUpIconSet rng, xl3Flags
          Case 4
            SetUpIconSet rng, xl3Signs
          Case 5
            SetUpIconSet rng, xl3Stars
          Case 6
            SetUpIconSet rng, xl3Symbols
          Case 7
            SetUpIconSet rng, xl3Symbols2
          Case 8
            SetUpIconSet rng, xl3TrafficLights1
          Case 9
            SetUpIconSet rng, xl3TrafficLights2
          Case 10
            SetUpIconSet rng, xl3Triangles
          Case 11
            SetUpIconSet rng, xl4Arrows
          Case 12
            ' Reverse the order on this one:
            SetUpIconSet rng, xl4ArrowsGray, True
          Case 13
            SetUpIconSet rng, xl4CRV
          Case 14
            SetUpIconSet rng, xl4RedToBlack
          Case 15
            SetUpIconSet rng, xl4TrafficLights
          Case 16
            SetUpIconSet rng, xl5Arrows
          Case 17
            ' Reverse the order on this one:
            SetUpIconSet rng, xl5ArrowsGray, True
          Case 18
            SetUpIconSet rng, xl5Boxes
          Case 19
            SetUpIconSet rng, xl5CRV
          Case 20
            SetUpIconSet rng, xl5Quarters
        End Select
      Next i
    End Sub
    
    Function SetupRange(col As Integer) As Range
        ' Set up ranges, filled with numbers from 1 to 10.
        Dim rng As Range
        Set rng = Range(Cells(1, col), Cells(10, col))
       
        Dim rng1 As Range
        Set rng1 = Cells(1, col)
        rng1.Value = 1
    
        Dim rng2 As Range
        Set rng2 = Cells(2, col)
        rng2.Value = 2
       
        Range(rng1, rng2).AutoFill Destination:=rng
        Set SetupRange = rng
    End Function
    
    Sub SetUpIconSet(rng As Range, iconSet As XlIconSet, Optional ReverseOrder As Boolean = False)
        ' Set up an icon set for the supplied range.
        rng.FormatConditions.Delete
        Dim isc As IconSetCondition
        Set isc = rng.FormatConditions.AddIconSetCondition
        With isc
            ' If specified, show the icons in the reverse ordering:
            .ReverseOrder = ReverseOrder
            .ShowIconOnly = False
            ' Select the requested icon set:
            .iconSet = ActiveWorkbook.IconSets(iconSet)
        End With
    End Sub
    
    
    

    Output:

    you can modify the code as per your requirement.

    Reference:

    Displaying Icon Sets in Excel

    if you have any further question then you can let us know about that, we try to provide you further suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 20, 2017 1:22 AM
  • Hi Deepak,

    I should have been a little more clear.  The images that I have on Sheet2! are what I am hoping to place in a cell on Sheet1!, based on another cell's content on Sheet1!. 

    The icon sets that you show above are integrated as part of the KPIs that can be used within excel.  The problem I have with using this integrated portion is that I have 5 images to show, per team, and the color's change for each team. 

    Below is my Icon Image Table from Sheet2!

    From here, I would like to place the appropriate image in the first column of my table on Sheet1!, based on the data in the last column of the same table.  (I should also mention that each team has a separate table on Sheet1!)

    Does that make a little better sense?  I don't want 3 kpi icons to choose from, I would like to use my own icon images.  I would like to use VBA or a Macro, because there are over 240 rows where this data is, and while I know that I can use a redirect and vlookup formula, the worksheet begins to bog down when I do this, and it also puts it into place that this is no longer scalable.


    Jason Wohlfeil

    Thursday, July 20, 2017 10:00 PM
  • Hi NmaticPwrHammer,

    I understand that you have your own images that you had added on Sheet2 and based on condition you want to add specific image to Sheet1.

    below I create one example that may help you.

    so first I added the images in Sheet 2 and give them a name to them.

    then I match the name on sheet change event and copy paste picture on Sheet 1.

    Private Sub Worksheet_Change(ByVal Target As Range)
     Dim picName As String
        If Target.Column = 1 Then
            picName = Target.Value
            Copy_Images picName
        End If
    
    End Sub
    
    
    Private Sub Copy_Images(imageName As String)
        Dim sh As Shape
        For Each sh In Sheets(2).Shapes
            If sh.Name = imageName Then
                sh.Copy
                Sheets(1).Pictures.Paste
            End If
        Next
    End Sub

    Output:

    so you can try to refer the example and try to implement in your code.

    you can also try to save all images in folder and directly add to sheet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 21, 2017 6:44 AM
  • That get's me part of the way there Deepak,

    Last thing would be, how do I get the image to paste in one cell, based on another.

    Right now, as a worksheet macro, this paste goes to the next cell selected.  So if I Type a name in column1 and tab so that the cell selected changes to column2, then the image pastes in column2.  If I hit enter, the selector moves to the next row in column1, and the image pastes there.

    Additionally, none of the information on this sheet is manually updated.  It is read from other sheets, and those other sheets are updated daily.  Will this result in (potentially) several images being pasted over one another?  I'm assuming I need to add a "clear image" function as well, correct?

    Thanks in advance too.  This is getting me 80% closer.


    Jason Wohlfeil

    Friday, July 21, 2017 5:32 PM
  • Hi NmaticPwrHammer,

    you had mentioned that,"Right now, as a worksheet macro, this paste goes to the next cell selected.  So if I Type a name in column1 and tab so that the cell selected changes to column2, then the image pastes in column2.  If I hit enter, the selector moves to the next row in column1, and the image pastes there."

    the effect will be same with the code above.

    if you write the name of the image and press 'TAB' key then it will add image in column 2 and when you will press Enter key then it will move the cursor to next row. so no need to change anything in code.

    other thing you had asked,"Additionally, none of the information on this sheet is manually updated.  It is read from other sheets, and those other sheets are updated daily.  Will this result in (potentially) several images being pasted over one another?  I'm assuming I need to add a "clear image" function as well, correct?"

    it is important to give name to the picture.

    whether it is done by you or done by code.

    so you need to make sure that the image get unique name each time, so that there is no issue occur for overwriting.

    if the name is same then over writing may occur.

    I can see that you already have the name of each image.

    you just need to assign that name to image in code.

    below is an example to change the name of image.

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        ActiveSheet.Shapes.Range(Array("banana")).Select
        Selection.ShapeRange.Name = "banana1"
        Selection.Name = "banana1"
    End Sub
    

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, July 24, 2017 2:15 AM