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 Justin


    It will require VBA. Right click on the sheet name tab (one housing the Hyperlink) and select View Code use the only one of appropriate codes from below

    Code
    1. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    2. 'For ComboBox From Forms Toolbar
    3. Me.Shapes("Drop Down 1").ControlFormat.LinkedCell = "$X$2"
    4. End Sub
    5. Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    6. 'For ComboBox From Control Toolbox Toolbar
    7. Me.ComboBox1.LinkedCell = "$X$2"
    8. End Sub
  • 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

    Files

    • outline.xls

      (26.62 kB, downloaded 150 times, last: )
  • 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

  • Try this code in the Sheet Module of Sheet1

    Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. Select Case Target.Address
    3. Case "$A$8"
    4. Range(Me.ComboBox1.LinkedCell) = "a"
    5. Case "$A$9"
    6. Range(Me.ComboBox1.LinkedCell) = "b"
    7. Case "$A$10"
    8. Range(Me.ComboBox1.LinkedCell) = "c"
    9. End Select
    10. End Sub
  • 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

    Files

    • outline.xls

      (29.7 kB, downloaded 154 times, last: )