HYPERLINK() function Seems Unable to Jump to Another Sheets Table and Filter

[sbelgrave]

Hi there,

I have 2 sheets in my workbook, called 'Master' and 'Transactions', each has 1 table in it called 'MasterTbl' and 'TransactionsTbl'

In the Master Sheet, I've created a column that uses the HYPERLINK() function to generate a hyperlink in each row like so :

=IFERROR(HYPERLINK("#createTransaction()","Add Transaction"),"Add Transaction")

The resultant hyperlink in each cell in that column says 'Add Transaction', and when clicked, it calls the 'createTransaction' function located in my Module 1 code.

The goal of this code is to simply do 3 things :

1. Add a new row to the end of the ''TransactionsTbl' table
2. Then filter the table in the 'Transactions' sheet based on some of the row data from the row we just clicked on in the 'MasterTbl' table. So for instance if we clicked on a row in the 'MasterTbl' which had an ID=16, then the goal would be to filter the table in the 'TransactionsTbl' table to only show rows that also had an ID of 16
3. And finally, the last goal would be to then jump to that sheet so the user could see the nicely filtered result.

What is frustrating is that it appears as though this might not be possible using the HYPERLNK() functionality (as the same commands i run in the 'Immediate' window or outside that function, seem to work. It's almost as if the HYPERLINK() functionality suppresses or ignores some of the things I want do. I'm hoping someone here can prove me wrong or offer another way in which I can achieve the same results. Currently right now, at the end of the 'createTransaction' function i run the following commands

Set createTransaction = Worksheets("Transactions").Range(tor.Range.Address)

// This successfully selects the focus to the newly added row in on the 'TransactionsTbl'


However, if i run any commands to try and filter the 'TransactionsTbl' table (using our example above , lets say , on ID=16), the UI does not update but remains showing all the records in the 'TransactionsTbl' on the 'Transactions' Sheet.

Sheets("Transactions").ListObjects(1).Range.AutoFilter Field:=1, Criteria1:=16

Can anyone possible shed some light on how I can retain the benefits of the HYPERLINK() functionality, but also be able to jump to sheet and have the UI update accordingly when i make changes to it ?

Thank you ahead of time.

 

[sbelgrave]

In case this helps. here is a visual for how it all comes together with the code and the UI.

Code.png

drive.google.com

 

[Skyybot]

Range.Select sets focus. Just add it to your CreateTransaction code (changing Range to suit your needs)

 

[sbelgrave]

Hi, yes, the part that already works right now is the focus. When i do the following code :

    'Creates a Filter to only show 'Line Numbers' that = 16
    Sheets("Transactions").ListObjects(1).Range.AutoFilter Field:=1, Criteria1:=16
    
    
    ' Jump to Newly Added Row in Transactions Worksheet
    Set createTransaction = Worksheets("Transactions").Range(tor.Range.Address)

The focus jumps from the 'MasterTable' sheet to the 'Transactions' sheet. The part that doesn't work however, is the FILTER. (1st part above). If i run that exact same code in the 'Immediate' window, it works. So this is what is making believe that running this code inside a function (e.g. 'createTransaction') called within a HYPERLINK() is not allowed or ignored. If i run that same code in a Button click, it also works. So the challenge I'm asking for is to see if there's a way to be able to still use HYPERLINK() and get this behavior ? Any ideas ?

 

[Skyybot]

Copy that. Before I start to dig in here, will you always want to Filter with the number 16?

 

[sbelgrave]

Copy that. Before I start to dig in here, will you always want to Filter with the number 16?

No, was just using it as an example. in general, intend to grab the ID from the row of the 'MasterTbl' and then filter the 'TransactionsTbl' based on that ID.

 

[Skyybot]

I've taken a closer look at the picture you posted. I believe I see what you are trying to do. I would probably take advantage of the Worksheet_BeforeDoubleClick event in MasterTbl column B to do my transaction adding and filtering instead of this hyperlink thing. I would create a hyperlink from that cell to the newly added transaction after the fact.