Hyperlink to Photos When Moving Files

WMayo · Jun 8th 2011

Re: Hyperlink to Photos When Moving Files

Said it before and I'll say it again - YOU DA MAN!

That works PERFECTLY.......

I'll post it, along with a very healthy THANK YOU and REVIEW....

Cannot tell you how much I appreciate the help!

WMayo · Jun 8th 2011

Re: Hyperlink to Photos When Moving Files

SOLUTION!!

Attila is THE MAN.....

My original post involved a problem I was having with attaching photos - almost 1000 of them - to an excel report so that the recipients of the report could read the item on the report and "click" to view a photo of the problem. In the past I've used hyperlinks and embedding - but both of those pose issue as well as being very time-consuming.

I posted a question here regarding the issue - having NO knowledge or experience in VBA - and Attila came to my rescue. He spent almost 4 days working this issue with me, continuously writing and re-writing the code (we name our photos uniquely, so it posed some major challenges) until it worked.

My most heart-felt thank you's go to Attila.

Anyone having a similar problem, here's the code:

Code

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> Range("O1").Column Or IsEmpty(Target) Then Exit Sub
    Select Case Range("A" & Target.Row).Value
            Case "AR"
            ShowImage "Acetylene Room (AR)", Target
            Case "BP"
            ShowImage "Ballast Pump Room (BP)", Target
            Case "BPV"
            ShowImage "Ballast Pump Vent Room (BPV)", Target
            Case "BL1"
            ShowImage "Battery Locker 1 (BL1)", Target
            Case "BL2"
            ShowImage "Battery Locker 2 (BL2)", Target
            Case "BS"
            ShowImage "Bulk Storage Area (BS)", Target
            Case "BSV"
            ShowImage "Bulk Storage Room Vent (BSV)", Target
            Case "DF"
            ShowImage "Drill Floor (DF)", Target
            Case "HR"
            ShowImage "Heli Fuel System (HR)", Target
            Case "HL"
            ShowImage "Helideck (HL)", Target
            Case "MP"
            ShowImage "Moonpool (MP)", Target
            Case "MPR"
            ShowImage "Mud Pit Room (MPR)", Target
            Case "MPM"
            ShowImage "Mud Process Module (MPM)", Target
            Case "MRT"
            ShowImage "Mud Reserve Tank (MRT)", Target
            Case "MRV"
            ShowImage "Mud Reserve Tank Vent (MRV)", Target
            Case "OXY"
            ShowImage "OXY Room (OXY)", Target
            Case "PL"
            ShowImage "Paint Locker (PL)", Target
            Case "SS"
            ShowImage "Shale Shakers (SS)", Target
            Case "WT"
            ShowImage "Well Test Area (WT)", Target
    End Select
End Sub
Sub ShowImage(Folder As String, Target As Range)
Dim i As Integer
If IsNumeric(Mid(Range("D" & Target.Row), 1, 1)) And Not IsEmpty(Mid(Range("D" & Target.Row), 1, 1)) Then GoTo MultipleImages
    Shell ("c:\windows\system32\rundll32.exe C:\WINDOWS\system32\shimgvw.dll,ImageView_Fullscreen " & ThisWorkbook.Path & "\Photos\" & Folder & "\" & ImageName(Target))
Exit Sub
MultipleImages:
    For i = Range("D" & Target.Row).Value To i Step -1
        Shell ("c:\windows\system32\rundll32.exe C:\WINDOWS\system32\shimgvw.dll,ImageView_Fullscreen " & ThisWorkbook.Path & "\Photos\" & Folder & "\" & ImageName(Target, i))
    Next i
End Sub
Function ImageName(Target As Range, Optional ImageNo As Integer = 0) As String
If ImageNo > 0 Then GoTo MultipleImages
If IsEmpty(Range("D" & Target.Row)) Then
    ImageName = Range("A" & Target.Row).Value & Range("B" & Target.Row).Value & Range("C" & Target.Row).Value & ".jpg"
Else
    ImageName = Range("D" & Target.Row).Value & ".jpg"
End If
Exit Function
MultipleImages:
    ImageName = Range("A" & Target.Row).Value & Range("B" & Target.Row).Value & Range("C" & Target.Row).Value & " (" & ImageNo & ").jpg"
End Function

Display More

Cannot say enough about Atilla an the help I got here. I will definitely do some exploring / experimentation in VBA in the future, seeing the usefulness of it now!

Hyperlink to Photos When Moving Files

Participate now!

Share

Users Viewing This Thread