Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell to display certain if an entry is provided
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell to display certain if an entry is provided
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. --- "Bryan" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell to display certain if an entry is provided
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'. "Bob Bridges" wrote: 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. --- "Bryan" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell to display certain if an entry is provided
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? --- "Bryan" wrote: 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'. --- "Bob Bridges" wrote: 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. --- "Bryan" wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to Don: display last entry to cell automatically | Excel Worksheet Functions | |||
display last entry into a cell automatically | Excel Worksheet Functions | |||
how can i display the date and time of a cell entry in excel? | Excel Discussion (Misc queries) | |||
Link cell between WS w/o dest display 0-jan if no entry at source | Excel Worksheet Functions | |||
How do i have a check display with any data entry in a cell | Excel Discussion (Misc queries) |