Use VBA to call hyperlink where Cell data is name of sheet?

**shannonvhm** · 11-25-2009, 12:01 PM

Good afternoon everyone! I would like to use VBA to set up the following:

I have sheets that are named by date. January 1, 2010 --> 12/31/10. They are in the format like this: 01-Jan-2010 What I would like to do is have code that when i click on a cell on my main page that has the date, it automatically goes to the appropriate sheet. So, if you click in the cell that says 1/15/10, you go to sheet 15-Jan-2010.

I guess the short version of my question is this - can you set up links where the sheet name is the data in the cell?

Thanks!!!

**DonkeyOte** · 11-25-2009, 12:06 PM

You have a few options... you could for ex. use the HYPERLINK function directly in the cell that "contains" the date, so rather than entering the date you could enter:

=HYPERLINK("#'15-Jan-2010'!A1",DATE(2010,1,15))

You could also possibly use VBA and a double click or selectionchange event for ex. but in those instances realistically we'd need to know more info, ranges containing dates etc... perhaps best to post a sample file ?

**shannonvhm** · 11-25-2009, 12:27 PM

Ok - here is the file. Here's the deal, when you look at one of the months, for example, january, i want to be able to click on the, for example, 15th, and have it take me to the sheet 15-Jan-2010. Thank you!

**shannonvhm** · 11-25-2009, 12:28 PM

oops. here is file.

**shannonvhm** · 11-25-2009, 12:38 PM

AHH! I have my answer. a wonderful gentleman on another board gave me this code:

Please Login or Register  to view this content.

and i put it on each sheet & it is PERFECT!

Thank you guys!

**DonkeyOte** · 11-25-2009, 12:45 PM

Thanks for posting your resolution but going forward please be sure to use CODE tags - I have on this occasion added them for you.

FWIW I would have suggested / opted for something like:

Please Login or Register  to view this content.

which would be placed within ThisWorkbook Object in VBE and in turn this would then be applied to all monthly sheets simultaneously (ie no need for separate code for separate sheets)

The above is also a DoubleClick event rather than a simple cell select event. Personally I dislike being navigated elsewhere simply because I navigated onto a given cell, I much prefer a deliberate action like a DoubleClick so as to prevent unexpected interactivity on part of end user (but that's just me).