+ Reply to Thread
Results 1 to 23 of 23

Show and hide the pictures based on the value.

  1. #1
    Registered User
    Join Date
    10-02-2014
    Location
    Philippines
    MS-Off Ver
    MAC MS office 2019
    Posts
    89

    Show and hide the pictures based on the value.

    Hi,

    I need your help guys. I need this code for display the pictures based on the value. But the pictures link on the folder in computer. And also the pictures display in box in the next column. Based on the sample in below.

    Screen Shot 2018-05-23 at 10.17.49 PM.jpg

    And also, once I enter the value automatic display pictures in box and next value then show pictures automatic.

    Please note: I'm using mac and window.

    Please I need your help


    Thanks.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,551

    Re: Show and hide the pictures based on the value.

    Hi, welcome to the forum.
    If I understand you correctly you do not have any code yet?
    The screenshots you have attached are manual actions?
    Have you tried recording a macro for each action? Then you can edit it to your use.
    I am not a MAC user and I know MAC VBA differs some, but if you record the macro you can attache the code (in code blocks) and then one of us could certainly help.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    10-02-2014
    Location
    Philippines
    MS-Off Ver
    MAC MS office 2019
    Posts
    89

    Re: Show and hide the pictures based on the value.

    Hi Hans,

    Thanks for your reply.
    Yes, I don't have a code for that. That why I need your help. I'm not expert in programming.
    Regarding the screenshot, yes the manual actions.
    I try to recording a macro here the result the code

    Please Login or Register  to view this content.
    Hans, please help me to find this solution so that very easy to me about my work. Because I want to see automatic the picture from the box when I scan the value. Please note the picture connect from the computer or desktop.
    Last edited by jeffreybrown; 05-26-2018 at 06:39 PM. Reason: Removed full quote!

  4. #4
    Registered User
    Join Date
    10-02-2014
    Location
    Philippines
    MS-Off Ver
    MAC MS office 2019
    Posts
    89

    Re: Show and hide the pictures based on the value.

    Hi Hans,

    Thanks for your reply.
    Yes, I don't have a code for that. That why I need your help. I'm not expert in programming.
    Regarding the screenshot, yes the manual actions.
    I try to recording a macro here the result the code

    Please Login or Register  to view this content.
    Hans, please help me to find this solution so that very easy to me about my work. Because I want to see automatic the picture from the box when I scan the value. Please note the picture connect from the computer or desktop.

    here the pictures inside the folder of my computer.

    Screen Shot 2018-05-24 at 8.14.20 PM.jpg
    Last edited by jeffreybrown; 05-26-2018 at 06:40 PM. Reason: Removed full quote!

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,551

    Re: Show and hide the pictures based on the value.

    Okay, I'll work with that and post code.
    I'll have to do this on a Windows system but the code will be the same.
    The Cell with the product name, you just enetr a name or do you want to enter the name in the same cell?

  6. #6
    Registered User
    Join Date
    10-02-2014
    Location
    Philippines
    MS-Off Ver
    MAC MS office 2019
    Posts
    89

    Re: Show and hide the pictures based on the value.

    I want to enter the name in same cell (A1 - A10000 or more) and another column display or show the picture result when I enter the right name of picture.

    i suggest if possible can you use the image box tool from developer so the picture can display in image box?

    And also I need also additional code for "no picture available" if the name not correct.
    Last edited by jeffreybrown; 05-26-2018 at 06:40 PM. Reason: Removed full quote!

  7. #7
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,551

    Re: Show and hide the pictures based on the value.

    you say A1 - A10000, does this mean that the picture must be displayed next to the name?
    Can you explain the idea little, I don't imagine you want all 10000 pictures displayed at the same time.
    How are you triggering this?

  8. #8
    Registered User
    Join Date
    10-02-2014
    Location
    Philippines
    MS-Off Ver
    MAC MS office 2019
    Posts
    89

    Re: Show and hide the pictures based on the value.

    yes your correct the picture must be display next to the name. Mean hide and show the picture based on the name.
    example:
    A1 - apple - show picture
    next
    A2 - banana - show picture then the apple picture to be hide.

    Because my job is enter the name of each item then I want to see the picture of item in the image box if that correct based on the physical item.

    Every time I enter the name of item automatic show and hide the picture in image box.

    I hope you got my point.
    Last edited by jeffreybrown; 05-26-2018 at 06:41 PM. Reason: Removed full quote!

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,551

    Re: Show and hide the pictures based on the value.

    You'll have your reason to do it the way you want.
    This macro code should go into the Workheet's VBA and will be triggered by a right-click in Column A and if there is a value in it it will trigger.
    You will have to check path wbPath and the photopath thsi last one is the folder where the pictures will be found.
    I attached my Excel file as a sample and also a NOPHOTO.jpg for the PHOTO NOT AVAIBALE, plce this one in the images or picture folder




    Please Login or Register  to view this content.
    Attached Images Attached Images
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-02-2014
    Location
    Philippines
    MS-Off Ver
    MAC MS office 2019
    Posts
    89

    Re: Show and hide the pictures based on the value.

    Hi,

    Thanks for your help. I open your sample but the pictures not showing. Sorry I'm not good in programming.

    By the way, I have a question. where can i put the path folder?

    This is my path folder. /Users/jcsc83ph/Desktop/sample of my computer.
    Last edited by jeffreybrown; 05-26-2018 at 06:41 PM. Reason: Removed full quote!

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,551

    Re: Show and hide the pictures based on the value.

    If you read the code part you can see where the path goes

    Please Login or Register  to view this content.
    The variable wbPath uses the actual path where this workbook is in (I hope a MAC understands this too)
    If your photos are in another path that you make sure that the variable photoPath = "..." point to that correct path

    And it it is not showing then there is nothing in that path.

    Without a little programming knowledge, well I cannot build it all for you, you need to do some on your side, check Google read up on it.
    The basics are correct they work

  12. #12
    Registered User
    Join Date
    10-02-2014
    Location
    Philippines
    MS-Off Ver
    MAC MS office 2019
    Posts
    89

    Re: Show and hide the pictures based on the value.

    I try to run this in excel window here the result:
    Picture1.png

    And the code

    HTML Code: 
    Option Explicit
    
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Count <> 1 Or Target.Column <> 1 Or Target.Value = "" Then Exit Sub
        Dim wbpath      As String
        Dim photoPath   As String
        Dim wB          As Workbook
        Dim wS          As Worksheet
        Dim photoName   As String
        Dim photoFile   As String
        Dim Cell        As Range
        Dim rng         As Range
        Dim sh          As Shape
        
        Dim noPhoto     As String
        noPhoto = "NOPHOTO.jpg"
        
        Dim photoExt    As String
        photoExt = ".jpg"
        
        'Turn screen updating off. You won't see the client file being updated.
        Application.ScreenUpdating = True
        Set wB = ActiveWorkbook
        Set wS = wB.Worksheets("Sheet1")
        
       ' path to your folder
        wbpath = "G:\sample"
        wbpath = wbpath & Application.PathSeparator
        ' the path to the folder witl aal the photos
        photoPath = "G:\sample\"
        photoPath = wbpath & "images" & Application.PathSeparator
        
        Set Cell = ActiveCell
        If Not Cell.Column = 1 Or Len(Trim(Cell.Value)) = 0 Then Exit Sub
        photoName = Cell.Value
        Set rng = wS.Range("B" & Cell.Row)
        photoFile = photoName & photoExt
        GoSub placePhotoInSheet
        Err.Clear
        On Error GoTo 0
        Range("D1").Select
        Cancel = True
    Application.ScreenUpdating = True
    Exit Sub
        
    deleteAllShapes:
    For Each sh In wS.Shapes
        sh.Delete
    Next sh
    Return
        
    placePhotoInSheet:
    On Error Resume Next
    wS.Shapes(photoName).Select
    If Err.Number = 0 Then
        wS.Shapes(photoName).Visible = msoTrue
        Return
    End If
    GoSub deleteAllShapes
    rng.Select
    If Not Dir(photoPath & photoFile) = "" Then
        ActiveSheet.Pictures.Insert(photoPath & photoFile).Select
    ElseIf Not Dir(wbpath & noPhoto) = "" Then
        ActiveSheet.Pictures.Insert(wbpath & noPhoto).Select
    ElseIf Not Dir(photoPath & noPhoto) = "" Then
        ActiveSheet.Pictures.Insert(photoPath & noPhoto).Select
    Else
        Return
    End If
    With Selection.ShapeRange
        .Name = photoName
        .LockAspectRatio = msoTrue
        .Top = rng.Top
        .Left = rng.Left
        '.Width = 141.75
        .Height = 141.75
        .IncrementLeft 0.75
        .IncrementTop 0.75
    End With
    rng.Offset(0, -rng.Column + 1).Select
    Return
    
    End Sub
    another question if possible the right click command can remove? I mean every time I enter the name of item automatic show the picture in column b no need to press the right click.
    Last edited by jeffreybrown; 05-26-2018 at 06:41 PM. Reason: Removed full quote!

  13. #13
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,551

    Re: Show and hide the pictures based on the value.

    Yes, just remove it in the worksheets event but keep in mind that my macro is triggered by this.

    The name is case sensitive
    So make sure the photo name is the same case as what you enter

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,551

    Re: Show and hide the pictures based on the value.

    Please don't reply with quote.
    Just reply and type and copy and paste a section as quote if required, makes it easier to read

  15. #15
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,551

    Re: Show and hide the pictures based on the value.

    And this is sufficient for the path

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    10-02-2014
    Location
    Philippines
    MS-Off Ver
    MAC MS office 2019
    Posts
    89

    Re: Show and hide the pictures based on the value.

    Hi Han,

    Finally is work in imac and window.

    By the way i need only remove the right click command. I need only the automatic show the picture when I enter the name of each item. mean show and hide after the next name.

    here the code that working now.

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    10-02-2014
    Location
    Philippines
    MS-Off Ver
    MAC MS office 2019
    Posts
    89

    Re: Show and hide the pictures based on the value.

    Hi Han,

    Sorry I too much question. Because I need you for your help regarding your code. I need only to run the code without the right click command so that my work so easy to see the picture every time I enter the name in cell.

    Your code is great and perfect. I hope you can help me.

  18. #18
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,551

    Re: Show and hide the pictures based on the value.

    Your code modified, test to see if it works

    Please Login or Register  to view this content.

  19. #19
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Show and hide the pictures based on the value.

    without VBA
    it can be done with google translate

  20. #20
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    Re: Show and hide the pictures based on the value.

    If interested in a non-vba approach...
    HTH
    Regards, Jeff

  21. #21
    Registered User
    Join Date
    10-02-2014
    Location
    Philippines
    MS-Off Ver
    MAC MS office 2019
    Posts
    89

    Re: Show and hide the pictures based on the value.

    Now is ok..

    you're superhero.. Thanks you for your help.

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,895

    Re: Show and hide the pictures based on the value.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2019
    Posts
    6,551

    Re: Show and hide the pictures based on the value.

    jefferybrown's solultion without VBA is great, but have you checked the size the Excel file? the more pictures (depending on their resolution the bigger it gets and it's not dynamic in the sense you always have to manually import a picture set in the sheet, give it a name.
    The vba solution makes it dynamic in that sense that you can keep adding adding pictures / images to the folder and just type the name in the Excel sheet
    But I agree, no vba makes life easier for non-vba addicts

+ 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. Hide or Show rows based off value of cell in that row?
    By dancor31 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-17-2017, 01:38 PM
  2. [SOLVED] Hide or show an object based on a cell value
    By OpOrange in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-26-2016, 09:17 AM
  3. [SOLVED] Hide/show row based on cell value
    By zanguard80 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-11-2015, 03:06 PM
  4. VBA hide row based on pull down with button to toggle show/hide
    By myronr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2012, 06:07 PM
  5. Replies: 1
    Last Post: 04-11-2008, 02:02 AM
  6. Show/Hide Button for Pictures
    By aposatsk in forum Excel General
    Replies: 2
    Last Post: 08-23-2006, 10:30 AM

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