auto hyperlink actions

I've had a bit of a play with this, using OnTime - based on the principles on Chip Pearson's excellent site: http://www.cpearson.com/excel/OnTime.aspx - and I think I've got a solution for you. It doesn't use a hyperlink; instead it uses the lack of any new selection on the working sheet to switch to a TimeOut sheet after a set time.

Before I get to the code, a few notes:

a. I've called the blank sheet 'TimeOut' - either change the name of your blank sheet to this, or change the reference in the code below (in the 'GoToTimeOutSheet' procedure);
b. The code works by running a repeating check of the time now against a time-out time set by either the data sheet being activated or the selection being changed on the data sheet;
c. Change the time-out time to what you want in the 'SetTimeOutTime' procedure - it's set to 50 secs in the code below;
d. The code needs to be put into four different modules: a standard module, the worksheet module for your data sheet, the worksheet module for the TimeOut sheet and the workbook module;
e. If you have more than one data sheet, repeat step 2 below for each one;
f. There's no error-trapping included - I don't think there should be any problems, but as always I advise trying this out on a copy of your file first;
g. As this uses VBA, the file will need to be saved as a macro-enabled file (filename.xlsm not filename.xlsx).

1. Create a new module (I named it 'TimeOut' but that's optional), and paste in all of this:

Please Login or Register  to view this content.

2. Paste this code into the worksheet module for your Data sheet(s):

Please Login or Register  to view this content.

3. Paste this code into the worksheet module for the TimeOut sheet:

Please Login or Register  to view this content.

4. Paste this code into the workbook module:

Please Login or Register  to view this content.

I've attached a file which has this working. Hope that does what you need.

Yes, you're correct - the data sheet will flip to the time-out sheet after about 50 secs. The reason it's about 50 secs is that Excel only checks every 10 secs to see if the time-out has been reached (that 10 secs is set near the top of the code, in this bit: Public Const TimerRerunSecs = 10
So the actual time for time-out could be anything between 50 and 60 secs, depending on whether the check has just happened or not. That's why I chose 50 secs - so the actual time-out would not be more than one minute.
If you want to change this time, just change the "hh:mm:ss" numbers in this line:

Please Login or Register  to view this content.

Don't worry about the 'dark arts' - I'll explain how to do it (I just didn't want to waste your time explaining how to implement the code if you were already familiar with VBA).

If you haven't used macros in your Excel installation before you should first check that they can run.
Click File → Options → Trust Centre → Trust Centre Settings → Macro Settings then check that the second option down (Disable all macros with notification) is selected (it's the default) (you could use the fourth (Enable all) but that's not recommended - this way you get to choose when/if to allow macros to run).
(The instructions above are for Excel 2010, but they should be good for 2013/2016 too.)

Now you can copy/paste the VBA code (VBA = Visual Basic for Applications - it's the MS Office programming language).


Test this with a copy of your file first, just in case!


A. First section of code
1. Copy the first section of code in the post above (you could do this later, but you might as well do it now).
2. With your workbook open, press Alt+F11 (together) to open the Visual Basic Editor (VBE).
3. Click Insert then Module.

Optional rename
3a. On the left-hand side at the bottom, you'll see an area titled Properties - Module1.
3b. A bit below that you'll see (Name) with Module1 beside it.
3c. Replace Module1 with TimeOut.

4. Click into the blank space on the right-hand side and Paste in the code you copied at step 1.


B. Second section of code
1. Come back to this page and copy the second section of code (for your Data sheet).
2. Go back to your workbook.
3. Right-click the tab for your Data sheet and choose View Code. This will take you to the VBE and you should see on the left-hand side near the top that your Data sheet is highlighted in the Explorer-type window - it'll be labelled something like Sheet1(Data).
4. Click into the blank space on the right-hand side and Paste in the code you copied.


C. Third section of code
1. Come back to this page and copy the third section of code (for the blank TimeOut sheet).
2. Go back to your workbook.
3. Right-click the tab for your blank sheet and choose Rename. Call it TimeOut.
4. Right-click the same tab again then choose View Code to take you back to the VBE where you should see the TimeOut sheet highlighted.
5. Click into the blank space on the right-hand side and Paste in the code you copied.


D. Fourth section of code
1. Come back to this page and copy the last section of code (for the workbook).
2. Go back to your workbook and press Alt+F11 (together) to go to the VBE (or just go directly to it instead of to the workbook first).
3. On the left-hand side near the top, you'll see ThisWorkbook under the sheet labels.
4. Right-click ThisWorkbook and choose View Code.
5. Click into the blank space on the right-hand side and Paste in the code you copied.

E. An addition to the above
A bit more testing has shown me one way that this won't work, which is if you save the file while you are on the Data sheet then close it before it times out. When you then re-open it, the Data sheet will be open so isn't actually 'activated' so the Time-out procedure doesn't run. In other words, you could open the file, do nothing and the time-out wouldn't happen.
You can avoid this by adding an extra section to the Workbook code.
Repeat section D, but this time when you do step 4 you'll see the code you already pasted in on the right-hand side. Copy the code below and paste this in on the right-hand side as well - either above the previous code or below it (just not in the middle of it !).

Please Login or Register  to view this content.

Save
Close the VBE, either with the standard close/exit button at the top-right or by pressing Alt+Q.
Save the file as a macro-enabled workbook (.xlsm extension).


Test
1. Close Excel completely.
2. Open Excel and the file you just worked on.
3. Go to the Data sheet and click somewhere. Don't do anything else. The TimeOut sheet should appear after 50 secs.
4. Go back to the Data sheet but don't click anywhere. The TimeOut sheet should appear again after 50 secs.






Sorry that's so long, but I hope that it all makes sense . If it doesn't , please don't hesitate to ask for more clarification.

When you get the error is there a 'debug' option? If so, click it and it should take you into the VBE then highlight the problem line in yellow.

Also, please check that you've put the code in the correct sections - and for the correct file, if you perhaps had another file open at the same time, or you already have some macros in your personal macro workbook. According to a thread from a few years ago (here), having code in the personal macro workbook caused this error.

Have you renamed your blank sheet to 'TimeOut'?
If not, either do this, or change the references in the code to use your sheet name instead:

Worksheets("yoursheetnamehere").Activate

It's in two places - the first piece of code in my first post and in the extra piece of code at the bottom of the second post.

Glad to have been of help. I'd never considered something like this before, so it was a nice challenge!

If any glitches do crop up, I'm usually on here every day or two, laptop problems allowing, so I can take a look - or someone else with better VBA knowledge might jump in - the forum's like that sometimes .