Make Userform label visible for row displayed based on cell being populated with hyperlink info


New Member
Nov 19, 2020
Office Version
  1. 365
  1. Windows
Hi All,
I'm not sure how to get this to work, I have a userform in place that allows me to scroll through all the entries in the stock worksheet row by row. I have managed to create a button that allows hyperlinks to image files to be added to the worksheet. 2 images per row with 2 labels allowing access on the userform.
It does create the labels that can be clicked on to open the image from the userform and populates the worksheet perfectly. All good. Unfortunately if you then use the spin button on the userform to move to the next record/row and return back the labels are no longer there? I assume I may need to add some code to the form populate from the spin button function that checks to see if the cell has a hyperlink in it for that row and the label then becomes visible on the userform, is this possible?
My code is begged, stolen and borrowed from everywhere so any help would be appreciated.

This is my code to add the hyperlink:

VBA Code:
Private Sub cmdAddDrawing1_Click()

Dim strFileToLink As String
Dim lnkNm As String
Dim erow As Long

If Me.lblAddDrawing2.Caption = "Click here to View Image" Then
MsgBox "No more images can be uploaded to this item", vbInformation + vbOKOnly, "All Images Loaded"
Exit Sub
End If

'link name
lnkNm = "Click here to View Image"
Application.ScreenUpdating = False
strFileToLink = Application.GetOpenFilename _
(Title:="Please select the image file to link to")

'Checking if file is selected.
If strFileToLink = "" Then
    MsgBox "No file selected.", vbExclamation, "Error"
    Exit Sub
          If Me.lblAddDrawing1 = "" Then
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(CurrentRow + 2, 34), _
            Address:=strFileToLink, _
            ScreenTip:="Picture Link", _
            Me.lblAddDrawing1.Caption = lnkNm
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(CurrentRow + 2, 35), _
            Address:=strFileToLink, _
            ScreenTip:="Picture Link", _
            Me.lblAddDrawing2.Caption = lnkNm
         End If
  End If
End Sub

This is my code to open the Hyperlinks from the labels:

VBA Code:
Private Sub lblAddDrawing1_Click()
ActiveWorkbook.FollowHyperlink Worksheets("Stock").Range(CurrentRow + 2, 34).Value, NewWindow:=True
End Sub

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Latest member