Preserving Hyperlink during a Lookup - vba code?

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
Hi, i am wondering if this possible.

I have created a little database within excel.

One table i have in this database is the following (see below). These "From and "To" fields are for a serial number. The user enters a serial number on a userform, and it pulls through the row from this table in which the serial number falls into, and puts it in my sheet named "Database".

1626088660134.png


The issue you can see is that column H is a hyperlink to a PDF file. If we take H1 for example, any serial number within 2200487 and 2200546 will use this PDF.

I can bring this data through to the database simply like this:

VBA Code:
Dim ID As Long
Dim StartID As Long
Dim EndID As Long
Dim i As Range
Dim Flg As Boolean


If SerialNumber.Value = "" Then

Else

'code for receiver range, *if number entered is between range then lookup rest of data*


    ID = SerialNumber.Value
  
        For Each i In Sheets("ReceiverData").Range("Table2[From]")
      
        StartID = i.Value
        EndID = i.Offset(0, 1).Value
      
            If ID >= StartID And ID < EndID Then

            txtType.Value = i.Offset(0, 3).Value
            txtSize.Value = i.Offset(0, 4).Value
            txtWKPRESS.Value = i.Offset(0, 6).Value
            txtCertDate.Value = i.Offset(0, 5).Value
            TextBox26.Value = i.Offset(0, 7).Value     'Certificate hyperlink
            Flg = True
            End If
      
        Next i

End If


I then add this information to the database like so:

VBA Code:
Dim sh As Worksheet
    Dim iRow As Long
 
    Set sh = ThisWorkbook.Sheets("Database")
    
    
    If frmForm.txtRowNumber.Value = "" Then
    
        iRow = [Counta(Database!A:A)] + 1
    Else
    
        iRow = frmForm.txtRowNumber.Value
        
    End If
    
    'Duplicate checking for Works Order Number
    
    
    
    With sh
    
    'adding each row to database
    
        .Cells(iRow, 1) = "=Row()-1" 'Dynamic Serial Number
        
        .Cells(iRow, 2) = frmForm.ModelNo.Value
        
        .Cells(iRow, 3) = frmForm.PartNo.Value
        
        .Cells(iRow, 4) = frmForm.WorksOrderNo.Value
        
        .Cells(iRow, 5) = frmForm.SerialNo.Value
        
        .Cells(iRow, 6) = frmForm.MaterialNo.Value
        
        .Cells(iRow, 7) = frmForm.SerialNumber.Value
        
        .Cells(iRow, 8) = frmForm.txtType.Value
        
        .Cells(iRow, 9) = frmForm.txtSize.Value
        
        .Cells(iRow, 10) = frmForm.txtWKPRESS.Value
        
        .Cells(iRow, 11) = frmForm.txtCertDate.Value
        
        .Cells(iRow, 12) = frmForm.BatchNo.Value
        
        .Cells(iRow, 13) = frmForm.JobNo.Value
        
        .Cells(iRow, 14) = frmForm.DateOfManufacture.Value
        
        .Cells(iRow, 15) = frmForm.Label47.Caption
        
        .Cells(iRow, 16) = Application.UserName
        
        .Cells(iRow, 17) = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]
        
        .Cells(iRow, 19) = frmForm.TextBox26.Value
        
    End With

This works and pulls through the correct certificate. However obviously, it does not include the hyperlink with it. I would like the hyperlink to be included if it is possible. As i am going to have a button to automatically print it depending on what serial number was entered into the database
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

severynm

Board Regular
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
Use the Hyperlinks.Add method of the worksheet whenever you are writing to your table. You'll need to provide the anchor (which cell is to contain the hyperlink), hyperlink address, and the friendly name for the link. Example:
VBA Code:
Me.Hyperlinks.Add Anchor:=Me.Range("A1"), Address:="C:\Temp\_CB2TMPDATA.csv", TextToDisplay:="1"
 

Forum statistics

Threads
1,141,294
Messages
5,705,537
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top