Moving Cursor to Current Date (Column) but same row that I was in before clicking hyperlink or vba button

BrerRabbit

Board Regular
Joined
Aug 20, 2023
Messages
87
Office Version
  1. 2021
  2. 2016
  3. 2013
Platform
  1. Windows
I would like to try some VBA code that remembers where the cursor was (row number) but go to the required column, and I have no idea where to even start.

I currently have a hyperlink that allows me to go to the today's date column, but always goes to the top of the page, instead of the row the cursor was at before clicking the hyperlink to go to today's date:

Excel Formula:
=HYPERLINK("#"&SUBSTITUTE(ADDRESS(1,MATCH(B1,11:11,0),4),1,"")&16,"Find Today")

(Current date is in B1 in my project).

If my mouse was in M4 ie 8 Jan Blue and I click the Hyperlink, it will take me to B1 28 Dec. I need to go to B4 ie 28 Dec Blue.

Is there a way to do this? This is obviously a small example of a large project I am currently working on.


ABCDEFGHIJKLMNOPQ
128/12/202428-Dec-2429-Dec-2430-Dec-2431-Dec-241-Jan-252-Jan-253-Jan-254-Jan-255-Jan-256-Jan-257-Jan-258-Jan-259-Jan-2510-Jan-2511-Jan-2512-Jan-25
2Red
3White
4Blue
5Pink
6Green
7Brown
8Black
9Yellow

Thank you in advance.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Rather than a hyperlink, would a before double click event be acceptable? If so, put the following code in the worksheet module of the sheet in question (right click the sheet tab name, select View Code and place in the code window that appears right of screen - save the file as either macro-enabled or binary). That way, if you're in M4 & double-click the mouse, the cursor will go to B4.

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, cancel As Boolean)
    On Error GoTo Escape
    Application.EnableEvents = False
    cancel = True
    
    Application.Goto Cells(Target.Row, Range("1:1").Find(Date).Column)
    
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Today's date not found in row 1"
    Resume Continue
End Sub
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

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