Macro add Hyperlink formula

[Guest]

A formula that finally works (thanks Dave Ritchie) is
=HYPERLINK("#"&CELL("address",Datasheet!C15),Datasheet!C15). I can copy and
it to other cells and also don't get cannot open specified file error

But how do I automate the creation of the the macro from original cell
contents of
=Datasheet!c15 or =+Datasheet!C15 to above hyperlink formula

Thanks very much.
Charles

 

[Dave Peterson]

Select the range to change and try this macro:

Option Explicit
Sub testme02()
Dim myRng As Range
Dim myCell As Range
Dim myStr As String

Set myRng = Selection
For Each myCell In myRng.Cells
If myCell.HasFormula Then
myStr = Mid(myCell.Formula, 2)
If Left(myStr, 1) = "+" Then
myStr = Mid(myStr, 2)
End If
'=HYPERLINK("#"&CELL("address",Datasheet!C15),Datasheet!C15)
myCell.Formula = "=hyperlink(""#""&cell(""address""," _
& myStr & ")," & myStr & ")"
End If
Next myCell

End Sub

 

[Guest]

Thanks Dave, I always find your posts work beautifully. One quick question,
why does it have to have Option Explicit?

Thanks

 

[Guest]

Never mind, found Option Explicit explanation

Thanks Dave, I always find your posts work beautifully. One quick question,
why does it have to have Option Explicit?

Thanks

 

[Dave Peterson]

And it's my passive aggressive way of trying to get everyone to use Option
Explicit!

 

[Tweedy]

dave,

I was trying to build off of your formula for inserting a hyperlink but keep
getting a
?NAME error. I am looping through a range of cells that I pre-select and
looking to see if a link is needed. If so I need to build it off of COL A in
the same row (1st 5 chars) and a number based on what iteration of a similar
group it is at.

The link builds but shows up as the literal text and the variable name
literally instead of the value it should contain.
=HYPERLINK("http://www.wilions.org/youthexchange/YEAPPS/"& myStr &
ctryCounter & link_suffix, "link")

How do I get the variables to come through as text?

Sub Create_Hyperlink_based_On_country_Name()
Dim myRng As Range
Dim myCell As Range
Dim myStr As String
Dim ctryCompare As String
Dim Counter As Integer
Dim ctryCounter As Integer
Dim appFormula As String
Dim link_suffix As String

link_suffix = ".pdf"

Counter = 5
myStr = Left(Cells(5, 1), 5)
ctryCompare = myStr
Set myRng = Selection
For Each myCell In myRng.Cells
If myCell.Value = "link" Then ' is a link needed?
myStr = Left(Cells(Counter, 1), 5) ' Yes, so get country
name (5 chars)
If myStr = ctryCompare Then ' Check if new country
ctryCounter = ctryCounter + 1 ' track what # to use
for link
Else: ctryCounter = 1 'New Country
End If

Counter = Counter + 1
myCell.Formula = "=hyperlink(""http://www.website.org/""& myStr
& _
ctryCounter & link_suffix,
""link"")"

Else: Counter = Counter + 1
End If
Next myCell


End Sub

 

[zak]

Hi Dave

I have been looking for some hyperlink help and have found your posts have
been quite helpful to others, and so i thought you may be able to help me
with a problem i am having.

I have a form to enter text (and the text gets copied to a worksheet). But
within the form I would like to add a browse button to find a file, which
then adds the path of that file to colmun H as a hyperlink in the same
worksheet...so that when you click on the hyperlink, the file opens up. The
types of files are normal Word and Excel files which will either be located
on my local drive or a netwrok drive.

It's basically the normal Hyperlink button you get in any MS application,
but I just want it within a form and for the link to be copied into column H5
onwards.

Please let me know if you can help me?

Rightclick on the worksheet tab that has the hyperlinks, then select view code.
Paste this in:

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

With Target.Parent
.Hyperlinks.Delete
.ClearContents '??? clear the cell, too???
End With

End Sub

Or maybe...

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

With Target.Parent
If Intersect(.Cells, Me.Range("e7:h17")) Is Nothing Then
'do nothing
Else
.Hyperlinks.Delete
.ClearContents '??? clear the cell, too???
End If
End With

End Sub

If you want to limit the range.

 

[Dave Peterson]

You could add a label to the userform and format the text to look like a
hyperlink (blue, underlined). Then use the Label's _Click event to search for
the file--using application.getopenfilename.

Personally, I would think a browse commandbutton would look more natural to a
user.

Option Explicit
Private Sub Label1_Click()
Dim myFileName As Variant
Dim NextRow As Long

With ActiveSheet
NextRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
End With

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
Else
ActiveSheet.Cells(NextRow, "F").Formula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """,""click me"")"
End If

End Sub

Hi Dave

I have been looking for some hyperlink help and have found your posts have
been quite helpful to others, and so i thought you may be able to help me
with a problem i am having.

I have a form to enter text (and the text gets copied to a worksheet). But
within the form I would like to add a browse button to find a file, which
then adds the path of that file to colmun H as a hyperlink in the same
worksheet...so that when you click on the hyperlink, the file opens up. The
types of files are normal Word and Excel files which will either be located
on my local drive or a netwrok drive.

It's basically the normal Hyperlink button you get in any MS application,
but I just want it within a form and for the link to be copied into column H5
onwards.

Please let me know if you can help me?

 

[zak]

Dave

This is absolutely GREAT, thank you!
Is there any way, where you have said "click me" that I could change this so
that it shows the actual file path?

I have a text input box called DocFilePath in which I want it to show in, on
this userform?

Thanks

You could add a label to the userform and format the text to look like a
hyperlink (blue, underlined). Then use the Label's _Click event to search for
the file--using application.getopenfilename.

Personally, I would think a browse commandbutton would look more natural to a
user.

Option Explicit
Private Sub Label1_Click()
Dim myFileName As Variant
Dim NextRow As Long

With ActiveSheet
NextRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
End With

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
Else
ActiveSheet.Cells(NextRow, "F").Formula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """,""click me"")"
End If

End Sub

 

[zak]

Hi Dave

Please ignore my previous message. I added the msgBox line in and got a
message saying 1048577 followed by the text i entered in the OwnerN text
input box.

Any ideas?

Thanks

You could add a label to the userform and format the text to look like a
hyperlink (blue, underlined). Then use the Label's _Click event to search for
the file--using application.getopenfilename.

Personally, I would think a browse commandbutton would look more natural to a
user.

Option Explicit
Private Sub Label1_Click()
Dim myFileName As Variant
Dim NextRow As Long

With ActiveSheet
NextRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
End With

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
Else
ActiveSheet.Cells(NextRow, "F").Formula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """,""click me"")"
End If

End Sub

 

[zak]

Some good news........Please ignore my last e-mail Dave.

I have got the code working now somehow. Thanks for all of your help on
this. I really appreciate it.

Thanks

You could add a label to the userform and format the text to look like a
hyperlink (blue, underlined). Then use the Label's _Click event to search for
the file--using application.getopenfilename.

Personally, I would think a browse commandbutton would look more natural to a
user.

Option Explicit
Private Sub Label1_Click()
Dim myFileName As Variant
Dim NextRow As Long

With ActiveSheet
NextRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
End With

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
Else
ActiveSheet.Cells(NextRow, "F").Formula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """,""click me"")"
End If

End Sub

 

[Dave Peterson]

I'm confused. Does this mean that you have both problems resolved?

Some good news........Please ignore my last e-mail Dave.

I have got the code working now somehow. Thanks for all of your help on
this. I really appreciate it.

Thanks

 

[zak]

Hi Dave

Sorry, I did send you a few replies in one go yesterday and the order they
were supposed to reach you has gotten mixed up.

No the problem is not resolved. It only populates row 2, but that's it.
When i fill in the form again, it doesnt add the info to row 3 and so on. I
do still have the row code you stated in your last reply. But ill add the
whole code now, please let me know what I am doing wrong:

Private Sub SendAppReq_Click()
If Sheets("Appointments").Range("A2").Value = "" _
Then
R = 2
Else
R = Sheets("Appointments").Range("A2").End(xlDown).Row + 1
End If
With Sheets("Appointments")
..Range("A" & R).Value = BookApp.OwnerN.Text
..Range("B" & R).Value = BookApp.Email.Text
..Range("C" & R).Value = BookApp.AppDur.Text
..Range("D" & R).Value = BookApp.LaptopDetails.Text
..Range("E" & R).Value = BookApp.PropDate.Text
..Range("F" & R).Value = BookApp.PropTime.Text
If BookApp.CheckBox1.Value = True Then
..Range("G" & R).Value = "Yes"
Else
..Range("G" & R).Value = "" '"no" '???
End If
End With
Unload BookApp
End Sub

Thanking you in advance.

 

[zak]

Hi Dave

I have not solved my hyperlink issues also, so please can you still offer
your help on this. My query yesterday was - Is there any way, where you have
said "click me" that I could change this so that it shows the actual file
path?

I have a text input box called DocFilePath in which I want the link to show
in, on
this userform?

This is the code I have at the moment:

Private Sub CommandButton4_Click()
Dim myFileName As Variant
Dim NextRow As Long

With ActiveSheet
NextRow = .Cells(.Rows.Count, "I").End(xlUp).Row + 1
End With

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
Else
ActiveSheet.Cells(NextRow, "I").Formula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """,""click me"")"
End If
End Sub

Thanks Dave, sorry for the mix up.

You could add a label to the userform and format the text to look like a
hyperlink (blue, underlined). Then use the Label's _Click event to search for
the file--using application.getopenfilename.

Personally, I would think a browse commandbutton would look more natural to a
user.

Option Explicit
Private Sub Label1_Click()
Dim myFileName As Variant
Dim NextRow As Long

With ActiveSheet
NextRow = .Cells(.Rows.Count, "F").End(xlUp).Row + 1
End With

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
Else
ActiveSheet.Cells(NextRow, "F").Formula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """,""click me"")"
End If

End Sub

 

[Dave Peterson]

You can remove the "click me" portion to see the path.

ActiveSheet.Cells(NextRow, "I").Formula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """)"


And you can use
me.docfilepath.value = myfilename

I think I would display this in a label. Textboxes seem better for getting
input from the user than displaying info.

Hi Dave

I have not solved my hyperlink issues also, so please can you still offer
your help on this. My query yesterday was - Is there any way, where you have
said "click me" that I could change this so that it shows the actual file
path?

I have a text input box called DocFilePath in which I want the link to show
in, on
this userform?

This is the code I have at the moment:

Private Sub CommandButton4_Click()
Dim myFileName As Variant
Dim NextRow As Long

With ActiveSheet
NextRow = .Cells(.Rows.Count, "I").End(xlUp).Row + 1
End With

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
Else
ActiveSheet.Cells(NextRow, "I").Formula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """,""click me"")"
End If
End Sub

Thanks Dave, sorry for the mix up.

 

[Dave Peterson]

It sounds like A2 (changed from A3!) is non-empty, but A3 is empty.

You'll have to check two cells before you do the .end(xldown).row stuff.

with worksheets("appointments")
if isempty(.range("a2").value then
R = 2
elseif isempty(.range("a3").value then
r = 3
else
r = .range("a2").end(xldown).row + 1
end if
end with

Try it manually and you'll see what's going on.

In a test worksheet, put something in A2 (and nothing else). You'll see that
you go to the bottom of the column after hitting end|down.

Put something in A2 and A3 and do the same test. And you'll see that you stop
where you want.

==========
I've always found that working from the bottom up is easier.

with worksheets("appointments")
'stop at the last used cell or row 1
r = .cells(.rows.count,"A").end(xlup).row
if isempty(.cells(r,"A").value) then
'do nothing, this is an empty cell
else
'come down one
r = r + 1
end if
'check to see if it's bigger than 2 (or 3????)
if r < 2 then
r = 2
end if
end with

Hi Dave

Sorry, I did send you a few replies in one go yesterday and the order they
were supposed to reach you has gotten mixed up.

No the problem is not resolved. It only populates row 2, but that's it.
When i fill in the form again, it doesnt add the info to row 3 and so on. I
do still have the row code you stated in your last reply. But ill add the
whole code now, please let me know what I am doing wrong:

Private Sub SendAppReq_Click()
If Sheets("Appointments").Range("A2").Value = "" _
Then
R = 2
Else
R = Sheets("Appointments").Range("A2").End(xlDown).Row + 1
End If
With Sheets("Appointments")
.Range("A" & R).Value = BookApp.OwnerN.Text
.Range("B" & R).Value = BookApp.Email.Text
.Range("C" & R).Value = BookApp.AppDur.Text
.Range("D" & R).Value = BookApp.LaptopDetails.Text
.Range("E" & R).Value = BookApp.PropDate.Text
.Range("F" & R).Value = BookApp.PropTime.Text
If BookApp.CheckBox1.Value = True Then
.Range("G" & R).Value = "Yes"
Else
.Range("G" & R).Value = "" '"no" '???
End If
End With
Unload BookApp
End Sub

Thanking you in advance.

 

[zak]

Hi Dave

Thanks for giving the code in your last response. It work as I wanted.
There is something else Id like to ask you in connection with this....

The form in which the browse buttons sits has other information which needs
to be entered. There are 2 radion buttons called radiobutton1 and
radiobutton2, depending on which button is selected the information is copied
into the respective sheet (laptop1 or laptop2). Below is the code for this
condition:

Private Sub BtnSaveInfo_Click()
Dim Sht As Worksheet

If Laptop1.Value = True Then
Set Sht = ThisWorkbook.Worksheets("Toshiba (00226)")
Sht.Range("A4").Value = TextBox2.Text
Sht.Range("B4").Value = RecDate.Text
Sht.Range("C4").Value = RSVers.Text
Sht.Range("D4").Value = CachVers.Text
Sht.Range("E4").Value = ApacVers.Text
Sht.Range("F4").Value = TomcVers.Text
Sht.Range("G4").Value = JavVers.Text
Sht.Range("H4").Value = TextBox1.Text
Sht.Range("J4").Value = mailworkdone1.Text

End If
If Laptop2.Value = True Then
Set Sht = ThisWorkbook.Worksheets("Dell (B000234)")
Sht.Range("A4").Value = TextBox2.Text
Sht.Range("B4").Value = RecDate.Text
Sht.Range("C4").Value = RSVers.Text
Sht.Range("D4").Value = CachVers.Text
Sht.Range("E4").Value = ApacVers.Text
Sht.Range("F4").Value = TomcVers.Text
Sht.Range("G4").Value = JavVers.Text
Sht.Range("H4").Value = TextBox1.Text
Sht.Range("J4").Value = mailworkdone1.Text
End If
End Sub

Now, what Id like to do is integrate the hyperlink code with the above, so
that depending on which radio button is selected the hyperlink file path gets
copied into the respective sheet (laptop1 or laptop2) in row I the same time
as when all the above info gets copied (as it is a part of the info in that
row). tHIS IS THE HYPERLINK CODE THAT i HAVE:

Private Sub CommandButton4_Click()
Dim myFileName As Variant
Dim NextRow As Long

With ActiveSheet
NextRow = .Cells(.Rows.Count, "I").End(xlUp).Row + 1
End With

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
Else
ActiveSheet.Cells(NextRow, "I").Formula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """)"
Me.DocFilePath.Value = myFileName
End If
End Sub

iS THERE anyway to link the two pieces of code?

Thanks Dave

 

[Dave Peterson]

In I4 of either sheet--or the next row in either sheet?



Private Sub BtnSaveInfo_Click()
Dim Sht As Worksheet

Dim myFileName As Variant
Dim NextRow As Long
dim myFormula as string

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
myformula = ""
Else
myformula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """)"
Me.DocFilePath.Value = myFileName
End If

If Laptop1.Value = True Then
Set Sht = ThisWorkbook.Worksheets("Toshiba (00226)")
else
Set Sht = ThisWorkbook.Worksheets("Dell (B000234)")
end if

with Sht
Sht.Range("A4").Value = TextBox2.Text
Sht.Range("B4").Value = RecDate.Text
Sht.Range("C4").Value = RSVers.Text
Sht.Range("D4").Value = CachVers.Text
Sht.Range("E4").Value = ApacVers.Text
Sht.Range("F4").Value = TomcVers.Text
Sht.Range("G4").Value = JavVers.Text
Sht.Range("H4").Value = TextBox1.Text
Sht.Range("J4").Value = mailworkdone1.Text

'not sure which one should be used
NextRow = .Cells(.Rows.Count, "I").End(xlUp).Row + 1
sht.Cells(NextRow, "I").Formula = myFormula
'or
'sht.range("I4").formula = myformula

end with
End Sub

Uncompiled, untested.

If you only have two option buttons, you can probably check one and decide what
to. And since the cells getting the value are the same addresses, you can just
use that "with sht" after deciding which sheet to use.

Hi Dave

Thanks for giving the code in your last response. It work as I wanted.
There is something else Id like to ask you in connection with this....

The form in which the browse buttons sits has other information which needs
to be entered. There are 2 radion buttons called radiobutton1 and
radiobutton2, depending on which button is selected the information is copied
into the respective sheet (laptop1 or laptop2). Below is the code for this
condition:

Private Sub BtnSaveInfo_Click()
Dim Sht As Worksheet

If Laptop1.Value = True Then
Set Sht = ThisWorkbook.Worksheets("Toshiba (00226)")
Sht.Range("A4").Value = TextBox2.Text
Sht.Range("B4").Value = RecDate.Text
Sht.Range("C4").Value = RSVers.Text
Sht.Range("D4").Value = CachVers.Text
Sht.Range("E4").Value = ApacVers.Text
Sht.Range("F4").Value = TomcVers.Text
Sht.Range("G4").Value = JavVers.Text
Sht.Range("H4").Value = TextBox1.Text
Sht.Range("J4").Value = mailworkdone1.Text

End If
If Laptop2.Value = True Then
Set Sht = ThisWorkbook.Worksheets("Dell (B000234)")
Sht.Range("A4").Value = TextBox2.Text
Sht.Range("B4").Value = RecDate.Text
Sht.Range("C4").Value = RSVers.Text
Sht.Range("D4").Value = CachVers.Text
Sht.Range("E4").Value = ApacVers.Text
Sht.Range("F4").Value = TomcVers.Text
Sht.Range("G4").Value = JavVers.Text
Sht.Range("H4").Value = TextBox1.Text
Sht.Range("J4").Value = mailworkdone1.Text
End If
End Sub

Now, what Id like to do is integrate the hyperlink code with the above, so
that depending on which radio button is selected the hyperlink file path gets
copied into the respective sheet (laptop1 or laptop2) in row I the same time
as when all the above info gets copied (as it is a part of the info in that
row). tHIS IS THE HYPERLINK CODE THAT i HAVE:

Private Sub CommandButton4_Click()
Dim myFileName As Variant
Dim NextRow As Long

With ActiveSheet
NextRow = .Cells(.Rows.Count, "I").End(xlUp).Row + 1
End With

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
Else
ActiveSheet.Cells(NextRow, "I").Formula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """)"
Me.DocFilePath.Value = myFileName
End If
End Sub

iS THERE anyway to link the two pieces of code?

Thanks Dave

 

[zak]

Dave

Thank you for being ever so kind and helping me out with this. It works
perfectly.

Everytime you respond to me, I always want to take the code further and make
it more dynamic.

You know how everytime the form is being filled in and I've hard coded it in
so that it all gets written to A2, B2, C2 and so on.... well I want to add
the new info to the next available row each time the form is filled in.

Here is my code, Please Dave let me know if you can help. Ive tried that
code where you do:

If Sheets("Appointments").Range("A2").Value = "" _
Then
R = 2
Else
R = Sheets("Appointments").Range("A1").End(xlDown).Row + 1
End If

Bur it doesnt work, I dont know how to fit it into the current code, which
is below:

Private Sub BtnSaveInfo_Click()
Dim Sht As Worksheet

Dim myFileName As Variant
Dim NextRow As Long
Dim myFormula As String

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
myFormula = ""
Else
myFormula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """)"
'Me.DocFilePath.Value = myFileName
End If

If Laptop1.Value = True Then
Set Sht = ThisWorkbook.Worksheets("Toshiba (00226)")
Else
Set Sht = ThisWorkbook.Worksheets("Dell (B000234)")
End If

With Sht
Sht.Range("A2").Value = TextBox2.Text
Sht.Range("B2").Value = RecDate.Text
Sht.Range("C2").Value = RSVers.Text
Sht.Range("D2").Value = CachVers.Text
Sht.Range("E2").Value = ApacVers.Text
Sht.Range("F2").Value = TomcVers.Text
Sht.Range("G2").Value = JavVers.Text
Sht.Range("H2").Value = TextBox1.Text
Sht.Range("J2").Value = mailworkdone1.Text
Sht.Range("I2").Formula = myFormula

End With
WorkCompleted.Hide
End Sub

 

[Dave Peterson]

I'm confused.

Do you want to write the values to a worksheet named "Appointments",
"Toshiba (00226)", or "Dell (B000234)"?

I'm guessing that Appointments doesn't matter anymore.

Option Explicit
Private Sub BtnSaveInfo_Click()

Dim Sht As Worksheet
Dim myFileName As Variant
Dim NextRow As Long
Dim myFormula As String

myFileName = Application.GetOpenFilename(filefilter:="All Files, *.*")
If myFileName = False Then
Beep 'do nothing???
myFormula = ""
Else
myFormula _
= "=hyperlink(""file:////""&" _
& """" & myFileName & """)"

Me.DocFilePath.Value = myFileName
End If

If Laptop1.Value = True Then
Set Sht = ThisWorkbook.Worksheets("Toshiba (00226)")
Else
Set Sht = ThisWorkbook.Worksheets("Dell (B000234)")
End If

With Sht
'what column can be used for the nextrow
'change "I" to the column letter that you know always has
'data when the row is used.
NextRow = .Cells(.Rows.Count, "I").End(xlUp).Row + 1
Sht.Cells(NextRow, "A").Value = TextBox2.Text
Sht.Cells(NextRow, "B").Value = RecDate.Text
Sht.Cells(NextRow, "C").Value = RSVers.Text
Sht.Cells(NextRow, "D").Value = CachVers.Text
Sht.Cells(NextRow, "E").Value = ApacVers.Text
Sht.Cells(NextRow, "F").Value = TomcVers.Text
Sht.Cells(NextRow, "G").Value = JavVers.Text
Sht.Cells(NextRow, "H").Value = TextBox1.Text
Sht.Cells(NextRow, "J").Value = mailworkdone1.Text
Sht.Cells(NextRow, "I").Formula = myFormula
End With
End Sub