#NAME Error in Image hyberlink

[Ramadan]

I'm working on office 2021 and I have a table for the cleints data where i need to store each client's ID in external folder with the same number of the client number in the table in column "B" like 1, 2, 3. 77, 189 etc... with a link in the table column "O" for that image so that I can display the client's ID from the table once click on the that hyberlink

When I faced that before in another sheet, a friend suggested to add this below function in VBA Module and to create the formula in the table column "O" to show to image link and it's ok working perfectly

Now I coppied the same function to the new sheet VBA module and also the formula with changing the folder path but it gives me #NAME error

this is the formula that I use

=HYPERLINK(imgPath("D:\Desktop\ClientsIDs",TEXT(B8,"000")),B8)

here is the function in the modue

Function imgPath(path, imgNum As String)
    Dim imgNameWithExtension As String
    imgNameWithExtension = Dir(path & imgNum & ".*") 'Using multiple wildcards character (*)to get any extension.
        If imgNameWithExtension <> "" Then
        imgPath = path & imgNameWithExtension
    Else
        imgPath = CVErr(xlErrName) 'Dir() returns a zero-length string ("") if file wasn't found, in which case imgPath() returns an error.
    End If
End Function

 

[Ramadan]

Maybe something like this could work for you. It uses the same hyperlink rollover trick to detect the mouse hover:

Workbook Example:
HyperlinkToPicture.xlsm

This is the rollover function I used:
Function RollOverImage( _
ByVal imgFilePathName As String, _
ByVal imgDisplayAnchorCell As Range, _
Optional ByVal imgWidth As Long, _
Optional ByVal imgHeight As Long _
)

Example of use in a worksheet cell:
=IFERROR(HYPERLINK(RollOverImage(B3,I3),A3),A3)
Where Cell B3 has the image file pathname, Cell I3 has the anchored cell over which the image is to be displayed and Cell A3 is the name that is displayed as the Hyperlink text.

The two last Optional arguments in the RollOverImage function correspond to the desired rendered image size (Width & Height) and they default to 100 pix X 100 pix ... Min 100 pix, Max 200pix.

Should work with all formats\types of images files (bmp, jpeg,png, jfif, ico etc) as it uses the GDIPlus api.

Note: You can, but you don't have to Click on the hyperlink in order to display the picture .... Just hovering over is enough.






In a Standard Module:

Option Explicit

#If Win64 Then
    Private Const NULL_PTR = 0^
#Else
    Private Const NULL_PTR = 0&
#End If

#If VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare PtrSafe Function ReleaseDC Lib "user32" (ByVal hwnd As LongPtr, ByVal hDC As LongPtr) As Long
    Private Declare PtrSafe Function GetDeviceCaps Lib "gdi32" (ByVal hDC As LongPtr, ByVal nIndex As Long) As Long
    Private Declare PtrSafe Function InvalidateRect Lib "user32" (ByVal hwnd As LongPtr, lpRect As Any, ByVal bErase As Long) As Long

    ' GDI+ x64bit API functions
    Private Declare PtrSafe Function GdiplusStartup Lib "GDIPlus" (token As LongPtr, inputbuf As GdiplusStartupInput, Optional ByVal outputbuf As LongPtr = 0) As Long
    Private Declare PtrSafe Function GdipLoadImageFromFile Lib "GDIPlus" (ByVal sFileName As LongPtr, hImage As LongPtr) As Long
    Private Declare PtrSafe Function GdipCreateFromHDC Lib "GDIPlus" (ByVal hDC As LongPtr, hGraphics As LongPtr) As Long
    Private Declare PtrSafe Function GdipDrawImageRectI Lib "GdiPlus.dll" (ByVal graphics As LongPtr, ByVal img As LongPtr, ByVal X As Long, ByVal Y As Long, ByVal Width As Long, ByVal Height As Long) As Long
    Private Declare PtrSafe Function GdipDisposeImage Lib "GDIPlus" (ByVal image As LongPtr) As Long
    Private Declare PtrSafe Function GdipDeleteGraphics Lib "GdiPlus.dll" (ByVal mGraphics As LongPtr) As Long
    Private Declare PtrSafe Function GdiplusShutdown Lib "GDIPlus" (ByVal token As LongPtr) As Long

#Else
    Private Enum LongPtr
        [_]
    End Enum
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetDC Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As LongPtr, ByVal hDC As LongPtr) As Long
    Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As LongPtr, ByVal nIndex As Long) As Long
    Private Declare Function InvalidateRect Lib "user32" (ByVal hwnd As LongPtr, lpRect As Any, ByVal bErase As Long) As Long

    ' GDI+ 32bit API functions
    Private Declare Function GdiplusStartup Lib "GDIPlus" (token As LongPtr, inputbuf As GdiplusStartupInput, Optional ByVal outputbuf As LongPtr = 0) As Long
    Private Declare Function GdipLoadImageFromFile Lib "GDIPlus" (ByVal sFileName As LongPtr, hImage As LongPtr) As Long
    Private Declare Function GdipCreateFromHDC Lib "GDIPlus" (ByVal hDC As LongPtr, hGraphics As LongPtr) As Long
    Private Declare Function GdipDrawImageRectI Lib "GdiPlus.dll" (ByVal graphics As LongPtr, ByVal img As LongPtr, ByVal x As Long, ByVal y As Long, ByVal Width As Long, ByVal Height As Long) As Long
    Private Declare Function GdipDisposeImage Lib "GDIPlus" (ByVal image As LongPtr) As Long
    Private Declare Function GdipDeleteGraphics Lib "GdiPlus.dll" (ByVal mGraphics As LongPtr) As Long
    Private Declare Function GdiplusShutdown Lib "GDIPlus" (ByVal token As LongPtr) As Long
#End If

Private Type POINTAPI
    X       As Long
    Y       As Long
End Type

Private Type RECT
    Left            As Long
    Top             As Long
    Right           As Long
    Bottom          As Long
End Type

Private Type GdiplusStartupInput
    GdiplusVersion            As Long
    DebugEventCallback        As LongPtr
    SuppressBackgroundThread  As LongPtr
    SuppressExternalCodecs    As Long
End Type


Public Function RollOverImage( _
    ByVal imgFilePathName As String, _
    ByVal imgDisplayAnchorCell As Range, _
    Optional ByVal imgWidth As Long, _
    Optional ByVal imgHeight As Long _
)

    Static bLooping As Boolean
    Dim oTargetCell As Range, oCurHoveredObject As Object
    Dim uCurPos As POINTAPI
 
    Set oTargetCell = Application.Caller
 
    GetCursorPos uCurPos
 
    If InStr(oTargetCell.Formula, "HYPERLINK(RollOverImage") Then
        If bLooping = False Then
            Do
                bLooping = True
                DoEvents
                GetCursorPos uCurPos
                Set oCurHoveredObject = ActiveWindow.RangeFromPoint(uCurPos.X, uCurPos.Y)
                If TypeName(oCurHoveredObject) = "Nothing" Then Exit Do
                If oTargetCell.Address <> oCurHoveredObject.Address Then Exit Do
                Call DrawImageToSreenDC(imgFilePathName, imgDisplayAnchorCell, imgWidth, imgHeight)
            Loop
            InvalidateRect NULL_PTR, ByVal 0&, 0&
        End If
        bLooping = False
    End If

End Function

Private Sub DrawImageToSreenDC( _
    ByVal imgPath As String, _
    ByVal imgAnchorRange As Range, _
    Optional ByVal imgWidth As Long = 100&, _
    Optional ByVal imgHeight As Long = 100& _
)

    Dim uGdiInput As GdiplusStartupInput, uAnchorRangeRect As RECT
    Dim gdiplusToken As LongPtr
    Dim image As LongPtr
    Dim hGraphics As LongPtr
    Dim hDC As LongPtr
    Dim lRet As Long
 
    On Error GoTo CleanUp
 
    uAnchorRangeRect = GetRangeRect(imgAnchorRange)
 
     ' Initialize GDI+
    uGdiInput.GdiplusVersion = 1&
    lRet = GdiplusStartup(gdiplusToken, uGdiInput)
 
    If lRet <> 0& Then
        MsgBox "Failed to initialize GDI+"
        Exit Sub
    End If
 
    lRet = GdipLoadImageFromFile(StrPtr(imgPath), image)
    If lRet <> 0& Then
        MsgBox "[Image invalid or doesn't exist]" & vbLf & vbLf & _
        "Make sure the Image file path in the 'Hyperlink' Function is correct."
        GdiplusShutdown gdiplusToken
        Exit Sub
    End If
 
    hDC = GetDC(NULL_PTR)
    GdipCreateFromHDC hDC, hGraphics
 
    With WorksheetFunction
        imgWidth = .Max(100&, .Min(200&, imgWidth))
        imgHeight = .Max(100&, .Min(200&, imgHeight))
    End With
 
    With uAnchorRangeRect
        GdipDrawImageRectI hGraphics, image, .Left + 10&, .Bottom - 10&, imgWidth, imgHeight
    End With
 
CleanUp:
    ' Cleanup Graphic Objects
    GdipDisposeImage image
    GdipDeleteGraphics hGraphics
    ReleaseDC NULL_PTR, hDC
    GdiplusShutdown gdiplusToken
 
End Sub

Private Function ScreenDPI(ByVal bVert As Boolean) As Long
    Const LOGPIXELSX As Long = 88&, LOGPIXELSY As Long = 90&
    Static lDPI(1) As Long, hDC As LongPtr
    If lDPI(0&) = 0& Then
        hDC = GetDC(NULL_PTR)
        lDPI(0&) = GetDeviceCaps(hDC, LOGPIXELSX)
        lDPI(1&) = GetDeviceCaps(hDC, LOGPIXELSY)
        hDC = ReleaseDC(NULL_PTR, hDC)
    End If
    ScreenDPI = lDPI(Abs(bVert))
End Function

Private Function PTtoPX(Points As Double, bVert As Boolean) As Long
    Const POINTS_PER_INCH = 72&
    PTtoPX = Points * ScreenDPI(bVert) / POINTS_PER_INCH
End Function
 
Private Function GetRangeRect(ByVal Rng As Range) As RECT
    Dim oPane  As Pane
    Set oPane = Rng.Parent.Parent.Windows(1&).ActivePane
    With GetRangeRect
        .Left = oPane.PointsToScreenPixelsX(Rng.Left - 1&)
        .Top = oPane.PointsToScreenPixelsY(Rng.Top)
        .Right = oPane.PointsToScreenPixelsX(Rng.Left + Rng.Width)
        .Bottom = oPane.PointsToScreenPixelsY(Rng.Top + Rng.Height)
    End With
End Function

Hope this helps.

@Jaafar Tribak First I would like to thank you soooooo much for your effort and being patient to write and explain all such details and I would like to clarify that my sheet is a business document not personal file and connected with other workbooks which makes adding new coulmns is not that much easy and the main point is we don't need to just dispaly the image on hovering but to fisically open the image file itself and as I mentioned in my question it works smoothly in other sheet but I can't figure why in this sheet giving error. Again as I mentioned with this formula =HYPERLINK("D:\Desktop\ClientsIDs\",B8) and without any codes in modules as you can see in my shared screenshot it looks good but when I click on the cell "O" it just takes me to the folder that containnig the images while I need if possible to fisically open the image itself like the other sheet and not to just dispaly it.

Thank you so much again

 

[Micron]

why only jpg ! and only img number 0003!

Because that is the file name for the picture. 0003.jpg is what is in the cell. I double click on the white space beside the link (NOT on the link) and it opens the pic in my default image app.

 

[Jaafar Tribak]

@Ramadan

we don't need to just dispaly the image on hovering but to fisically open the image file itself

So in that case I see no reason for posting the vba imgPath function in your initial post !! You shouldn't include imgPath in the hyperlink formula !!
=HYPERLINK(imgPath("D:\Desktop\ClientsIDs",TEXT(B8,"000")),B8)

In your last post, you wrote this: =HYPERLINK("D:\Desktop\ClientsIDs\",B8), and you are saying that when clicking the hyperlinks located in column O, it takes you to the image folder instead of opening the actual picture. This suggests that you are not passing the correct path as mentioned by Micron in his previous answer.

Also, can I ask where you are getting the path strings from? Are the paths srtings in some other column \ cell(s)?

Anyways, Just to make sure we are on the same page, let's try the following test and see if it solves the problem:

We are going to base this small test on row# 27 taken from your previous screenshot as follows:

Do the following:

1- Get rid of (or better, comment out) the imgPath vba function. (because it is not needed)
2- Place the formula =HYPERLINK("D:\Desktop\ClientsIDs\" & B27 ,B27) in the Cell O27 ( Notice that we didn't use here the TEXT function as that would alter the path string)
Obviously, the above formula assumes that "D:\Desktop\ClientsIDs\" is the path of the folder where all the file images are stored ... If the folder path string is stored in a worksheet cell, then pass the the cell address instead of passing the litteral path string.
3- Click the hyperlink in Cell O27 - This should now launch the default image application associated with the respective image file extension along with the image loaded and on display.
4- Done.

If the above test succeeds, carry on doing the same with the rest of the records on the table. If it fails, it means we are missing some info.

BTW, I see that a filter is being applied (and maybe a split screen as well) to the worksheet but, I don't think that can cause the issue you are describing.

@Micron
As you probably already know, the Worksheet_FollowHyperlink event won't fire with the Hyperlink function . It only works with inserted hyperlinks .

 

[Micron]

As you probably already know, the Worksheet_FollowHyperlink event won't fire with the Hyperlink function

Not sure what you're referring to by hyperlink function. I think you mean that is something you can write in a cell formula, and I only know the basic of basic cell functions. Also, too old to want to be bothered learning. I wouldn't expect a vba procedure to fire from a cell formula though, so if there are any that will I won't know about them.

I just don't see why this is not solved yet. I've given two solutions already and thought I had explained both well enough to solve the problem. I guess not.
Both worked for me. If OP wants to continue, I think it best if OP uploads a wb to a file share.

 

[Ramadan]

Because that is the file name for the picture. 0003.jpg is what is in the cell. I double click on the white space beside the link (NOT on the link) and it opens the pic in my default image app.

Not sure what you're referring to by hyperlink function. I think you mean that is something you can write in a cell formula, and I only know the basic of basic cell functions. Also, too old to want to be bothered learning. I wouldn't expect a vba procedure to fire from a cell formula though, so if there are any that will I won't know about them.

I just don't see why this is not solved yet. I've given two solutions already and thought I had explained both well enough to solve the problem. I guess not.
Both worked for me. If OP wants to continue, I think it best if OP uploads a wb to a file share.

@Micron Ok, Understood, forgive my little knowledge in such professional issues but as I told you I put your code in a module and pasted the formula in "O" not working

 

[Ramadan]

@Ramadan

So in that case I see no reason for posting the vba imgPath function in your initial post !! You shouldn't include imgPath in the hyperlink formula !!
=HYPERLINK(imgPath("D:\Desktop\ClientsIDs",TEXT(B8,"000")),B8)

In your last post, you wrote this: =HYPERLINK("D:\Desktop\ClientsIDs\",B8), and you are saying that when clicking the hyperlinks located in column O, it takes you to the image folder instead of opening the actual picture. This suggests that you are not passing the correct path as mentioned by Micron in his previous answer.

Also, can I ask where you are getting the path strings from? Are the paths srtings in some other column \ cell(s)?

Anyways, Just to make sure we are on the same page, let's try the following test and see if it solves the problem:

We are going to base this small test on row# 27 taken from your previous screenshot as follows:

View attachment 122533

Do the following:

1- Get rid of (or better, comment out) the imgPath vba function. (because it is not needed)
2- Place the formula =HYPERLINK("D:\Desktop\ClientsIDs\" & B27 ,B27) in the Cell O27 ( Notice that we didn't use here the TEXT function as that would alter the path string)
Obviously, the above formula assumes that "D:\Desktop\ClientsIDs\" is the path of the folder where all the file images are stored ... If the folder path string is stored in a worksheet cell, then pass the the cell address instead of passing the litteral path string.
3- Click the hyperlink in Cell O27 - This should now launch the default image application associated with the respective image file extension along with the image loaded and on display.
4- Done.

If the above test succeeds, carry on doing the same with the rest of the records on the table. If it fails, it means we are missing some info.

BTW, I see that a filter is being applied (and maybe a split screen as well) to the worksheet but, I don't think that can cause the issue you are describing.

@Micron
As you probably already know, the Worksheet_FollowHyperlink event won't fire with the Hyperlink function . It only works with inserted hyperlinks .

@Jaafar Tribak my folder is on desktop and I get the path string through (Shift+Right Click +copy path) not in any other cell
I have done all what you said above but I gor an error (Cannot open the specified file.) as you see in the screenshot below
I'm sure that there is something wrong I don't know
I have share a copy from my file that you can test it if possible https://files.fm/u/rpf9d8w7a6https://files.fm/u/rpf9d8w7a6

 

[Ramadan]

@Micron & @Jaafar Tribak , Friends only now I discovered what is my probelm
with the same code I have in my question and the same Hyberlink which works fine as I told you in my other sheets, now in this sheet I discovered that the problem that giving me #NAME error is because B8 has a formula to automatically create serial number based on cell value of C8 once i add new data in C8 it gives a new serial with this formula =IF($C8<>"",ROW()-ROW($B$7),"")

Thank you so much for your several Tries to help it's ok now solved