Click cell Col i Sheet 1 to create hyperlink to Col J Sheet 2 where dates in sheets match

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
Hi

I'm looking for some code that will run only when I double click a cell in sheet 'Training Log' column i cells 7322 to 23357 that begins with the text "Indoor bike session" (without the commas).

When I double click the cell, the code searches Col A rows 11 onwards of sheet 'Indoor Bike' for the same date and when found, creates a link to Col J in that row, in Col i of sheet Training Log and retains the same text "Indoor bike session..." as before, with a tooltip "Go to Indoor Bike entry for (+ the relevant date)".

The code should only run once for each of the relevant cells i.e. it should only function as a link once the link has been created.

I hope the below extracts showing Indoor Bike sessions in blue with the same dates in both sheets make this reasonably clear.

A solution would be greatly appreciated.

Many thanks!


Sheet 'Training Log' extract
Copy of Exercise Log for MrExcel posts.xlsx
ABCDEFGHI
8666Sat, 11 Sep 2021OTHER11169%JIndoor bike session, 60 mins.
8667Sun, 12 Sep 2021REST0%Well earned.
8668Mon, 13 Sep 2021OTHER11672%JIndoor bike session, 60 mins.
8669Tue, 14 Sep 2021Hallas Br/Down Bents Ln Harden Lane/Smithy Ln/ Lee Farm/Black Hills/ Golf Course/R down Beck Foot Lane/Wagon Lane/ Down LLC to Shipley (Over Dock Ln to post) Back, past 5-Rise Locks to 2nd road junction (Duck House)/Back down to 3-Rise & over Br/ Brown Cow/Main Rd all the way back home17.14:13:1914:4911672%JDay 5537. 1:15pm, 13ºC, light rain most of the way round, quite nice. Phone app said heavy rain (took heed as it was eventually right about the deluge the other week) but thankfully it got it wrong as it was only ever light drizzle. Wore old yellow Goretex jacket + t-shirt, loads of vaseline on right nipple (and fingers), no problem (even though t-shirt was drenched when I took it off after). Porage oats and milk + 2pts squash 30 mins+ B4 leaving. No water taken. Pee stop at wall of field B4 approach to Blackhills. Heart felt bit strained for first 45 mins and bit concerned about ave HR of 114bpm on watch (it reduced once I was on the canal). It was probably cos of broken sleep - in bed 2am then getting up for a wee at least 3 times in the night. Head VERY tired along canal towards Shipley and back towards Dowley Gap, could have just gone to sleep although heart/legs were OK, but familiarity then made me feel better beyond there for rest of the run. Backache didn't set in until Bingley this time but bit worried about slight spasms in L calf muscle, although thankfully it never got any worse. L hamstring became tight but as ever but it didn't affect my gait. Had it in mind from the start I wanted to exceed 4hrs and by 5-Rise Locks I felt confident enough to keep going to the Duck House (2nd road junction). When I got back to the 5-Rise I calculated I was on for about 4hrs 10mins. Although I started to get v thirsty and sweaty again from there on (devoured 3 oranges and 2 pts squash when I got in), finishing was never in doubt. AND I DID! THIS IS THE LONGEST DURATION RUN I'VE EVER DONE, AND I'VE FINALLY BEATEN MY PREVIOUS RECORD OF 3:57:40 HELD FOR 13 YEARS! This is now Iron Man run No.21 this year and my first DOUBLE! It's also the furthest I've run this year and the furthest distance ever run on this day of the year!
8670Wed, 15 Sep 2021OTHER11471%JIndoor bike session, 60 mins. Think I must have strained my intercostal (chest) muscle in the night (no discomfort during run yesterday but bad when I got up this morning).
8671Thu, 16 Sep 2021REST0%Very well deserved. Intercostal muscle really tight, sharp pain leaves me momentarily breathless. Worried how this will affect the next run.
8672Fri, 17 Sep 2021OTHER11370%JIndoor bike session, 60 mins.
Training Log
Cell Formulas
RangeFormula
E8669E8669=D8669/C8669
G8666:G8672G8666=F8666/(220-(DATEDIF($G$7,A8666,"Y")))
Named Ranges
NameRefers ToCells
LastLogDate=OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0)G8672
LastRunDate=OFFSET('Training Log'!$A$11,'Training Log'!$B$10,0)G8672
Log_LastDate=INDEX('Training Log'!All_Log_Dates,ROWS('Training Log'!All_Log_Dates))G8672


Sheet 'Indoor Bike' extract
Copy of Exercise Log for MrExcel posts.xlsx
ABCDEFGHIJ
349Sat, 11 Sep 20211:00:0020.012.4811169%148JSession 339. 6:10pm. Black Sabbath Live At Last (perfect speed for getting used to new level). Legs bit tired for first 5-10 mins then started to get used to it. I know the overall output is less than the last session (2½hr run yesterday) but my legs felt they were getting used to it much earlier in the session and it didn't feel as much of a grind. Maybe the familiar music made it easier?
350Mon, 13 Sep 20211:00:0021.013.0811672%155JSession 340. 5:45pm. Hawkwind Space Ritual (great music, Gong at 45rpm with a good beat!). Was asleep nearly all afternoon after 5hrs sleep last night and all lost sleep being up on MrExcel.com until 4-5am night after night - looks like it paid off as after a couple of mins quads were v light and strong and stayed that way the whole way round, even managing 180W for the last 10 secs. V good session.
351Wed, 15 Sep 20211:00:0020.812.9811471%154JSession 341. 7:00pm. Zep Cleveland 28.04.1977 (in celebration of first ever >4hr run yesterday). Great session, even better coming so soon after yesterday's run. Quads got v strong after first 5 mins warming up and legs became nice and light as if I hadn't even run yesterday. Inner core muscles had been bit cramped during day but session lessened it. R hand was cramping like hell though for last 20 mins. Starting to own this level already!
352Fri, 17 Sep 20211:00:0020.612.8811370%152JSession 342. 5:15pm. Beck, Bogart & Appice 1972 (good guitar & drums, rubbish vocalist) + first 15 mins of Mahogany Rush Live. Legs cranky for at least first 15 mins, then found it quite hard work until around 45 mins when they were finally nice and light. Pleasantly surprised wattage was so high.
Indoor Bike
Cell Formulas
RangeFormula
D349:D352D349=IF(B349>0,C349*0.621,"")
G349:G352G349=F349/(220-(DATEDIF($F$1,A349,"Y")))
 
Last edited:
Brilliant, that worked!

Just a couple of minor things - would it be possible to amend the code as follows please?

1) The text format to remain as it was - Comic Sans MS Bold 8 no underline
2) The tooltip text has been added to the existing text in the cells - can this be corrected?
3) Change the msgbox so I'm able to exit the macro if I need to (I was well and truly locked in - clicking the 'x' only advanced to the next link)

One more question - I presume I can run this again for future entries without affecting the existing links?

Many thanks!
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Just a couple of minor things - would it be possible to amend the code as follows please?

1) The text format to remain as it was - Comic Sans MS Bold 8 no underline
- what will let you know there is a hyperlink there then ?
- if you want all hyperlinks to be changed to the same thing, you can simply modify the hyperlink “style”

2) The tooltip text has been added to the existing text in the cells - can this be corrected?
- what do you want it to show ?

3) Change the msgbox so I'm able to exit the macro if I need to (I was well and truly locked in - clicking the 'x' only advanced to the next link)
- as Michael said it was just for testing, simply remove it or comment it out.

4) One more question - I presume I can run this again for future entries without affecting the existing links?
- at the moment it will repeat the action (but the net impact is nil) we can look at skipping those with existing links

Nothing will happen until tomorrow though.
 
Upvote 0
Thanks Alex

1) I will just know, it's only me who uses it. Noted, thanks.

2) The tooltip text should be this instead of cell text
VBA Code:
TextToDisplay:=Cells(i, "I").Text & "Go To Indoor Bike Sheet, Column J, Row " & t & " for Details"

3) Ah, OK, understood

4) That would be very useful if that could be done, thanks

Thanks once again!
 
Upvote 0
Hey, in the meantime I've just managed to suss out (2) myself :biggrin:
VBA Code:
               ActiveSheet.Hyperlinks.Add Anchor:=Range("I" & i), Address:="", SubAddress:="'" & "Indoor Bike" & "'!" & Range("J" & t).Address, _
               TextToDisplay:=Cells(i, "I").Text, ScreenTip:="Go To Indoor Bike Sheet, Column J, Row " & t & " for Details"
 
Upvote 0
For 4)..
It won't matter if the code is rerun for new entries if it writes over the old ones nothing will change !!
If it's only taking a second or 2, I wouldn't bother.
If there is a bit of screen flicker, add
Application.screenUpdating = false at the beginning of the code and
Application.screenUpdating =true at the end....that should improve the speed as well.
 
Upvote 0
Many thanks Michael, noted - and thanks a lot for your solution too.
 
Upvote 0
Gents - could I just ask for a small modification to your code please?

If possible, I need to convert just the last entry to a link instead of all entries. My reason is because I had some additional text in some of the older links in a different font colour and I've just noticed they've been overwritten in a single colour, so I'd like to be able to retain 2 different colours if possible.

I'm thinking it's this line that needs amending but I'm not sure how to do it:
VBA Code:
For i = 12 To Lr1
Thanks again!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top