Question LibreOffice Calc Macro Skipping, and Event Spamming, Plus Clumsy Code (First Macro)

[KtX2SkD]

Personal Context: Experienced computer and spreadsheet user; programming & databases as a job, degree, & some personal experiments. However, zero initial knowledge in marcos, whether LibreOffice or MS Excel.

Problem Context: Seeking a spreadsheet with data-validation-like behaviour. Doesn’t seem non-macro approach can cut it. Made good progress on a macro, but can’t finalize it.

Problem Details:

Over the last two days I’ve been seeking to make a spreadsheet where there are two date-filled columns. If a change of value were to occur on the first column, then based on the value of the second column the change could be rejected. The rejection could be explicit, as in preserving the pre-change value in its original place, or by other means such as copying the first column to a third column only when a change is not rejected.

I managed to learn and use quite a bit of macro language, and I think I’m very close to achieving the objective, but finding answers online for the last few hurdles is eluding me. It comes down to which sheet event I’d be hooking up my macro to, and its specific mis-behaviour.

My first choice of event was “Content changed”. Last I recall, this event was working fine, until I started using the keyboard hotkey <CTRL + ;> to input the current date, and apparently this way of inputting data to the sheet does not trigger the event, and I don’t think it’s feasible to forgo this input method for my spreadsheet.

The second event to consider was “Formulas calculated”, and it started off fine, until as I was wrapping up the sheet started lagging each time the event was meant to trigger, so I used one “MsgBox” line to debug and it seems I’m getting an infinite amount of “MsgBox” calls now, or perhaps one for each cell in the sheet? Whatever it is, it’s obviously mad wrong and overkill. With “MsgBox” I’d have to kill the macro to exit the infinity, and without it it seems to work but takes a few seconds per change, which is quite unsmooth.

There are a few other issues lying around, be that sad coding in the background (both what I do and don’t know) and behavioural problems I’d like resolved but we can get to it later. I’m only two days deep in learning macros but the problem is searching is just isn’t working for these last few issues. Hope someone can spare the time, check the code, and give some guidance.

Notes:

• If, supposedly, there was an alternative approach to the objective that relies on drastically different code, I hope to pursue both versions if possible, for the sake of learning marcos more.
• This macro version is meant to monitor inputs towards sheet range B3 to B5, and will involve some read & write against range C3 to E5.
• I’m hoping the macro is small & clear enough to not need many comments or much elaboration, but if desired or necessary then I’ll definitely provide any additional notes.

REM  *****  BASIC  *****

Sub Main
    MsgBox ThisComponent.ImplementationName
    Sheet_This                 = ThisComponent.CurrentController.ActiveSheet
    Selection_This             = ThisComponent.CurrentSelection
  
'    Need this to not throw on using "ThisComponent.CurrentSelection.CellAddress".
'    Thrown is: "BASIC runtime error.", "Property or method not found: CellAddress.".
    If Selection_This.ImplementationName <> "ScCellObj" Then
        MsgBox "Alert: Macro <InsertNameHere> will not run due to recently changed area exceeding one cell."
    Else
      
        CellRange_Col_Flagged     = Sheet_This.getCellRangeByName("B3:B5")
        CellRange_IsIntersected    = Selection_This.queryIntersection(CellRange_Col_Flagged.getRangeAddress())
      
        If CellRange_IsIntersected.getCount() > 0 Then
          
            Selection_This_Index_C     = Selection_This.CellAddress.Column
            Selection_This_Index_R     = Selection_This.CellAddress.Row
          
            Cell_Col_Flagged_Raw        = Sheet_This.getCellByPosition(Selection_This_Index_C    , Selection_This_Index_R)
            Cell_Col_Flagged_Val        = Sheet_This.getCellByPosition(Selection_This_Index_C + 1, Selection_This_Index_R)
            Cell_Col_Flagged_Val_Mirror    = Sheet_This.getCellByPosition(Selection_This_Index_C + 2, Selection_This_Index_R)
            Cell_Col_CheckedOff         = Sheet_This.getCellByPosition(Selection_This_Index_C + 3, Selection_This_Index_R)
          
'            If "Flagged Mirror" and "Checked Off" are equal.
            If Cell_Col_Flagged_Val_Mirror.String = Cell_Col_CheckedOff.String Or Cell_Col_CheckedOff.String = "" Then
                Cell_Col_Flagged_Val_Mirror.String = Cell_Col_Flagged_Val.String
                MsgBox "Writ"
            Else
                MsgBox "ERROR NOT TIME FOR INPUT"
            EndIf
          
'            MsgBox Cell_Col_CheckedOff.String
'            Cell_This_Counter.String = Cell_This.String
'            MsgBox Cell_This_Counter.String
'            MsgBox getCellByAddress(Selection_This.CellAddress)
'            MsgBox Selection_Counterpart.Value
'            MsgBox Selection_This_Index_C
'            MsgBox Selection_This_Index_R
          
        EndIf
      
    EndIf

End Sub

Forward thanks.

 

[Ralston18]

With respect to:

"Hope someone can spare the time, check the code, and give some guidance."

= = = =

Question about the overall spreadsheet and its' design:

Are you restricted to two date columns?

The now being something like "Column1_Original_Date" and "Column2_Comparison_Date".

I suggest that three columns be used: "Column1_Original_Date", Column2_Revised_Date", and "Column3_Comparison_Date". (You can rename the columns as applicable and appropriate.)

At the time of initial entry "Column1_Original_Date" could be also be automatically entered into "Column2_Revised_Date". And Column1_Original_Date then locked against further changes at some point in the process.

[Note: Not sure when or where Column3_Comparison_Date is entered. Or when the process allows a future change to "Column2_Revised_Date".]

Overall though comparing the three dates becomes trivial: compare the dates in Columns 1 and 2.

If the dates in Columns 1 and 2 are different then compare Columns 2 and 3 to invoke/trigger whatever actions/rejections are applicable.

Suggestion: You could create a function that does the necessary date comparisons (IF THEN ELSE) to achieve the required results.

Plus using 3 date columns adds the advantage of preserving the date in Column1_Original_Date should that date be needed for future references and usage. Likely to happen. And if so then it may be even more difficult to revise all of the macros and associated code.

= = = =

Very good that you are working on solutions. You have presented much more work and effort than many others who post with similar questions.

And in the process of working out the macros etc. you have probably learned quite a bit about it all. That is good and not at all a waste of time and effort.

I think of macros more as a way to automate repetitious actions. Not as a primary way of making logical decisions with respective data.

However, when things become cumbersome and convoluted that is the time to step back and take a look at the root issue.

Occam's Razor.

https://fs.blog/occams-razor/

 

[KtX2SkD]

I appreciate the compliment, Mr. Ralston. I was worried about being overwhemlingly detailed but at the same time I can hardly stand being intentionally incomplete, and, not frequently but sometimes, there are people who prefer to read one long item instead of multiple short ones with mandatory hiatuses in-between.

I'm a little lost in the text, but it seems you'd like clarification of my original paragraph #1 describing the original problem. I initially stated "two date columns", as in they are the actual inputs and meaningful elements, but we can add support columns as needed, but obviously less is better when it's feasible.

Let us describe the columns as "Date Due" and "Date Completed". The idea is items will become due, then completed, then due, then completed, for a long or indefinite amount of time, which means both dates for whatever item will probably be continuously updated over & over by the user. The restriction in user input I have in mind right now is: "if there is a due date already, and it's bigger than the completed date, then refuse to update the due date, because item is still due and wasn't completed yet".

Non-marco-wise, I can probably make a cell in a third column type "ALERT" inside itself or use conditional formatting as an alert for whenever wrong input is detected, and a reasonable user will promptly realize the mistake and CTRL+Z, but I'd like something more fool-proof and rigorous (preserve correct data) and more noticeable (alert in the face) than that, hence looking into marcos.

As partially noted in original paragraphs #3 and #4, and perhaps I didn't clarify this earlier, I think I don't have a problem in reading data, writing data, or applying boolean checks and decision-making. What I'm seemingly stuck on is the inability for the macro to notice user input via the <CTRL + ;> hotkey, and the overkill over-summoning of the macro for possibly millions of cells instead of just the desired three. Each of these two issues is tied to whichever event I try to tie my macro to, and I'm curious to learn how to solve both but one would be enough to get the objective working.

You mention copying the value from an input column to a backup column, for the sake of backup, and facilitating the validation/calculation, and I think I'm already trying that, being described in my code as "mirror". I'd like to avoid that though if possible, considering CTRL+Z seems to work to undo the macro's actions, and the data isn't "sensitive" so losses aren't objectively a big deal, but I get the sentiment and I'll definitely keep it in mind (will probably have to do it when I repeat this in MS Excel later).

I hope that I covered all what you expressed? Sorry if I missed anything, for some reason I'm a bit confused while navigating through it.

- KitCat.

 

[Ralston18]

The requirement/restriction being that you can only have the two date columns: Date Due and Date Complete.

And no further design flexibility from your perspective.

= = = =

IF Date Due >= Date Complete THEN [disable input into Date Due field/cell]

The THEN could be to execute some action that should include a popup message. And depending on the overall requirements you could make use of some "ELSE" action.

For example: ELSE could be an instruction regarding how complete what needs to be done and thus update the Date_Complete.

The actions could be macros. I am not sure that macros are fully needed.

As another option you can conditionally lock the applicable date cells as necessary. ( And actually should do so the moment a user opens the spreadsheet.) Color code if desired to show the lock.

FYI as a general reference.

https://www.extendoffice.com/documents/excel/3779-excel-lock-cell-based-on-another-cell-value.html

https://howtoexcelatexcel.com/blog/highlight-locked-cells-with-the-cell-function-formula-friday/

Using "Protect" may be a viable option.

Fully agree: such things are confusing and it does take some time and effort to work through the logic. Especially when trying to anticipate anything that end users can do, may do, may attempt, or simply try not to do.

Remember I, we (anyone following this thread) are looking through a very tiny porthole and not truly be able to see everything.

There may be other ideas and suggestions.

 

[KtX2SkD]

The requirement/restriction being that you can only have the two date columns: Date Due and Date Complete.

And no further design flexibility from your perspective.

Not really, I did mention:

[...] I initially stated "two date columns", as in they are the actual inputs and meaningful elements, but we can add support columns as needed, but obviously less is better when it's feasible.

and regarding:

IF Date Due >= Date Complete THEN [disable input into Date Due field/cell]

The THEN could be to execute some action that should include a popup message. And depending on the overall requirements you could make use of some "ELSE" action.

For example: ELSE could be an instruction regarding how complete what needs to be done and thus update the Date_Complete.

I'm already attempting that in the original post, problem as I'm originally noting is:

• When attaching the macro to the "content changed" event, the macro doesn't seem to trigger when the hotkey for inserting dates <CTRL + ;> is used, rendering the macro useless.
• When attaching the macro to the "formulas calculated" event, the macro seems to run for so many cells instead of the programmed three, leaving me to think it is running for every cell within the sheet, which obviously screws up with performance.

I appreciate the references for using locks. I'll consider them but for now it seems the problem precedes that level.

 

[Ralston18]

Date insertion - using this method:

https://bettersolutions.com/excel/dates-times/shortcut-keys.htm

That would make the Date Due the current date. Not what I would expect....

What other date functions (if any) are being used?

Where does the user enter the dates?

Direct entry into the spreadsheet date cells?

Some form or other spreadsheet?

Workflow:

Date Due is initially entered and the Date Complete is simply left blank until someone deems the effort complete and enters Date Complete.

Dates all being static and not really dependent on system date. Other than perhaps the initial Date Due being entered via Hotkey for the current date + some number of days later. Calculated.

However, the workflow includes someone going back and creating/entering a new Date Due and blanking out the Date Complete.

And Due Due can never be >= Date Complete

Repeats as necessary....

Edit my workflow to show the correct process you are trying to work through.

= = = =

I am wondering about the requirement for a "content changed" macro.

If the date(s) are changed then that is self-evident and you only need to do the necessary date comparisons to allow or disallow further actions as warranted.

 

[KtX2SkD]

• That would make the Date Due the current date. Not what I would expect....
  • Seems there's a language issue here, perhaps "Date Assigned" is more accurate than "Date Due"...
• What other date functions (if any) are being used?
  • Unsure of meaning, but if input was meant then only hotkey is <CTRL+;>, maybe sometimes F2 then manually typing a date.
• Where does the user enter the dates?
  • Directly into the two columns "Date Assigned" and "Date Completed" if possible.

Workflow-wise, I'm imagining:

1. New sheet, three rows are made for three tasks, let's say one of them is "charge my phone". Initially, both dates are blank.
2. At some point in time I see my phone is low on battery, so I input a value to "Date Assigned", for this one the usual input method <CTRL+;> is suitable, and so I'll use that.
3. During the same day, or a future day, I'm done with charging my phone, and so I'll go to "Date Completed" and also hit <CTRL+;>, inputting a value.
   
   
4. A few days later my phone is low on battery again, so I go to "Date Assigned" and hit the hotkey again.
5. I charge the phone on the same day, but I forget or get busy to update "Date Completed". The next day I remember or get free time and attempt to update "Date Completed" using F2 and inputting yesterday's date.
   • Say I screw up and type 2021 in the date, I should receive an error, because I'm reducing value of "Date Completed", and because I'm inputting a "Date Completed" value less than "Date Assigned".
   • Thanks to the error, I have forceibly noticed a mistake due to a prompt coming at my face, and the value of "Date Completed" remained unchanged, a behaviour that is not undesirable, and sometimes would be desirable.
     
6. A week later, battery is low again, so I go to "Date Assigned" and hit the hotkey again.
   • Say my computer's clock is wrong and it causes the hotkey click to assign a value that is below either values of "Date Assigned" or "Date Completed", an error should be thrown, just as before, with a pop-up and without changing the value that was attempted to be changed.

I hope I have properly clarified what was asked

 

[Ralston18]

The root issue is using the hotkey for the date and catching any date errors/discrepancies with respect to Date Assigned and Date Completed.

You can do that. And you can probably make it work without needing prompts in your face. The objective being to just alert you to some date error of omission or commission.

Three fields: "Date Assigned", "Date Completed", and "Task" [Actually cells, but we can use "fields".]

Use Conditional Formatting to color code the Task field. You just need to set up the necessary rules.

If Date_Completed or Date Assigned is < TODAY() then color the Task field red.

If Date_Completed >= Date Assigned then color the Task field green. Else red.

Etc., etc..

Using green and red is the simplest. However if you want some indication of what the error is you could additionally use orange or other colors.

You choose the logic and colors as you deem appropriate. In any case you would have immediate feedback if there is some date related error.

And there is the option of coloring the entire cell or just changing font color.

Might be a bit more cumbersome if Date Assigned and/or Date Completed are empty but that can be addressed with some thought to the logic involved. IF THEN ELSE.

And other date related functions may prove useful. TODAY(), NOW().

The main advantage is that the logic is all addressed within the Task field and only needs to reference back to the Date Assigned and Date Completed fields for the dates they contain. Or if blank/null. No dependency on when and how the dates are or were placed into the Date Assigned and Date Completed fields.

Google "Excel Cell conditional formatting". Many, many links to be found. Read a few and revise the search criteria to narrow things down as necessary for your requirements.

Then set up a simple test spreadsheet to first get a sense of what all is involved and test the logic. Get it working first then streamline if possible.

You could bundle the color coding logic into a custom function and apply the function anywhere you wish.

And filter and sort by colors:

https://www.timeatlas.com/sort-excel-by-color/

= = = =

I have a simpler version using the same process. I keep track of financial information that is received in monthly, quarterly, semi-annually, and annual statements.

I enter the financial information and the statement date. If some statement date, for example, is 30 or 90 days less thanTODAY() then the statement date automatically changes from black to red. (I chose to change font color versus cell color. Font color was nicer looking and not so blocky.)

I can easily spot missed updates. Or date entry mistakes. Like entering 2021 in the first months of 2022.

 

[KtX2SkD]

I'm familiar with conditional formatting, and use it frequently. I stated at the original post that while I can do what you've just described, I chose to consider macros instead. Reasoning is below, some of which was already mentioned:

• Conditional formatting entails temporary loss of data.
• Sometimes, conditional formatting lags or bugs out.
• In LibreOffice, it seems that there isn't native support for precedence or co-existence of multiple conditional formats.

and, as stated earlier, while part of this endavour is about achieving a functional objective, another part is learning more about marcos by considering different means of achieving the same objective.

 

[Ralston18]

Curious about "Conditional formatting entails temporary loss of data."

Conceptually, all I can think of is that you want to retain the last date value/entries in some manner while you update and error check the date fields. Then roll back the original values if there is an error. Probably need some "on focus" trigger for each date field.

Not familiar enough with LibreOffice per se with regards to its' capabilities (or lack thereof) with supporting multiple conditional formats.

Anyway now at a bit of an impasse regarding other ideas and suggestions.

Hopefully there will be other postings that can help move things along.