B
bobwilson
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
****
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
****