1

I am struggling with getting another tab within my document to activate within a function I am running - it works fine when I debug, but as soon as I run it, the rest of the code runs, but it just doesn't switch tabs.

Here is a snippet of some of the code

Function HyperlinkClick()

'Run some other Code...

Worksheets("Delivery Note").Activate

End Function

I am running it as a Function in a separate module as I need to have it linking from a hyperlink that is only created on each row as it is filled out ie:

=IF(C2>0,HYPERLINK("#HyperlinkClick()","Reprint"),"")

Has anyone got any ideas on why this is or another way of achieving this?

Thanks in anticipation!

Kieran Barter
  • 13
  • 3
  • 3
    just don't use activate. It is one of the most rediculous commands in VBA as it is very volatile. Better is to explicitly name the worksheet your following code is having an effect on. Also see: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Luuklag Apr 03 '18 at 12:05
  • @Luuklag: great shout. Other than for visual reference, I have no idea why **`.Activate`** is even available in VBA. It serves no purpose other than not always working and slowing down VBA code – Zac Apr 03 '18 at 12:08
  • Thanks for your comments - however I should have clarified, I'm not using `.Activate` for any of the actual running code etc, but I just want to make that tab visible to the user after it has run. – Kieran Barter Apr 03 '18 at 12:20
  • @Luuklag Also with ref to your comment about the # - I was just going by this: https://stackoverflow.com/questions/33114093/excel-hyperlink-to-nothing?rq=1 and it seems to work – Kieran Barter Apr 03 '18 at 12:26
  • @KieranBarter, my mistake then. – Luuklag Apr 03 '18 at 12:28
  • How about using the index number of the worksheet in question, perhaps there is something wrong with the spelling of your worksheet name, or leading or trailing spaces? – Luuklag Apr 03 '18 at 12:34
  • @Luuklag good suggestion... but have just tried it - and it's no different! :-S Its a bit strange that its working as it should and switching tabs when I debug, but not when I run the code properly. – Kieran Barter Apr 03 '18 at 12:40
  • 1
    You can't make a worksheet formula activate a spreadsheet. https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel – SJR Apr 03 '18 at 12:47
  • @SJR : ok - yes I was starting to wonder if it might be a limitation like that - is there any other way to achieve this? calling another function? – Kieran Barter Apr 03 '18 at 13:02
  • Why not just Sheets("Delivery Note").Cells(1,1).Select ? – Cyril Apr 03 '18 at 13:03
  • You can use via VBA or using a sub, but it's not clear what you want to achieve. It's normally necessary to activate thing as has already been mentioned. – SJR Apr 03 '18 at 13:07
  • Typo - "not necessary ..." (obvs). – SJR Apr 03 '18 at 13:22
  • @SJR as clarified in my comment - all I need to do is make another tab visible to the user after a function has run. It needs to be a function rather than a sub because of how it's being called from a hyperlink formula. – Kieran Barter Apr 03 '18 at 13:44
  • KIeran, put the information about making the sheet visible to the user (and anything else that's relevant) in your original question. Then it will be clear to all who start reading this fresh and haven't seen (nor want to wade through) the comments. You can use the [edit] link. – Cindy Meister Apr 03 '18 at 15:04
  • You can hyperlink to a cell on a different sheet, no need for VBA. – SJR Apr 03 '18 at 16:38

1 Answers1

1

Some restrictions on what you can do in a function evoked from a hyperlink apply (though not exactly as they do for UDF's), but if you want the link to take you to a different location, then return that location from your function: 

Function HyperlinkClick()

    'other code here (with restrictions)

    Set HyperlinkClick = Worksheets("Delivery Note").Range("A1")

End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125