Hi all,
Is it possible to create and add the hyperlink after the existing data
in the cell(that is, in the next line after the existing data in the
same cell). So what i'm trying to do is, to get an outlook email, store
email as .msg file in my folder and then add hyperlink to the just
stored file in a particular cell in my worksheet. I can do all the
things except for appending the hyperlink. As i create the hyperlink
and add it in the cell selected by user, all the data in that cell is
just overwritten by the hyperlink. So after this i only have the
hyperlink in the cell and all the data that was there in the cell is
lost. so is it possible to keep both , the existing data and hyperlink
added in the cell.
i tried reading the data of the cell into a variable before creating
and adding hyperling. then i added the hyperlink in the cell. then i
concatenated the data and hyperlink and then i made this concatination
as the value of cell. but with this process, all the data is converted
into the hyperlink, that is data that was there before the hyperlink is
also a part of hyperlink text now. so is it possible to have normal
text and hyperlink together in 1 cell.
my code is....

existingData = SelCells(j).Value
ActiveSheet.Hyperlinks.Add Anchor:=SelCells(j), _
Address:=FullPath, TextToDisplay:=Filename
hyperlinkString = SelCells(j).Value
SelCells(j).Value = existingData & vbCrLf & hyperlinkString


thanx for any help
amit

Amit..

does this help?


Sub TextWithAppendedHyperlink()

Dim txt$, url$
Dim colorTxt, colorUrl&

url = "http://www.google.com"
colorUrl = ActiveWorkbook.Styles("Hyperlink").Font.Color

With ActiveCell

txt = .Value
If InStr(txt, vbLf & url) = 0 Then txt = txt & vbLf & url

colorTxt = .Font.Color
If IsNull(colorTxt) Then colorTxt = .Characters(1).Font.Color

.Hyperlinks.Add _
Anchor:=.Cells(1), _
Address:=url, _
TextToDisplay:=txt

With .Font
.Color = colorTxt
.Underline = False
End With
With .Characters(InStr(txt, vbLf & url) + 1, Len(url)).Font
.Color = colorUrl
.Underline = True
End With
End With

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


helpwithXL wrote :

Hi all,
Is it possible to create and add the hyperlink after the existing data
in the cell(that is, in the next line after the existing data in the
same cell). So what i'm trying to do is, to get an outlook email,
store email as .msg file in my folder and then add hyperlink to the
just stored file in a particular cell in my worksheet. I can do all
the things except for appending the hyperlink. As i create the
hyperlink and add it in the cell selected by user, all the data in
that cell is just overwritten by the hyperlink. So after this i only
have the hyperlink in the cell and all the data that was there in the
cell is lost. so is it possible to keep both , the existing data and
hyperlink added in the cell.
i tried reading the data of the cell into a variable before creating
and adding hyperling. then i added the hyperlink in the cell. then i
concatenated the data and hyperlink and then i made this
concatination as the value of cell. but with this process, all the
data is converted into the hyperlink, that is data that was there
before the hyperlink is also a part of hyperlink text now. so is it
possible to have normal text and hyperlink together in 1 cell.
my code is....

existingData = SelCells(j).Value
ActiveSheet.Hyperlinks.Add Anchor:=SelCells(j), _
Address:=FullPath, TextToDisplay:=Filename
hyperlinkString = SelCells(j).Value
SelCells(j).Value = existingData & vbCrLf & hyperlinkString


thanx for any help
amit

thanx for your reply keepITcool.
It solved the problem whose answer i sought but the code u gave me
makes all the data in the cell (existing and hypertext) as the link. so
after implimenting your code, i can click on the text data also to
follow the link, which does not conform to my requirements. so
essentially i mean that if hyperlink is appended to the existing, user
should be able to follow the link only when he clicks on the hyperlink
and not on the existing text data. is it possible to separate text data
and hyperlink.
Also when I try to put more than 1 hyperlink, it does not work . I mean
that i can get the text of hyperlink but the link is only made for the
last hyperlink. so if text1 is the existing text, h1 is the 1st
hyperlink, h2 is the 2nd hyperlink and h3 is the 3rd hyperlink, then i
can see all the text of text1,h1,h2,h3 in the selected cell but link is
only made for h3. and this link for h3 can be followed by clicking
anywhere in the cell, that is if i click on either text1 or h1 or h2 ,
i still get to the address of h3.
sorry for such a big post but i wanted to be as clear as possible.
my code....
Dim txt$
Dim colorTxt, colorFullPath&

colorFullPath =
ActiveWorkbook.Styles("Hyperlink").Font.Color

With SelCells(j)
txt = .Value
If InStr(txt, vbLf & FullPath) = 0 Then txt =
txt & vbLf & FullPath

colorTxt = .Font.Color
If IsNull(colorTxt) Then colorTxt =
..Characters(1).Font.Color

.Hyperlinks.Add Anchor:=SelCells(j),
Address:=FullPath, TextToDisplay:=FullPath

With .Font
.Color = colorTxt
.Underline = False
End With
With .Characters(InStr(txt, vbLf & FullPath) +
1, Len(FullPath)).Font
.Color = colorFullPath
.Underline = True
End With
End With


regards
amit

afaik a cell can contain only 1 hyperlink
and yes.. the whole cell will react to a click.

if that is not conform your requirements,
then I'm afraid it cannot be done the way you require.

excel can do a lot.. but it cannot do everything.
maybe you have to put the hyperlinks in separate cells.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


helpwithXL wrote :

thanx for your reply keepITcool.
It solved the problem whose answer i sought but the code u gave me
makes all the data in the cell (existing and hypertext) as the link.
so after implimenting your code, i can click on the text data also to
follow the link, which does not conform to my requirements. so
essentially i mean that if hyperlink is appended to the existing, user
should be able to follow the link only when he clicks on the hyperlink
and not on the existing text data. is it possible to separate text
data and hyperlink.
Also when I try to put more than 1 hyperlink, it does not work . I
mean that i can get the text of hyperlink but the link is only made
for the last hyperlink. so if text1 is the existing text, h1 is the
1st hyperlink, h2 is the 2nd hyperlink and h3 is the 3rd hyperlink,
then i can see all the text of text1,h1,h2,h3 in the selected cell
but link is only made for h3. and this link for h3 can be followed by
clicking anywhere in the cell, that is if i click on either text1 or
h1 or h2 , i still get to the address of h3.
sorry for such a big post but i wanted to be as clear as possible.

thanx again for replying,
i just wanted to know if it is possible to have both normal text and
hyperlink in one cell? i was looking at user-defined data type but
until now i have not been able to have both string and hyperlink in the
user-defined data type and then assingning that to a particular cell.
again a reply will be much appreciated
regards
amit

Amit,

you are NOT going to get it done with a UDF. a UDF can only set the
value of a cell, not it's properties or behaviour.

you could possibly try something fancy with event handlers.

But it will be complex and you will NOT get it elegant:
you cant catch single click on a worksheet.
so you'd need double click.

then you must decide IF the cell contains any text that may be
interpreted as an url, or even would contain multiple urls..
if multiple urls you've got to figure out WHERE the user (double)
clicked exactly...

i wouldnt do it.

as said earlier: put you urls in multiple cells.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


helpwithXL wrote :

thanx again for replying,
i just wanted to know if it is possible to have both normal text and
hyperlink in one cell? i was looking at user-defined data type but
until now i have not been able to have both string and hyperlink in
the user-defined data type and then assingning that to a particular
cell. again a reply will be much appreciated
regards
amit