Is there an event like Worksheet_BeforeHyperlink or a workaround?

  • Hi all,
    In Sheet1 I have a cell where an Hyperlink links to Sheet2.
    I'd like to set the back color of an Activex control button to red in Sheet1, the same where I click on the Hyperlink, right after I click but before control is passed to Sheet2 so that, when I go back to Sheet1, the color of the button is red.
    Is there a way to do it by using an event, something like Worksheet_BeforeHyperlink, or maybe someone can suggest a workaround?


    Thank you!

  • Assuming that the cell with the hyperlink in is cell A1, copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Change the target range to suit your needs. Close the code window to return to your sheet. Click the hyperlink.

    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
        Sheets("Sheet1").CommandButton1.BackColor = 500
    End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thank you Mumps, but the sub Worksheet_SelectionChange is not executed; To make sure I also put an interruption point at the left of it to see what it was doing but after I clicked on the hyperlink nothing happened and I went straight to Sheet2, on the contrary when I did the same for other events the execution stopped showing me the associated sub was being executed. Putting the sub in a Sheet or Module made no difference.
    Do you know why this happens?
    Thank you
    Guido

  • When I tried the macro on a dummy sheet, it worked properly. Could you post a copy of your file which includes the macros you are currently using? De-sensitize the data if necessary.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I just did what you did: I set up a new sheet with nothing in it but the code you gave me, a button and a cell which hyperlinks to a sheet in another file; it does't work. I realize I didn't mention the underlined words before, sorry; could it be the reason it doesn't work?
    What do you mean by 'De-sensitize the data'?
    Thank you for your patience; Im just a little above the beginner status.


    P.S. I attached the two files I just tested; I'm afraid they are in Italian. The format shouldn't be much different from English Excel but if you'd like a translation just ask.

  • Try:


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
        Dim desWS As Worksheet
        Set desWS = ThisWorkbook.Sheets("Sheet1")
        desWS.Activate
        ActiveSheet.CommandButton1.BackColor = 500
    End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • The link to SSS.xlsx doesn't seem to be working. Could you try again?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I'm not sure what you mean: when you click on gggg (cell B2) it doesn't do anthing? On my computer RRR links to and shows SSS.xlsx which is totally void of code or anything. Maybe when one downloads one has to redo the hyperlink path? Seems strange....don't know. :o2
    I attached the two files again; RRR includes the second version of Worksheet_SelectionChange. If you put an interruption point next to it you'll see it is not executed (at least it does so on my computer).
    Thank you Mumps!
    Guido.

  • My apologies. I wasn't clear. I meant that the link to the SSS.xlsx file you posted in Post #5 wasn't working properly.
    Try:

    Code
    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
        Workbooks("RRR.xlsm").Sheets("RRR").CommandButton1.BackColor = 500
    End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!