VBA to simulate clicking on a cell with LET function which has a HYPERLINK function within

[Ludwig]

I have a specific cell that I can click on manually that has a HYPERLINK with calculated value based on the data within the worksheet. To work out the correct cell to hyperlink to, I use the LET function to remove duplicated function calls within the cell formula. This works fine.

What I need now is to simulate within an existing VBA macro is clicking on that cell to achieve the same effect. Selecting the cell and then issuing

ActiveWorkbook.FollowHyperlink ActiveCell.Hyperlinks(1).Address

fails as I expected because the first function encountered in the cell is LET (within which the last parameter is "hyperlink(...)"), nor is there a link defined on the cell - because the cell to be jumped to needs to be calculated first based on the data in the worksheet.

I also looked into SENDKEY as an option but cannot find how to 'sendkey' a click of the mouse.

    Range("P4").Select                  ' select this cell which will allow me to go to first unfilled data cell
    Application.SendKeys "~", True      ' except ~ is an Enter not a mouse click on the cell

What is the VBA code I need to simulate the mouse press on the cell (P4 in this case)?

Thanks for the help.

 

[FormR]

Hi, it's nothing to do with the LET() function ,the hyperlink() function is not an actual hyperlink that can be accessed by the hyperlinks property in VBA.

You would need to calculate the address you want to follow using the same method that you use in the formula.

 

[Ludwig]

So can SENDKEY be used to generate my mouse click on the cell?

 

[FormR]

No, not as far as I'm aware, is there a reason why you couldn't derive the address using the same logic you have used in HYPERLINK() function?

It might help generate some other ideas if you posted the LET(....,HYPERLINK()) function you are using.

 

[Ludwig]

Here's the LET(..HYPERLINK(... ) ) formula I use in cell P4:

=LET( FirstMissingDataRowLCL, MIN( INDEX( FILTER( tbkWhRdgs[Row Nbr], tbkWhRdgs[Date of reading] = "", MAX( tbkWhRdgs[Row Nbr] ) + 1 ), 1 ),
               INDEX( FILTER( tbkWhRdgs[Row Nbr], tbkWhRdgs[kWh reading] = "", MAX( tbkWhRdgs[Row Nbr] ) + 1 ), 1 ),
               INDEX( FILTER( tbkWhRdgs[Row Nbr], tbkWhRdgs[Odometer reading] = "", MAX( tbkWhRdgs[Row Nbr] ) + 1 ), 1 ) ),
HYPERLINK( "#" & FirstMissingColm & FirstMissingDataRowLCL,
                                 "click here to go to first incomplete" & CHAR( 10 ) &
                                 IF( INDIRECT( ADDRESS( FirstMissingDataRowLCL, COLUMN( tbkWhRdgs[[#Headers],[Is Row Hidden]] ), 1, 1 ) ) = "H", "the HIDDEN ", "" ) &
                                 "data cell ...  i.e. " & FirstMissingColm & FirstMissingDataRowLCL )
)

Note - FirstMissingColm is a named cell with another formula in it. LET messes things up if I code it here [it forgets the "hyperlink" function and just provides the cell value as "#" & FirstMissingColm & FirstMissingDataRowLCL ignoring HYPERLINK's 2nd parameter rather than doing the expected HYPERLINK thing - I'm waiting for MS to fix that bug].

Back to *this* question: I have a circumvention for my question in place right now - in VBA I take the cell reference to go to from the text I've now included at the end of the resultant cell value. I would rather not have to do this but have VBA "mouse click" on P4 for me and jump as the cell HYPERLINK resolves to. Coding the equivalent formula into VBA means a second place to update in the future and I don't want to have to remember to do that if/when I make changes to the formula!

 

[FormR]

Here's the LET(..HYPERLINK(... ) ) formula I use in cell P4:

That's quite some formula!

in VBA I take the cell value to go to from the text I've now included at the end of the resultant cell value.

Sounds like a reasonable compromise

 

[Ludwig]

Effectively what I'm doing is finding the first of 3 columns where data is missing. So first LET parameter finds the row across 3 columns, the second one determines the column for that row, then LET is supposed to allow me to run the HYPERLINK command to that cell.

Easy said like that, formula looks more complicated but achieves what I need