2

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!

Improve this question
0

1 Answer 1

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you very much for your reply! This thought had crossed my mind, although I am worried that it will create a bunch of overhead for a lot of the automated worksheet changes that take place inside the sheet, potentially causing either an overflow or a significant slowdown of the sheet. What are your thoughts?
Umm, I would like to understand how will the code I suggested above will create a "bunch of overhead for a lot of the automated worksheet changes"?
I was under the impression that every time the worksheet changes, this macro will fire. But in fact, you are using Worksheet_SelectionChange which is user controlled. The rest of the VBA code manipulates objects only. So this fires only when the user is clicking around on the sheet - and there is a further control to make sure that their selection is retained inside the ListObject. It's quite elegant and will not cause 1000 operations if the automated code makes 1000 sheet manipulations.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.