Cell to display certain if an entry is provided

B

Bryan

I have a column in my excel spreadsheet title WWW. It's used to list
hyperlinks to products on a company website. What I would like to do, is
when a user selects the desired cell in this column and hits cntrl+k to enter
a hyperlink, a formula will automatically insert the text 'link' when there
is an entry instead of the entire URL. Is this possible? I looked into
conditional formatting, but I cannot seem to figure out how to do what I want.
 
B

Bob Bridges

I don't know much about links in VBA/Excel, but you can set up an event
that's triggered whenever your user changes the contents of a cell; the event
code would check to see whether the cell is this special one, and if it is
could (I presume) do whatever is necessary to turn the entered text into a
link. I thought you could do a KeyPress event to check for <Ctl-K>, but I
don't see that available in the VBA/Excel documentation now that I look --
that is, not for worksheets or cells, though it is for user forms and
controls.

But maybe this simply means the user doesn't have to hit <Ctl-K>; just enter
the data and it works.
 
B

Bryan

Bob, thanks for the reply but I still cannot find a way to do what I want.
Hopefully bumping this up a little will result in more idea's. Just to
recap, when a use enters a URL in a cell, I don't want the entire, long, url
to be displayed instead a clickable link entitled 'www'.
 
B

Bob Bridges

Right: A user types a URL into any cell in a particular column that you'll
specify, and you want to get Excel automatically to turn the URL into a
clickable link. (What the link displays is a mere detail; once you know how
to create a link, you can make the displayed text ("WWW") and the actual URL
whatever you say they should be.) Here's what you need to know:

1) How to create a link in Excel. I've never done that before, but the Help
file seems clear enough. I expect you already know this part; if not, just
go to Help and enter "create hyperlink" to see the directions on that.

2) How to write a VBA program in general. I've been assuming you already
have this part under your belt; some people come here not knowing even that
(you gotta start somewhere, after all), but most folks here already know this
part. If you don't, this little task of yours actually isn't too bad a place
to start -- but we'll have to go over some fundamentals first or the actual
doing of it won't make any sense to you.

3) How to get a VBA program to run whenever a user changes a cell. For that
it seems to me you need a Worksheet_Change subroutine, which is what I was
referring to below, and my first guess was that this is the part you don't
already know how to do -- but maybe not, based on your reaction.

4) How to code the VBA statements that will create the link that you already
know how to create manually. I haven't tried that yet, but I expect once you
know the first three things, this would fall into place pretty naturally. So
I don't think this is what you're asking.

If you know all of the above four things, it seems to me, then you know how
to do what you're trying to do. Therefore I figure your question is how to
do one or more of the above. Which?
 
Top