Sorting Table using Hyperlink

Question

Hi MVP and community members,

Is it possible to click on a hyperlink and make the hyperlink automatically sort my table to display only the data I click?

I don't even know what to Google for since I don't know how to describe the problem.

For example:

I want to create a hyperlink of AW18201, so that when I click on it, the table will only show AW18201 data only.

Regards,

Firdaus Yassy

Answer 1

Hi Firbaus,

replace (2 lines)

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

Operator:=xlBetween, Formula1:=Join(v, ",")

with (one line)

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(v, ",")

all vba 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'' ### 14 Jan. 2019 oAnast ###

Dim ws As Worksheet

Set ws = ActiveSheet

Dim rCel As Range

Set rCel = ws.Range("G2") '<< create drop down list in cell G2

Dim c As New Collection, cItem

Dim L As Long, i As Long

Dim v As Variant

L = ws.Cells(Rows.Count, "B").End(xlUp).Row

On Error Resume Next

For Each cItem In ws.Range("B3:B" & L) 'id numbers in col B from row 3

c.Add cItem, cItem

Next

ReDim v(1 To c.Count + 1)

For i = 1 To c.Count

v(i) = c(i)

Next

v(c.Count + 1) = "ALL"

If Not Intersect(Target, rCel) Is Nothing Then

rCel.Interior.Color = vbYellow

With rCel.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(v, ",")

End With

rCel.Offset(, 1).Select

End If

End Sub

Answer 2

Hi oAnast,

So I form the data set exactly as shown in the video.

Then, I paste the vba macro code, and when i double click cell G2, this shows up. 

Answer 3

I paste your code but the validation list does not come out (not exist). Did I do something wrong?

Hi Firdaus,

(really sorry, it's my fault)

right click on Sheet1 tab, select View code and paste in the following (two vba macros)  (delete old macro)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

'' ### 04 Jan. 2019 oAnast ###

Dim ws As Worksheet

Set ws = ActiveSheet

Dim rCel As Range

Set rCel = ws.Range**("G2") '<< create drop down list in cell G2**

Dim c As New Collection, cItem

Dim L As Long, i As Long

Dim v As Variant

L = ws.Cells(Rows.Count, "B").End(xlUp).Row

On Error Resume Next

For Each cItem In ws.Range("B3:B" & L) 'id numbers in col B from row 3

c.Add cItem, cItem

Next

ReDim v(1 To c.Count + 1)

For i = 1 To c.Count

v(i) = c(i)

Next

v(c.Count + 1) = "ALL"

If Not Intersect(Target, rCel) Is Nothing Then

rCel.Interior.Color = vbYellow

With rCel.Validation

.Delete

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

Operator:=xlBetween, Formula1:=Join(v, ",")

End With

rCel.Offset(, 1).Select ' << new line

End If

End Sub

'

Private Sub Worksheet_Change(ByVal Target As Range)

' ### 04 Jan. 2019 oAnast ###

Dim ws As Worksheet

Set ws = ActiveSheet

Dim rCel As Range

Set rCel = ws.Range**("G2") '<< drop down list**

Dim L As Long

L = ws.Cells(Rows.Count, "B").End(xlUp).Row

If Not Intersect(Target, rCel) Is Nothing Then

If rCel.Value = "ALL" Or rCel = "" Then

ws.AutoFilterMode = False

Exit Sub

End If

ws.Range("B2:B" & L).AutoFilter Field:=1, Criteria1:=rCel.Value

End If

End Sub

now,

1. double click in cell G2(create a validation list)
2. select an item from the list (cell G2)

HERE...VIDEO

Good Luck

XXXXXXXXXXXXXXXXXXXX

NOTE

Each time you add new rows or delete/edit data  in table (column B), double click in cell G2

in order to  update validation list

Answer 4

Hi,

Actually I do try doing as per your instruction before asking the last question (which I thought would be easier to achieve than validation list), but it does not happen the way shown in the video.

I paste your code but the validation list does not come out (not exist). Did I do something wrong?

My bad if I got on your nerve.