Using vba to hyperlink cells when populated

Try adding the following to the Sheet1 tab

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Application.EnableEvents = False
For Each Cell In Target
    If Cell.Column = 5 Then
        If Cell.Value <> "" Then
            ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:= _
            "C:\Documents and Settings\carl.walker\My Documents\Machine DWG\" & Cell.Value & ".txt" ', TextToDisplay:=Cell.Value
        Else
            Cell.Hyperlinks.Delete
        End If
    End If
Next Cell
Application.EnableEvents = True
End Sub

I've assumed that the file has a .txt extension.

OK - can you try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Application.EnableEvents = False
For Each Cell In Target
    If Cell.Column = 5 Then
        If Cell.Value <> "" Then
            If Dir("C:\Documents and Settings\carl.walker\My Documents\Machine DWG\" & Cell.Value & ".pdf") <> "" Then
                ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:= _
                "C:\Documents and Settings\carl.walker\My Documents\Machine DWG\" & Cell.Value & ".pdf", TextToDisplay:=Cell.Value
            End If
        Else
            Cell.Hyperlinks.Delete
        End If
    End If
Next Cell
Application.EnableEvents = True
End Sub

It's probably that the string that I am using to generate the path and filename is not exactly the same as on your system.

"C:\Documents and Settings\carl.walker\My Documents\Machine DWG\" & Cell.Value & ".pdf"

If your replace Cell.Value with the number - does this give the correct path?