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

hi keepitcool,
thanx again, i think now i (my boss) would have to go the u suggested,
multiple cells.
can u also help me with following???

just wondering if its possible to capture the pressing of enter key in
excel?.
normally if enter key is pressed in cell, next cell is selected. I want

to change (over ride) this behaviour of enter key. i want that when
user presses enter key inside the cell, the cursor should go to next
line in the same cell. right now "alt" key needs to pressed to achieve
this but is it possible to capture this event when enter key is pressed

so that i can change what it does.
any help is appreciated.

regards,
amit

your users are NOT going to like it if you're going to fiddle with the
enter key. you must truely be able to write bulletproof code if you do.

also:

normally if enter key is pressed in cell, next cell is selected. I
want

it is not 'normal' = it depends on the option
application.moveAfterReturn
If that option is TRUE then the 'next' cell is selected.
'the 'next' cell again depending on the option
Application.MoveAfterReturnDirection

DONT assume users will have this option set.
but if your going to handle the enter key be sure
to mimick this option.

ALSO.. those options are global.
so if you handle it for YOUR workbook, be sure to leave the behaviour
in other workbooks unaffected... how? that is the question.


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


helpwithXL wrote :

hi keepitcool,
thanx again, i think now i (my boss) would have to go the u suggested,
multiple cells.
can u also help me with following???

just wondering if its possible to capture the pressing of enter key in
excel?.
normally if enter key is pressed in cell, next cell is selected. I
want

to change (over ride) this behaviour of enter key. i want that when
user presses enter key inside the cell, the cursor should go to next
line in the same cell. right now "alt" key needs to pressed to achieve
this but is it possible to capture this event when enter key is
pressed

so that i can change what it does.
any help is appreciated.

regards,
amit

hello again keepitcool,
i do understand your point, but this a specific requirement by the user
that he does not want to go the next cell by pressing enter but to next
line in the same cell. if you make Application.MoveAfterReturn = false,
then pressing enter has no effect at all, but like i said, my user is
too lazy to press alt+enter to go to next line in same cell. would you
be kind enuf to tell me if there is a way in which i can change this
enter key behaviour?
also what do u mean by -- "be sure to mimick this option. "


regards,
amit

i rest my case.


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


helpwithXL wrote :

hello again keepitcool,
i do understand your point, but this a specific requirement by the
user that he does not want to go the next cell by pressing enter but
to next line in the same cell. if you make
Application.MoveAfterReturn = false, then pressing enter has no
effect at all, but like i said, my user is too lazy to press
alt+enter to go to next line in same cell. would you be kind enuf to
tell me if there is a way in which i can change this enter key
behaviour? also what do u mean by -- "be sure to mimick this option.
"


regards,
amit