CODEWITHSUNDEEP
vbaexcel
chrisleepotter
chrisleepotter

Reputation: 59

How to Create Hyperlink in Workbook Cell to Hard Drive File using a Userform Textbox and Macro

I have a Userform that uses textboxes as a source to add data onto a worksheet. The way it does so is simple: specified cells equal textbox value.

One of my columns is for photo links that go to a folder on my computer. This is where I am stuck, as I can add the filepath to the cell from my textbox, but it is not highlighted blue and underlined(not hyperlinked). That is something I want to be automated.

Thank you for your time guys, this team is literally the best I have found online!

Note: As you will see in my code, my Folder path and file format will always be the same, with the textbox value making all the difference; "filepath" & userform.textbox.value & ".jpg". I like it this way for some good reasons, and changing it is not really an option I have. Suggestions and education however are sought after!

Code: 

    With ws
      '.Unprotect Password:="password"
      .Cells(iRow, 1).Value = DRAFT1.PART2.Value
      .Cells(iRow, 2).Value = DRAFT1.LOC2.Value
      .Cells(iRow, 3).Value = DRAFT1.DESC2.Value
      .Cells(iRow, 5).Value = DRAFT1.QTY2.Value
      .Cells(iRow, 8).Value = DRAFT1.DATE2.Value
      .Cells(iRow, 9).Value = DRAFT1.POS2.Value
      .Cells(iRow, 10).Value = DRAFT1.TYPE2.Value
      .Cells(iRow, 11).Value = DRAFT1.COLOR2.Value
      .Cells(iRow, 12).Value = DRAFT1.FEATURES2.Value

      'this is where I am having trouble
      .Cells(iRow, 13).Value = "C:\User\ttech\Desktop\EXCEL\PHOTOS\" & DRAFT1.PHOTO2.Value & ".jpg"
      'I tried ActiveSheets.Hyperlink.Add(filepath...) 
      'And (filepath...).hyperlinks

      .Cells(iRow, 14).Value = DRAFT1.OTHERNAMES2.Value
    '  .Protect Password:="password"

Upvotes: 0

Views: 78

Answers (1)

Smithy7876
Smithy7876

Reputation: 344

Try this

.hyperlinks.add Anchor:= .Cells(iRow, 13), Address:= "C:\User\ttech\Desktop\EXCEL\PHOTOS\" & DRAFT1.PHOTO2.Value & ".jpg", TextToDisplay:= "C:\User\ttech\Desktop\EXCEL\PHOTOS\" & DRAFT1.PHOTO2.Value & ".jpg"

Not sure how you tried the hyperlinks add but it should work as above

You don't need the active sheet if its contained within your with

Upvotes: 1

Related Questions