change cell value on hyperlink click

Hello all,

This site has proven to be an invaluable resource, thankyou to everyone who posts both questions and responses. This is the first time I have posted a question although I have had a bit of a look I cant guarantee it hasnt been asked before, if so please point me in the right direction.

I have developed a basic front end for a simple database using the vlookup and index search functions.

Within the front end I have an if function that shows a hyperlink if a search cell is used and a result is obtained

=IF(ISTEXT(Properties!X2), HYPERLINK(CONCATENATE("#", Properties!X2,"hazard"), Properties!Z2), ""))

I also have a combobox that you can select from.

1. Is it possible to have the combobox linked-cell changed to a particular cell (eg. x2) value when someone clicks on the hyperlink cell (only if the hyperlink is active). 2. Does it require VBA or can it be managed using a function?
3. will it update the combobox?

Thanks in advance

Justin

Hi Dave, thankyou for the reply.

I cannot get the code to work so I have put together an explanatory excel book to show what I am after.

In sheet1 I would like to change the value cell labelled "outcell" ... sheet2!a9 (which is the linked cell for the combobox), to change to the respective value from the sheet (this will update another page that uses this cell to fill its values).

Thankyou for the help

Justin

Fair enough, I was just re-reading what I had written myself and it made no sense to me,
Try this:

I am trying to work it so that when you click on any of the hyperlinks (sheet1 a8,a9 and a10) that appear after you have entered text in the search cell (sheet1 a1) the value of the linked-cell for the combobox will change (to a value a, b or c depending upon which of the hyperlinks you click.)

sorry for the confusion, hope this helps.

Again thankyou for the help

Justin

forgot to mention,

the text entered in the search cell has to be something from apples, oranges or pears in order for the hyperlinks to appear.

Justin

Almost there me thinks

I think this is the idea that I want to happen, the workbook has had a couple of changes...

the code below (adapted from yours) does not even pretent to work but I am hoping you can see what I mean to do.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address

Case "$A$8"

if a8<> ""

Range("sheet1!b4").copy
Range("sheet2!a9").paste

Application.CutCopyMode = False

else nothing

case "$A$9"

if a9 <> ""

range("sheet1!b5").copy
range("sheet2!a9").paste

application.cutcopymode = false

else nothing

end sub

I guess I just need to work out a couple of things with the code...

for example why range("x").copy works as code but range("y").paste does not.

what do you use to make the macro activate only if there is a hyperlink showing (so that if you get a mad-clicker user you dont wind up with blank cells being copied)

and finally whether the hyperlink will continue as usual once the macro has run.

Thankyou for your time with this

Justin