Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    presenting a picture for the active row Excel 2013

    hi,

    i would like to present a picture from a given directory based on a row i am on.
    in other words, i build a small DB with rows as records. one of the fields is a link pointing to specific JPG file in a specific directory. i want to present only the picture relevant to the row i am on. i would like also to control the size of the picture (i.e. to have a "window" in which the relvant picture would be presented).

    is it possible in Excel 2013?

    thanks for help,

    tsr1955

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    tsr,

    Here is a simple database with the desired images to show in column D. Column D is just text with no hyperlink. When the user clicks on a cell in column D, the corresponding picture will open in a form called PictureFrame that can be resized as you wish by dragging the corners. It can also be moved anywhere on the screen. Because the modal property of the form is set to false, you can continue to work while the form is open. Clicking on othe cellsin Column D will update the form with the new pic.

    picts.png

    In the sheet module:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        LastRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
        If Not Intersect(Target, Range("D2:D" & LastRow)) Is Nothing Then
            PictureFrame.Hide
            PictureFrame.Show
        End If
    End Sub
    The PictureFrame Form has no controls. The picture will load as a background image

    In the Form module:
    Code:
    Private Sub UserForm_Activate()
        MakeFormResizable
        Row = ActiveCell.Row
        With Worksheets("Sheet2")
        Me.Picture = LoadPicture(.Cells(Row, 1))
        End With
    End Sub
    The full paths for the images reside on a hidden sheet (Sheet 2) in column A in the corresponding row as on sheet 1. Of course, for your pictures to be visible, you will have to change the paths for your images else you will receive a message that it cannot find the file.

    picts2.png

    The meat and potatoes is some code written by Leith Ross that enables the form window to be resized

    In a standard module:
    Code:
    Private Declare Function GetForegroundWindow Lib "User32.dll" () As Long
    
    Private Declare Function GetWindowLong _
      Lib "User32.dll" Alias "GetWindowLongA" _
        (ByVal hWnd As Long, _
         ByVal nIndex As Long) _
      As Long
                   
    Private Declare Function SetWindowLong _
      Lib "User32.dll" Alias "SetWindowLongA" _
        (ByVal hWnd As Long, _
         ByVal nIndex As Long, _
         ByVal dwNewLong As Long) _
      As Long
    
    Private Const WS_THICKFRAME As Long = &H40000
    Private Const GWL_STYLE As Long = -16
    
    Public Sub MakeFormResizable()
    
      Dim lStyle As Long
      Dim hWnd As Long
      Dim RetVal
      
        hWnd = GetForegroundWindow
      
        'Get the basic window style
         lStyle = GetWindowLong(hWnd, GWL_STYLE) Or WS_THICKFRAME
    
        'Set the basic window styles
         RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)
    
    End Sub
    Remember to change then path for each image on sheet 2 on the corresponding row then click away.

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2014-12-10 at 21:25.

  3. #3
    New Lounger
    Join Date
    Dec 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    thanks, maudibe! will try it today. regards, tsr

    QUOTE=Maudibe;977783]tsr,

    Here is a simple database with the desired images to show in column D. Column D is just text with no hyperlink. When the user clicks on a cell in column D, the corresponding picture will open in a form called PictureFrame that can be resized as you wish by dragging the corners. It can also be moved anywhere on the screen. Because the modal property of the form is set to false, you can continue to work while the form is open. Clicking on othe cellsin Column D will update the form with the new pic.

    picts.png

    In the sheet module:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        LastRow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
        If Not Intersect(Target, Range("D2:D" & LastRow)) Is Nothing Then
            PictureFrame.Hide
            PictureFrame.Show
        End If
    End Sub
    The PictureFrame Form has no controls. The picture will load as a background image

    In the Form module:
    Code:
    Private Sub UserForm_Activate()
        MakeFormResizable
        Row = ActiveCell.Row
        With Worksheets("Sheet2")
        Me.Picture = LoadPicture(.Cells(Row, 1))
        End With
    End Sub
    The full paths for the images reside on a hidden sheet (Sheet 2) in column A in the corresponding row as on sheet 1. Of course, for your pictures to be visible, you will have to change the paths for your images else you will receive a message that it cannot find the file.

    picts2.png

    The meat and potatoes is some code written by Leith Ross that enables the form window to be resized

    In a standard module:
    Code:
    Private Declare Function GetForegroundWindow Lib "User32.dll" () As Long
    
    Private Declare Function GetWindowLong _
      Lib "User32.dll" Alias "GetWindowLongA" _
        (ByVal hWnd As Long, _
         ByVal nIndex As Long) _
      As Long
                   
    Private Declare Function SetWindowLong _
      Lib "User32.dll" Alias "SetWindowLongA" _
        (ByVal hWnd As Long, _
         ByVal nIndex As Long, _
         ByVal dwNewLong As Long) _
      As Long
    
    Private Const WS_THICKFRAME As Long = &H40000
    Private Const GWL_STYLE As Long = -16
    
    Public Sub MakeFormResizable()
    
      Dim lStyle As Long
      Dim hWnd As Long
      Dim RetVal
      
        hWnd = GetForegroundWindow
      
        'Get the basic window style
         lStyle = GetWindowLong(hWnd, GWL_STYLE) Or WS_THICKFRAME
    
        'Set the basic window styles
         RetVal = SetWindowLong(hWnd, GWL_STYLE, lStyle)
    
    End Sub
    Remember to change then path for each image on sheet 2 on the corresponding row then click away.

    HTH,
    Maud[/QUOTE]

Tags for this Thread

Posting Permissions

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