Basically I Push a Macro Button on Sheet 1 which creates a copy of
Sheet1 in a specified folder. It then adds a record to my Record
listing (at the bottom of the list) on Sheet2. What I’m trying to do is
when I update the record on Sheet2 I want the macro to include a link
(hyperlink would work) to the newly created file (the file path is
being specified as “FileName” for the macro). This will allow me to
link to the file from my records list which is great for archiving.
Since the cell is not static I don’t know how to work it into the
formula.

..Offset(0, 4) = FileName '**** I want it to turn this data into a
hyperlink ****

Here is most of the code

s_Database = "Sheet2"


' This defines and pulls my data from Sheet1
Dim indexnr As Integer
Dim name As String
Dim mtype As String
Dim totalvalue As Double

indexnr = ActiveSheet.Range("A3")
name = ActiveSheet.Range("B2")
mtype = ActiveSheet.Range("C3")
totalvalue = ActiveSheet.Range("D4")

With Sheets(s_Database).Range("A" & indexnr + 1)
If Val(.Value) 0 Then
MsgBox "A record with number " & indexnr & " already exists!"
Else

'This creates the file name = "FileName"
Dim FileName As String
FileName = "c:\test\" & Range("A3") & Range("B2")

..Value = indexnr
..Offset(0, 1) = name
..Offset(0, 2) = mtype
..Offset(0, 3) = totalvalue
..Offset(0, 4) = FileName '**** I want it to turn this into a hyperlink
****


--
bobwilson
------------------------------------------------------------------------
bobwilson's Profile: http://www.excelforum.com/member.php...o&userid=33046
View this thread: http://www.excelforum.com/showthread...hreadid=528621

This should work for you; replace your code:

Code:
--------------------
.Offset(0, 4) = FileName '**** I want it to turn this into a hyperlink ****
--------------------


With this:

Code:
--------------------
.Offset(0, 4).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"file:" & FileName, SubAddress:="A1"
--------------------


Hope this helps,
theDude


--
theDude
------------------------------------------------------------------------
theDude's Profile: http://www.excelforum.com/member.php...o&userid=16550
View this thread: http://www.excelforum.com/showthread...hreadid=528621