0

Users will enter new information into a sheet using an userform. This information is tied to a unique ID given at time of entry (i.e. 2019-7 for the 7th item in this year). Currently, each piece of information is placed into separate cells in a row. I want to hide some of these cells but be able to allow users to click on the unique ID at the start of that row, which will run a macro that will create a new sheet to display all information in a user friendly way.

I have tried creating a hyperlink to a run a macro but the code for identifying the hyperlink can't be cell specific. I need a more dynamic way to have excel recognize which ID was clicked and then use that to gather the rest of the information in that row (i.e. the hidden columns).

My best option had been to put a private sub in the worksheet to recognize when a cell was changed/clicked but couldn't get past it just identifying the cell. I need it to then identify the contents of that cell, which would be the unique ID.

I have no issues with creating a hyperlink to nothing, I just need excel to run a macro when a hyperlink is clicked and then within that macro, identify the unique ID and/or row that was clicked. Once I have the ID or Row identified, I then can go from there to grab the rest of the info in the other columns.

DFQuality
  • 9
  • 1

1 Answers1

0

You can use the BeforeDoubleClick event of the Worksheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address <> "$A$7" Then Exit Sub
' below, call the functions/subs that produce your output worksheet
MsgBox "You clicked on " & Target.Address
End Sub

Above only works on cell A7. If you need this to apply to entire column A, modify slightly:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
' below, call the functions/subs that produce your output worksheet
MsgBox "You clicked on " & Target.Address
End Sub
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • My issue isn't with getting excel to recognize a particular cell, which I have been able to do with Worksheet_Change. But I need excel to then grab the contents of that cell which it has now identified. – DFQuality Jun 19 '19 at 16:58
  • In the code above, the contents of that cell would be given by `Target.Value`, `Target.Value2` or `Target.Text`, depending on what you need. – David Zemens Jun 19 '19 at 17:22
  • That did it! It's always something so simple. Thank you! – DFQuality Jun 19 '19 at 17:47
  • Cheers. If this answers the question, do consider marking it as "Accepted". – David Zemens Jun 19 '19 at 17:59