Position Cursor on Cell containing Today's Date

Question

Existing Spreadsheet with Column A containing a years worth of dates (not contiguous - meaning there are blank rows with other than a date in them). On opening the Spreadsheet and without doing anything, I would like the Cursor to be positioned on the cell in column A that contains today's date. This then allows me to move to a specific column on that row to do data entry for today.

I can conditionally highlight the cell I wish to enter the data into, but there appears no way to position the cursor on that cell without manual intervention.

As above - I can highlight the cell I want the cursor to appear in, but how do I get calc to do that positioning for me automatically.

Answer 1

Yes, tasks with cursor positioning (selecting cell) are solved using a macro. The procedure code will not be very complicated. Your task consists of three steps:

1. Create a macro that will try to find a row with the current date in column A and move the cursor to the desired cell
2. Make sure the code works correctly with your data
3. Teach Calc to run a macro when opening documents.

You didn't say in which sheet of your spreadsheet you want to select a cell. Perhaps you only have one sheet. However, we will tell the macro the name of the sheet on which to perform actions, for example "myData".

Also you didn't specify in which column the cursor should be placed. Suppose this is column D (the number of this column is 3)

The macro code could be, for example, like this:

Sub selectCellByDate(Optional oEvent As Variant)
Const DEFAULT_SHEET_NAME = "myData"
Const COLUMN_TO_SELECT = 3
Dim oSheets As Variant, oSheet As Variant, oRange As Variant, oCursor As Variant
Dim dDate As Long 
Dim oFA As Variant 
Dim rowOfToday As Variant
Rem Get all sheetsa of the current spreadsheet:
    oSheets = ThisComponent.getSheets()
    If Not oSheets.hasByName(DEFAULT_SHEET_NAME) Then
        MsgBox("The current spreadsheet does not contain a sheet named  " & DEFAULT_SHEET_NAME, 16, "Attention!")
        Exit Sub
    EndIf
Rem Get sheet by name:
    oSheet = oSheets.getByName(DEFAULT_SHEET_NAME)
Rem Find last used row in sheet:
    oCursor = oSheet.createCursor()
    oCursor.gotoEndOfUsedArea(True)
Rem Range from A1 to last used cell in column A:
    oRange = oSheet.getCellRangeByPosition(0, 0, 0, oCursor.getRangeAddress().EndRow)
Rem TODAY - we must store result of Date() to variable
Rem See https://help.libreoffice.org/latest/en-US/text/sbasic/shared/03030301.html
    dDate = Date()
Rem To call Calc function from code:
    oFA = createUnoService( "com.sun.star.sheet.FunctionAccess" )
Rem Try to search:
    rowOfToday = oFA.callFunction("MATCH",Array(dDate,oRange,0))
    If IsEmpty(rowOfToday) Then 
        MsgBox("Cell with today's date not found in range " & oRange.AbsoluteName, 16, "Attention!")
        Exit Sub
    EndIf
Rem Cell to enter new data:
    oRange = oSheet.getCellByPosition(COLUMN_TO_SELECT, rowOfToday - 1)
Rem Select cell:
    oCurrentController = ThisComponent.getCurrentController()
    oCurrentController.Select(oRange)
Rem ...and deselect cell:
    oCurrentController.Select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
End Sub

I hope that the remarks in the code will help you understand what and how this subroutine does.

Put the code in your spreadsheet and try run

If this is difficult for you, then please look at this page - in the Structuring section you will find the necessary instructions for embedding Basic code in a document

If the macro does not work correctly, please make sure you change the sheet name in the DEFAULT_SHEET_NAME constant. Also, the macro will only work correctly if column A contains actual dates, not date-like text strings. Check the contents of your column A - press CTRL+F8 and see what color the values are displayed in: if they are black, then this is text, the macro will not work - the values ​​should be blue.

If you encounter such a problem, do not worry - this page contains detailed instructions on how to fix the situation.

Last step. Set macro to event Open Document - this is also detailed in the documentation. For example this page - How to run a macro

Updated after more questions in the comments

1. Yes, you may experience similar problems. I assure you that in two or three months you will no longer regard these difficulties as "problems." Just add the subdirectory with your file to the list of trusted sources (you trust this macro, don't you?)
2. Please change the event triggered by the macro from "Open Document" to "Document loading finished" (yes, I know - I made a mistake, sorry)

Also pay attention to the drop-down list at the bottom - this is where you specify whether you need to run the macro for any opened document or only for one specified exactly.