When VBA creates a hyperlink, it is using the original cell value as the name. I cant figure out how to stop it from doing this...

[kbishop94]

My current code looks like this:

Sheets("Seatex Incident Log").Hyperlinks.Add Sheets("Seatex Incident Log").Cells(RecordRow, 29), "J:\QMS_General Facility\INCIDENT LOGS\Incident_Pictures\" & Me.txtIncidentID1.Text & ".jpg"

Cell 29 on the worksheet will always have a "-" (single dash) in it (always until when, and if, it is to be replaced with a hyperlink address, that is.)

What I am left with after running the code to update the worksheet via my userform is a hyperlink that looks like this: " - "

But what I need is it to show is what the code shown above is telling it to... so in the cell it should end up looking like this:

J:\QMS_General Facility\INCIDENT LOGS\Incident_Pictures\22-1150.jpg

Where the "22-1150" represents a number from the userform ("Me.txtIncidentID1") that is specific for that record.

Thoughts? Suggestions? Thanks everyone.

 

[kbishop94]

I think I was trying to overthink this one

It finally dawned on me that the solution was really simple:

With ws
    If chkAddPic.value = True Then

        .Cells(RecordRow, 29).value = "J:\QMS_General Facility\INCIDENT LOGS\Incident_Pictures\" & Me.txtIncidentID1.Text & ".jpg"

        Sheets("Seatex Incident Log").Hyperlinks.Add Sheets("Seatex Incident Log").Cells(RecordRow, 29), "J:\QMS_General Facility\INCIDENT LOGS\Incident_Pictures\" & Me.txtIncidentID1.Text & ".jpg"                
    Else
    End If
End With

All that it needed was to change the cells contents from " - " to the text that I wanted the hyperlink to show, and THEN add the hyperlink. (duh)

 

[Alex Blakenburg]

Just to round out the discussion, a couple of other options would have been.
1) since you want the full address in the cell, instead of:
.Cells(RecordRow, 29).value = "J:\QMS_General Facility\INCIDENT LOGS\Incident_Pictures\" & Me.txtIncidentID1.Text & ".jpg"
You could have just cleared the cell
.Cells(RecordRow, 29).ClearContents

2) If you did want something different you could have used the display text property.

    Dim sPicName As String
    sPicName = "J:\QMS_General Facility\INCIDENT LOGS\Incident_Pictures\" & Me.txtIncidentID1.Text & ".jpg"

    ActiveSheet.Hyperlinks.Add _
        Anchor:=Sheets("Seatex Incident Log").Cells(RecordRow, "B"), _
        Address:=sPicName, _
        TextToDisplay:=sPicName