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

markw1804

New Member
Joined
Nov 19, 2020
Messages
11
Office Version
  1. 365
Platform
  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.
Thanks
Mark



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
Else
          If Me.lblAddDrawing1 = "" Then
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(CurrentRow + 2, 34), _
            Address:=strFileToLink, _
            ScreenTip:="Picture Link", _
            TextToDisplay:=lnkNm
            Me.lblAddDrawing1.Caption = lnkNm
            
        Else
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(CurrentRow + 2, 35), _
            Address:=strFileToLink, _
            ScreenTip:="Picture Link", _
            TextToDisplay:=lnkNm
            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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,118,709
Messages
5,573,735
Members
412,550
Latest member
soking
Top