Using Excel 2002
I have a user form which has various text boxes which information is typed
into, and then when OK is clicked puts the data from the text boxes in
specific columns on the next emty row of the 'In Work' worksheet.
I would like to have the form add a hyperlink (to an external folder) to one
of the cells, using some predefined (relative) path information, and
extracting the remainder of the path information from the information
entered in the form. This hyperlink would need to follow the data in the
cell around whenever it was sorted, copied, pasted, etc.

The info:
Form uses 'nextmtrow' as the row #, the column for the hyperlink location is
'E'.
The predefined path is 'C:\My Documents\Work\TDP\'
The spreadsheet file lives in 'C:\My Documents\Work' thereby the relativity.
The information extracted from the form data is:
from text box 'NSN' (which is always a 13 digit numerical value): the first
four digits
from text box 'NSN' (which is always a 13 digit numerical value): the first
6 digits with a dash added between digits 4 & 5
from text box 'NSN' (which is always a 13 digit numerical value): the entire
value with a dash added between digits 4 & 5, 6 & 7, 9 &10
from text box 'DrawingNumber': the entire value
from text box 'DashNumber': the entire value

Sample values below:
NSN.value = 1560012251548
DrawingNumber.value = 16D4568
DashNumber.value = -101

Hyperlink full path from above data would look like this: "C:\My
Documents\Work\TDP\1560+\1560-01+\1560-01-225-1548 16D4568 16D4568-101"
the plus sign is a predetermined given, and the breakdown is as follows:
predetermined relative path\first four digits of NSN followed by +\first 6
digits of NSN with dash added between 4th and 5th digit, followed by
+\entire value of NSN with dash added between 4th and 5th, 6th and 7th, and
9th and 10th digits, followed by a space, entire value of DrawingNumber,
followed by a space, entire value of DrawingNumber, entire value of
DashNumber.

The worksheet displays the value of the NSN textbox in the E column (with a
display format which adds the dashes in), having the text turn blue, and
underlined is acceptable but not necessary for my purposes, I have
conditional formatting in place which would overide that aspect (I think).

I primarily need direction in the proper syntax for hyperlink creation, as
well as modification of the NSN value to add the dashes, and spaces have
given me fits before, so maybe that too.

my current primitive state - non-functional code below:

nsnvalue = NSN.Value
drawingnvalue = DrawingNumber.Value
dashnvalue = DashNumber.Value
With Worksheets("In Work").Hyperlinks.Add.Range("E" & nextmtrow), _
"C:\My Documents\Work\TDP\" & nsnvalue & drawingnvalue & drawingnvalue
& dashnvalue
End With


Thanks,

Danny