Solution 1: Automatically Create Hyperlink for Image in Folder Based on a Cell Value
- Edit your formula like this to create the full filename as hyperlink, assuming your ID column just has an ID number without file extension:
```html
=HYPERLINK(""D:\Desktop\Guards\Guards National IDs\"" & B5 & ".jpg", B5)
```
- If you prefer a friendly name with truncating, apply this formula instead:
```html
=HYPERLINK(""D:\Desktop\Guards\Guards National IDs\"" & B5,LEFT(B5,FIND(""."",B5)-1))
```
Solution 2: Handle Different Image File Extensions
- To account for varying image file extensions, define a custom function called imgPath() in a module:
```html
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
```
- In your worksheet, use this formula to create hyperlinks:
```html
=HYPERLINK(imgPath(""D:\Desktop\Guards\Guards National IDs\"", A2), A2)
```
This method will create hyperlinks to image files with any extension, displaying #errors for missing files.
Solution 3: Handle Hyperlink Creation and Removal with Worksheet Change Event
- In the sheet module (e.g., Sheet1), add this code:
```html
Private Sub Worksheet_Change(ByVal Target As Range)
Const IMAGES_PATH As String = "D:\Desktop\Guards\Guards National IDs\"
Const IMAGE_FILE_EXTENSION As String = ".jpg"
Const FIRST_ID_CELL As String = "B5"
Const HYPERLINK_COLUMN As String = "V"
Const FRIENDLY_NAME As String = "Image"
Dim ws As Worksheet: Set ws = Target.Worksheet
Dim LastRow As Long
With ws.UsedRange
LastRow = .Row + .Rows.Count - 1
End With
Dim rg As Range
With ws.Range(FIRST_ID_CELL)
If LastRow < .Row Then Exit Sub
Set rg = .Resize(LastRow - .Row + 1)
End With
Dim irg As Range: Set irg = Intersect(rg, Target)
If irg Is Nothing Then Exit Sub
Application.EnableEvents = False
Dim icell As Range, hcell As Range
For Each icell In irg.Cells
Set hcell = icell.EntireRow.Columns(HYPERLINK_COLUMN)
If Len(icell.Value) = 0 Then
If Len(hcell.Value) > 0 Then hcell.Clear
Else
hcell.Hyperlinks.Add hcell, IMAGES_PATH & icell.Value _
& IMAGE_FILE_EXTENSION, , , FRIENDLY_NAME
End If
Next icell
Application.EnableEvents = True
End Sub
```
- This code automatically adds or removes hyperlinks in column V based on changes in column B (ID).
Solution 4: Handle Hyperlink Creation for Dynamically Generated IDs
- In the sheet module (e.g., Sheet1), replace the previous code with this improved version:
```html
Private Sub Worksheet_Change(ByVal Target As Range)
Const IMAGES_PATH As String = "D:\Desktop\Guards\Guards National IDs\"
Const FIRST_NAME_CELL As String = "C5"
Const ID_COLUMN As String = "B"
Const HYPERLINK_COLUMN As String = "V"
Const FRIENDLY_NAME As String = "Image"
Dim ws As Worksheet: Set ws = Target.Worksheet
Dim LastRow As Long
With ws.UsedRange
LastRow = .Row + .Rows.Count - 1
End With
Dim rg As Range
With ws.Range(FIRST_NAME_CELL)
If LastRow < .Row Then Exit Sub
Set rg = .Resize(LastRow - .Row + 1)
End With
Dim nrg As Range: Set nrg = Intersect(rg, Target)
If nrg Is Nothing Then Exit Sub
Application.EnableEvents = False
Dim ncell As Range, icell As Range, hcell As Range
Dim iFilePath As String, iFileName As String
For Each ncell In nrg.Cells
Set icell = ncell.EntireRow.Columns(ID_COLUMN)
Set hcell = ncell.EntireRow.Columns(HYPERLINK_COLUMN)
If Len(icell.Value) = 0 Then
If Len(hcell.Value) > 0 Then hcell.Clear
Else
iFileName = Dir(IMAGES_PATH & icell.Value & ".*")
If Len(iFileName) > 0 Then
iFilePath = IMAGES_PATH & iFileName
hcell.Hyperlinks.Add hcell, iFilePath, , , FRIENDLY_NAME
Else
hcell.Clear
End If
End If
Next ncell
Application.EnableEvents = True
End Sub
```
- This updated code handles dynamic ID generation in column B and creates hyperlinks to corresponding image files, clearing hyperlinks for non-existent files.