Creating a hyperlink with a String in Text cell trims 0s

Question

I created a sub to create hyperlinks in my Excel 2010 workbook, and it works for all my purposes except one.

I have a use case where the sub is supposed to create a link with a task number for the text value, with the format "00.00". Passing a string parameter as the hyperlink's text value trims trailing 0s, i.e. If I pass "12.10" as my task number, the variable watcher shows a consistent "12.10" value in the debugger, but the cell shows "12.1", even if I preformat the whole column to be a "Text" cell.

Before you suggest treating the task number as a number: it looks like a number but it isn't. It's a concatenation of two things and needs to be a string in all instances of its use.

Here is the sub:

sSheet : Sheet where the link will be added
sColumn : Column of the hyperlink cell
sRow : Row of the hyperlink cell
sTargetSheet : link destination sheet name
sAddress : link destination cell address
sText : link text to display (this is my problematic parameter)

Sub CreateLink(sSheet As String, sColumn As String, sRow As String, sTargetSheet As String, _
  sAddress As String, sText As String)
    Dim sSubAddress As String
    If sTargetSheet = "" Then
        sSubAddress = ""
    ElseIf sAddress <> "" Then
        sSubAddress = "'" & sTargetSheet & "'!" & sAddress
    End If

    With Sheets(sSheet)
        .Hyperlinks.Add Anchor:=.Range(sColumn & sRow), _
                        Address:="", _
                        SubAddress:=sSubAddress, _
                        TextToDisplay:=sText

    End With
End Sub

This creates any link I want, whether they point to a cell or activate a macro through the Worksheet_FollowHyperlink call.

In the case that causes a problem, I want to set a cell with a hyperlink with text format "99.99".

I have hard set the whole column where those links are created to be Text, and call the Sub by passing the value parameter as String, like this (the original variable is also a string but Call forces me to cast as String anyway).

Call CreateLink("Tasks", "A", CStr(nextRow), "", "", CStr(nextTaskNumber))

I don't know how to force the .Hyperlinks.Add sub to pass the value as is. I watch the variable and until the very end nextTaskNumber has all its trailing 0s, but the cell just loses it.

How can I make sure the cell does not trim the 0?

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2013-12-30T16:09:02

try to to change cell format to text format:

With Sheets(sSheet)
        .Range(sColumn & sRow).NumberFormat = "@"
        .Hyperlinks.Add Anchor:=.Range(sColumn & sRow), _
                        Address:="", _
                        SubAddress:=sSubAddress, _
                        TextToDisplay:=sText

End With

Creating a hyperlink with a String in Text cell trims 0s

1 Answers