Disable Hyperlink in and Excel shape

[OwenMcH]

Hi Folks,

I've created a dashboard for my workbook and have just one shape button which has a hyperlink on it. I would, however, like to have the button's hyperlink become disabled if the value of a specific cell is a set value.

I have attached an example, and simply put, I would like for the button to be 'not-clickable' if the user selects 'Option B' from the drop down list. When the user selects 'Option A' again, the hyperlink should become active again.

If possible, I'd like the button's format to change in some way as well; perhaps fade, or change colour.

Any help would be appreciated.

Thanks in advance.

[lokicl]

Hi,

Update the below code in the respective sheet event

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("c3").Value = "Option B" Then
    Sheet1.Shapes("Rounded Rectangle 1").Hyperlink.Delete
Else
    Sheet1.Hyperlinks.Add Anchor:=Shapes("Rounded Rectangle 1"), Address:="", SubAddress:="Sheet2!A1"
End If
End Sub

Regards,
Lokesh

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.