CODEWITHSUNDEEP
excelvba
Andrew
Andrew

Reputation: 73

Clicking a Hyperlink in Excel Clears a Range Selection Before Running Macro

I am running a macro from a hyperlink that I have placed in a cell on my worksheet. The macro requires the user to select some cells from a table below, which it then transfers to another place on the worksheet. The problem is that clicking a hyperlink will clear the user's selection and instead select the hyperlink's cell.

What I would like to do: Have the user select the cells, click on the hyperlink and be able to reference the Selection range through the macro.

Alternative 1: I had this implemented before but had to remove it: create a shape linked to the macro, so the user does not clear the selection when clicking the shape...

The problem with this is that the worksheet is automatically generated and is very complex. I am unable to maintain the shape's position in the worksheet when the user manipulates rows and tables located above the shape. So the shape either gets deformed or loses its place and goes off into outer space (or appears in the middle of some other table). This occurs even when fixing the shape placement like so:

Set sh = ws.Shapes.AddShape(msoShapeRoundedRectangle, l, t, w, h) 'Non-KtCpl
With sh
  sh.ShapeStyle = msoShapeStylePreset40
  sh.OnAction = "'macroName """ & ws.name & """, ""shapeName""'"
  sh.Placement = 1
End With

The position does not get maintained.

Alternative 2: The user selects the range after clicking the hyperlink using multi-select input, i.e.:

Application.InputBox("Please select the range from the sheet", Title:="Select Range", Type:=8)

Alternative 2 will work but I would really like to know if my original question is even possible.

Thank you!

Upvotes: 2

Views: 386

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

I would really like to know if my original question is even possible.

Logic:

  1. Store previous selection in a range object.
  2. When user clicks hyperlink, check the previous selection with the current selection and see if it is not same and run macro.

Code:

Is this what you are trying? I am assuming that the hyperlink is in A1. Change as applicable.

Option Explicit

Dim rng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Set rng = Target
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If rng Is Nothing Then
        MsgBox "Select relevant cells"
        Exit Sub
    End If
    
    If Not Intersect(Target.Range, Range("A1")) Is Nothing Then
        MsgBox "The range selected is " & rng.Address '<~~ Do what you want here
    End If
End Sub

In Action

enter image description here

Upvotes: 1

Related Questions