Need hyperlinking help

[Cyrric]

I'm a complete idiot when it comes to Excel. I did search on the forum, but couldn't find anything that worked for what I need.

I have a cell that is blank. When I enter text into it, which will be a 6 digit number, I need to have a hyperlink created.

So as the example, in the blank cell D19, I enter in 123456. The URL that the hyperlink needs to point to is http://server.domain.net/apps/123456?OpenDocument

The text that is displayed should just be the 6 digit number I entered.

I've tried different things but nothing seems to work. Any help would be greatly appreciated.

[Ron Coderre]

Maybe something like this....

B2: (a document number to be viewed....eg 123456)

This formula creates a hyperlink to that document and displays a message to: Click here to view document:

C2: =HYPERLINK("http://server.domain.net/apps/"&B2&"?OpenDocument",
"Click me to view document")

Is that something you can work with?

[Cyrric]

Sort of... (I think.. remember.. I'm an idiot hehehe)

I can put the above code in, changing B2 to my cell which is D19. I hit enter, it gives me an error about circular reference and then sets the cell to 0.

Now, I can put the 6 digit number in E19, which is what I think you were driving at with C2 above, and that works. But I'd like to be able to not have to use an additional column for the 6 digit number.

Is that even possible? I know I'm not explaining this very well.

[Ron Coderre]

If you don't mind a VBA solution....try this:

[Alt]+F11....to open the VBA editor
Right-click on your workbook name and Insert.Module

Copy the below code and paste it into the module:

Sub CovertTxt2Hyperlink()
Dim cCell As Range

For Each cCell In Selection.Cells

   If cCell.Hyperlinks.Count = 0 Then
      On Error Resume Next
      ActiveSheet.Hyperlinks.Add _
         Anchor:=cCell, _
         Address:="http://server.domain.net/apps/" & cCell.Value & "?OpenDocument"
   End If
Next cCell
End Sub

Now, switch back to your worksheet.
Enter a number...eg 123456...and press enter.
Select that cell.
Tools.Macro.Macros....select: CovertTxt2Hyperlink...click: Run

That should convert that cell into a hyperlink to the document.

If that works...you can select a whole range of cells and run the macro on them.

Is that something you can work with?

[Phil_V]

If your hyperlinks are all going to be in one column, or one row, or one range of cells you can also use this macro to change your text in real-time.

If your hyperlinks are only going on one sheet, then right click on that sheet tab, 'View Code', and then paste the code below in the editor window that appears.
It is currently set to change anything you type in column E into a hyperlink, but you can change the column by adjusting the red text:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Columns("E"), Target) Is Nothing Then
        If Target.Hyperlinks.Count > 0 Then Target.Hyperlinks.Delete
        If Target.Value <> "" Then
            Target.Hyperlinks.Add Anchor:=Target, _
                                  Address:="http://server.domain.net/apps/" & Target.Value & "?OpenDocument"
        End If
    End If
End Sub

[Cyrric]

Phil_V that is almost PERFECT! Your code worked flawlessly.

Is there anyway that code can be modified to ONLY do that on cell D19? The reason I ask is because the cell directly below it (D20) also needs a hyperlink, but that hyperlink is a different URL (also based on what I put in cell D20)

So to explain it better, D19 needs to have the hyperlink:
http://server.domain.net/apps/<contents of D19>?OpenDocument

And D20 needs to have the hyperlink:
http://anotherdomain.net/ticket=<contents of D20>

I think I'm almost there!

[Phil_V]

Sure, if you just want D19 then you can do this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("D19"), Target) Is Nothing Then        If Target.Hyperlinks.Count > 0 Then Target.Hyperlinks.Delete
        If Target.Value <> "" Then
            Target.Hyperlinks.Add Anchor:=Target, _
                                  Address:="http://server.domain.net/apps/" & Target.Value & "?OpenDocument"
        End If
    End If
End Sub

If you want the flexibility of having different hyperlink for different cells, then you can use the following code instead.
D19 will have a hyperlink to h ttp://server.domain.net/apps/<D19 Value>?OpenDocument
D20 will have a hyperlink to h ttp://anotherdomain.net/ticket=<D20 Value>
D21 and D22 will both use the same hyperlink address of h ttp://yetanotherone.com/value=, then followed by the contents of the appropriate cell.

Hopefully you can see how you can alter this to fit your needs, but if you have any questions feel free to ask

Private Sub Worksheet_Change(ByVal Target As Range)
Dim target_cell As Range
Dim url As String

For Each target_cell In Target.Cells
    url = ""
    Select Case Target.Address
        Case Range("D19").Address
            url = "http://server.domain.net/apps/" & target_cell.Value & "?OpenDocument"
        Case Range("D20").Address
            url = "http://anotherdomain.net/ticket=" & target_cell.Value
        Case Range("D21").Address, Range("D22").Address
            url = "http://yetanotherone.com/value=" & target_cell.Value
    End Select
        
    If url <> "" Then
        ' Delete any current hyperlink in the cell
        If target_cell.Hyperlinks.Count > 0 Then target_cell.Hyperlinks.Delete
        
        ' Put in the new hyperlink if required
        If target_cell.Value <> "" Then
            target_cell.Hyperlinks.Add Anchor:=target_cell, _
                                  Address:=url
        End If
    End If
Next
End Sub

[Cyrric]

Phil_V,

I just got the chance to try out your code. It worked like a charm. Thank you SO much for helping out this Excel challenged individual.