Macro add Hyperlink formula
 

A formula that finally works (thanks Dave Ritchie) is
=HYPERLINK("#"&CELL("address",Datasheet!C15),Datas heet!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

Macro add Hyperlink formula
 

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),Data sheet!C15)
myCell.Formula = "=hyperlink(""#""&cell(""address""," _
& myStr & ")," & myStr & ")"
End If
Next myCell

End Sub


cmarch wrote:

A formula that finally works (thanks Dave Ritchie) is
=HYPERLINK("#"&CELL("address",Datasheet!C15),Datas heet!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

Macro add Hyperlink formula
 

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

Thanks

"Dave Peterson" wrote:

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),Data sheet!C15)
myCell.Formula = "=hyperlink(""#""&cell(""address""," _
& myStr & ")," & myStr & ")"
End If
Next myCell

End Sub


cmarch wrote:

A formula that finally works (thanks Dave Ritchie) is
=HYPERLINK("#"&CELL("address",Datasheet!C15),Datas heet!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

Macro add Hyperlink formula
 

Never mind, found Option Explicit explanation

"cmarch" wrote:

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

Thanks

"Dave Peterson" wrote:

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),Data sheet!C15)
myCell.Formula = "=hyperlink(""#""&cell(""address""," _
& myStr & ")," & myStr & ")"
End If
Next myCell

End Sub


cmarch wrote:

A formula that finally works (thanks Dave Ritchie) is
=HYPERLINK("#"&CELL("address",Datasheet!C15),Datas heet!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

Macro add Hyperlink formula
 

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

cmarch wrote:

Never mind, found Option Explicit explanation

"cmarch" wrote:

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

Thanks

"Dave Peterson" wrote:

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),Data sheet!C15)
myCell.Formula = "=hyperlink(""#""&cell(""address""," _
& myStr & ")," & myStr & ")"
End If
Next myCell

End Sub


cmarch wrote:

A formula that finally works (thanks Dave Ritchie) is
=HYPERLINK("#"&CELL("address",Datasheet!C15),Datas heet!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


--

Dave Peterson

Macro add Hyperlink formula
 

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle


--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

Macro add Hyperlink formula
 

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson

Macro add Hyperlink formula
 

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
--
Ray Tweedale
"Dave Peterson" wrote:

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),Data sheet!C15)
myCell.Formula = "=hyperlink(""#""&cell(""address""," _
& myStr & ")," & myStr & ")"
End If
Next myCell

End Sub


cmarch wrote:

A formula that finally works (thanks Dave Ritchie) is
=HYPERLINK("#"&CELL("address",Datasheet!C15),Datas heet!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

Macro add Hyperlink formula
 

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?



"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson

Macro add Hyperlink formula
 

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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


"Dave Peterson" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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



"Dave Peterson" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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

"Dave Peterson" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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

zak wrote:

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

"Dave Peterson" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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.



"Dave Peterson" wrote:

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

zak wrote:

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

"Dave Peterson" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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.




zak wrote:

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" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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

zak wrote:

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.

"Dave Peterson" wrote:

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

zak wrote:

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

"Dave Peterson" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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" wrote:

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.




zak wrote:

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" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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.



zak wrote:

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" wrote:

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.




zak wrote:

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" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Macro add Hyperlink formula
 

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" wrote:

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.



zak wrote:

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" wrote:

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.




zak wrote:

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" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson


--

Macro add Hyperlink formula
 

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


zak wrote:

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" wrote:

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.



zak wrote:

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" wrote:

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.




zak wrote:

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" wrote:

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 wrote:

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?

"Dave Peterson" wrote:

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.



twinklejmj wrote:

Hi
I desperately look for a macro to remove/ delete the hyperlinks which
are already clicked by the users from a set of hyperlinks, the moment
it is clicked. I have a set of hyperlinks in a column, which I set blue
in color.

Please help!!

Thanks a lot

Twinkle

--
twinklejmj
------------------------------------------------------------------------
twinklejmj's Profile: http://www.excelforum.com/member.php...o&userid=27085
View this thread: http://www.excelforum.com/showthread...hreadid=466303

--

Dave Peterson


--

Dave Peterson


--


--

Dave Peterson

Macro add Hyperlink formula
 

Hi Dave

No - the "Appointments" sheet is not relating to me query, I'm still talking
about the Toshiba and dell sheets. I'll give the code a try now. Thanks
again.



"Dave Peterson" wrote:

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


zak wrote:

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" wrote:

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.



zak wrote:

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" wrote:

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.




zak wrote:

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

Macro add Hyperlink formula
 

Hi Dave

The code you have given me below works a treat, as I wanted. So, THANK YOU
VERY MUCH! I adapted your code to help me in other areas of my spreadsheet
system which I had similar issues that new rows were not being added, so
thanks again for all your help.

There was something else, I had posted another issue on here, which i did
get a response for, but I had to e-mail the person again and have had no
reply in a few days because I needed further help. So, if you can help me
with this too, it will be greatly appreciated, hopefully I can meet my
deadline too:

When the Toshiba (00226) sheet gets populated from info entered into the
form, I've added a new column,K, which the heading is "Complete?". Then in
column K i have created a list drop down (via Data - Validation - Allow:List
etc) with two options (1. Complete or 2. Pending). My intention is that when
Complete is selected from the drop down for a particular row, I'd like the
row from A:K to be cut and pasted into the Toshiba_History sheet from row 2
onwards, as row 1 contains headings). Then once pasted, that row in Toshiba
(00226) should get deleted.

After this, any new rows in Toshiba (00226) that have complete in row K
should get pasted in a new row in Toshiba_History (so it's the adding the
info to a new row again).

Please let me know if you can help. The person helping me before has not
responded and my deadline has been set by my manager for this Friday.

Here is my code that I have, but it doesn't work as I wanted i.e. it copies
the whole row when I just want it to cut the row from column A to K, and once
copied, the info on Toshiba (00226) does not get deleted.

Sub PasteTosh()
'Sheet3 .....all data..(Toshiba (00226))
'Sheet6......contains filtered data_(Toshiba_History)
Dim i, j, n As Integer
j = 1
Sheet3.Activate
For i = 1 To Sheet3.UsedRange.Rows.Count
If (Cells(i, 11) = "Complete") Then
For n = 1 To Sheet3.UsedRange.Columns.Count
Sheet6.Cells(j, n) = Sheet3.Cells(i, n)
Next n
j = j + 1
End If
Next i
End Sub







"Dave Peterson" wrote:

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


zak wrote:

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" wrote:

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.



zak wrote:

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" wrote:

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.




zak wrote:

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

Macro add Hyperlink formula
 

Maybe...


Option Explicit
Sub PasteTosh2()

Dim FromWks As Worksheet
Dim FirstRow As Long
Dim ToWks As Worksheet
Dim FromRow As Long
Dim DestCell As Range

Set FromWks = Worksheets("Toshiba (00226)")
Set ToWks = Worksheets("toshiba_history")

With ToWks
'assumes that column A is always used!
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With FromWks
FirstRow = 2 'headers in row 1???
For FromRow = .Cells(.Rows.Count, "K").Row To FirstRow Step -1
If LCase(.Cells(FromRow, "K").Value) = LCase("Complete") Then
'copy 11 columns to the destination cell
.Cells(FromRow, "A").Resize(1, 11).Copy _
Destination:=DestCell
'delete that entire row
.Rows(FromRow).Delete
'get ready for the next one (move down a row)
Set DestCell = DestCell.Offset(1, 0)
End If
Next FromRow
End With
End Sub

(Untested, but it did compile.)

You may want to look at Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

If you ever have more options and each option gets its own worksheet



zak wrote:

Hi Dave

The code you have given me below works a treat, as I wanted. So, THANK YOU
VERY MUCH! I adapted your code to help me in other areas of my spreadsheet
system which I had similar issues that new rows were not being added, so
thanks again for all your help.

There was something else, I had posted another issue on here, which i did
get a response for, but I had to e-mail the person again and have had no
reply in a few days because I needed further help. So, if you can help me
with this too, it will be greatly appreciated, hopefully I can meet my
deadline too:

When the Toshiba (00226) sheet gets populated from info entered into the
form, I've added a new column,K, which the heading is "Complete?". Then in
column K i have created a list drop down (via Data - Validation - Allow:List
etc) with two options (1. Complete or 2. Pending). My intention is that when
Complete is selected from the drop down for a particular row, I'd like the
row from A:K to be cut and pasted into the Toshiba_History sheet from row 2
onwards, as row 1 contains headings). Then once pasted, that row in Toshiba
(00226) should get deleted.

After this, any new rows in Toshiba (00226) that have complete in row K
should get pasted in a new row in Toshiba_History (so it's the adding the
info to a new row again).

Please let me know if you can help. The person helping me before has not
responded and my deadline has been set by my manager for this Friday.

Here is my code that I have, but it doesn't work as I wanted i.e. it copies
the whole row when I just want it to cut the row from column A to K, and once
copied, the info on Toshiba (00226) does not get deleted.

Sub PasteTosh()
'Sheet3 .....all data..(Toshiba (00226))
'Sheet6......contains filtered data_(Toshiba_History)
Dim i, j, n As Integer
j = 1
Sheet3.Activate
For i = 1 To Sheet3.UsedRange.Rows.Count
If (Cells(i, 11) = "Complete") Then
For n = 1 To Sheet3.UsedRange.Columns.Count
Sheet6.Cells(j, n) = Sheet3.Cells(i, n)
Next n
j = j + 1
End If
Next i
End Sub

"Dave Peterson" wrote:

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


zak wrote:

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" wrote:

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.



zak wrote:

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" wrote:

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.




zak wrote:

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


--

Dave Peterson

Macro add Hyperlink formula
 

Thanks Dave

Your code works perfectly. Thank you for all your help.

Now - This is my last question for now, i promise, do you know anything
about sendin automatic e-mail where I won't have to even create a button to
send the e-mail, nor open the workbook up - i want it to be totally auto
generated. I have code that works fine, but i have to press a button in
order for it to work. But I want something, where its completely auto
generated.

It's OK if you can't help, Im grateful for all your help thus far. But if
you think you can help, then please see my code below, which runs from
pressing a button:

Thanks in advance:

Sub TestFile_2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In
Sheets("RSReleaseDates").Columns("B").Cells.Specia lCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) < "Sent" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder - New RhymeSIGHT Release Coming Soon"
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"A new version of RhymeSIGHT is due to be released 7
days from receipt of this e-mail." & vbNewLine & vbNewLine & _
"Please e-mail me to arrange a date to upgrade your
laptop." & vbNewLine & vbNewLine & _
"Thank You." & vbNewLine & vbNewLine & _
"(YOUR NAME)" & vbNewLine & vbNewLine & _
"On Behalf of Support Services"
.Display 'or use Send
End With
On Error GoTo 0

cell.Offset(0, 2).Value = "Sent"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub




"Dave Peterson" wrote:

Maybe...


Option Explicit
Sub PasteTosh2()

Dim FromWks As Worksheet
Dim FirstRow As Long
Dim ToWks As Worksheet
Dim FromRow As Long
Dim DestCell As Range

Set FromWks = Worksheets("Toshiba (00226)")
Set ToWks = Worksheets("toshiba_history")

With ToWks
'assumes that column A is always used!
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With FromWks
FirstRow = 2 'headers in row 1???
For FromRow = .Cells(.Rows.Count, "K").Row To FirstRow Step -1
If LCase(.Cells(FromRow, "K").Value) = LCase("Complete") Then
'copy 11 columns to the destination cell
.Cells(FromRow, "A").Resize(1, 11).Copy _
Destination:=DestCell
'delete that entire row
.Rows(FromRow).Delete
'get ready for the next one (move down a row)
Set DestCell = DestCell.Offset(1, 0)
End If
Next FromRow
End With
End Sub

(Untested, but it did compile.)

You may want to look at Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

If you ever have more options and each option gets its own worksheet



zak wrote:

Hi Dave

The code you have given me below works a treat, as I wanted. So, THANK YOU
VERY MUCH! I adapted your code to help me in other areas of my spreadsheet
system which I had similar issues that new rows were not being added, so
thanks again for all your help.

There was something else, I had posted another issue on here, which i did
get a response for, but I had to e-mail the person again and have had no
reply in a few days because I needed further help. So, if you can help me
with this too, it will be greatly appreciated, hopefully I can meet my
deadline too:

When the Toshiba (00226) sheet gets populated from info entered into the
form, I've added a new column,K, which the heading is "Complete?". Then in
column K i have created a list drop down (via Data - Validation - Allow:List
etc) with two options (1. Complete or 2. Pending). My intention is that when
Complete is selected from the drop down for a particular row, I'd like the
row from A:K to be cut and pasted into the Toshiba_History sheet from row 2
onwards, as row 1 contains headings). Then once pasted, that row in Toshiba
(00226) should get deleted.

After this, any new rows in Toshiba (00226) that have complete in row K
should get pasted in a new row in Toshiba_History (so it's the adding the
info to a new row again).

Please let me know if you can help. The person helping me before has not
responded and my deadline has been set by my manager for this Friday.

Here is my code that I have, but it doesn't work as I wanted i.e. it copies
the whole row when I just want it to cut the row from column A to K, and once
copied, the info on Toshiba (00226) does not get deleted.

Sub PasteTosh()
'Sheet3 .....all data..(Toshiba (00226))
'Sheet6......contains filtered data_(Toshiba_History)
Dim i, j, n As Integer
j = 1
Sheet3.Activate
For i = 1 To Sheet3.UsedRange.Rows.Count
If (Cells(i, 11) = "Complete") Then
For n = 1 To Sheet3.UsedRange.Columns.Count
Sheet6.Cells(j, n) = Sheet3.Cells(i, n)
Next n
j = j + 1
End If
Next i
End Sub

"Dave Peterson" wrote:

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


zak wrote:

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" wrote:

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.



zak wrote:

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

Macro add Hyperlink formula
 

If you're going to stay in excel, you'll have to open excel and the workbook.
If you don't want to click the button, you could use the Auto_Open or
workbook_open procedures that run when the workbook is opened.

And if you don't want to even open excel and the workbook, maybe you could use a
scheduling program (some versions of windows have their own, but you can find
others on google).

And if you haven't looked at Ron de Bruin's site, it may come in handy someday):
http://www.rondebruin.nl/tips.htm

Good luck!

zak wrote:

Thanks Dave

Your code works perfectly. Thank you for all your help.

Now - This is my last question for now, i promise, do you know anything
about sendin automatic e-mail where I won't have to even create a button to
send the e-mail, nor open the workbook up - i want it to be totally auto
generated. I have code that works fine, but i have to press a button in
order for it to work. But I want something, where its completely auto
generated.

It's OK if you can't help, Im grateful for all your help thus far. But if
you think you can help, then please see my code below, which runs from
pressing a button:

Thanks in advance:

Sub TestFile_2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In
Sheets("RSReleaseDates").Columns("B").Cells.Specia lCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" _
And LCase(cell.Offset(0, 2).Value) < "Sent" Then
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder - New RhymeSIGHT Release Coming Soon"
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
"A new version of RhymeSIGHT is due to be released 7
days from receipt of this e-mail." & vbNewLine & vbNewLine & _
"Please e-mail me to arrange a date to upgrade your
laptop." & vbNewLine & vbNewLine & _
"Thank You." & vbNewLine & vbNewLine & _
"(YOUR NAME)" & vbNewLine & vbNewLine & _
"On Behalf of Support Services"
.Display 'or use Send
End With
On Error GoTo 0

cell.Offset(0, 2).Value = "Sent"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

"Dave Peterson" wrote:

Maybe...


Option Explicit
Sub PasteTosh2()

Dim FromWks As Worksheet
Dim FirstRow As Long
Dim ToWks As Worksheet
Dim FromRow As Long
Dim DestCell As Range

Set FromWks = Worksheets("Toshiba (00226)")
Set ToWks = Worksheets("toshiba_history")

With ToWks
'assumes that column A is always used!
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With FromWks
FirstRow = 2 'headers in row 1???
For FromRow = .Cells(.Rows.Count, "K").Row To FirstRow Step -1
If LCase(.Cells(FromRow, "K").Value) = LCase("Complete") Then
'copy 11 columns to the destination cell
.Cells(FromRow, "A").Resize(1, 11).Copy _
Destination:=DestCell
'delete that entire row
.Rows(FromRow).Delete
'get ready for the next one (move down a row)
Set DestCell = DestCell.Offset(1, 0)
End If
Next FromRow
End With
End Sub

(Untested, but it did compile.)

You may want to look at Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

If you ever have more options and each option gets its own worksheet



zak wrote:

Hi Dave

The code you have given me below works a treat, as I wanted. So, THANK YOU
VERY MUCH! I adapted your code to help me in other areas of my spreadsheet
system which I had similar issues that new rows were not being added, so
thanks again for all your help.

There was something else, I had posted another issue on here, which i did
get a response for, but I had to e-mail the person again and have had no
reply in a few days because I needed further help. So, if you can help me
with this too, it will be greatly appreciated, hopefully I can meet my
deadline too:

When the Toshiba (00226) sheet gets populated from info entered into the
form, I've added a new column,K, which the heading is "Complete?". Then in
column K i have created a list drop down (via Data - Validation - Allow:List
etc) with two options (1. Complete or 2. Pending). My intention is that when
Complete is selected from the drop down for a particular row, I'd like the
row from A:K to be cut and pasted into the Toshiba_History sheet from row 2
onwards, as row 1 contains headings). Then once pasted, that row in Toshiba
(00226) should get deleted.

After this, any new rows in Toshiba (00226) that have complete in row K
should get pasted in a new row in Toshiba_History (so it's the adding the
info to a new row again).

Please let me know if you can help. The person helping me before has not
responded and my deadline has been set by my manager for this Friday.

Here is my code that I have, but it doesn't work as I wanted i.e. it copies
the whole row when I just want it to cut the row from column A to K, and once
copied, the info on Toshiba (00226) does not get deleted.

Sub PasteTosh()
'Sheet3 .....all data..(Toshiba (00226))
'Sheet6......contains filtered data_(Toshiba_History)
Dim i, j, n As Integer
j = 1
Sheet3.Activate
For i = 1 To Sheet3.UsedRange.Rows.Count
If (Cells(i, 11) = "Complete") Then
For n = 1 To Sheet3.UsedRange.Columns.Count
Sheet6.Cells(j, n) = Sheet3.Cells(i, n)
Next n
j = j + 1
End If
Next i
End Sub

"Dave Peterson" wrote:

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


zak wrote:

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" wrote:

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.



zak wrote:

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

--

Dave Peterson