LibreOffice Calc Macro Skipping, and Event Spamming, Plus Clumsy Code (First Macro)
LibreOffice Calc Macro Skipping, and Event Spamming, Plus Clumsy Code (First Macro)
Regarding:
"Hope someone can spare the time, check the code, and give some guidance."
Consider whether you are limited to just two date fields. Currently, it seems like "Column1_Original_Date" and "Column2_Comparison_Date". It would be better to use three columns: "Column1_Original_Date", "Column2_Revised_Date", and "Column3_Comparison_Date". You might also allow "Column1_Original_Date" to auto-populate in "Column2_Revised_Date" during entry. Once entered, it should be fixed from further changes.
At the start, "Column1_Original_Date" could automatically appear in "Column2_Revised_Date". The idea is that comparing the three dates becomes simple—just check Columns 1 and 2. If they differ, then compare Columns 2 and 3 to trigger any needed actions or prevent errors.
A possible improvement would be to build a function for date comparisons (IF THEN ELSE) to simplify this process. Using three columns also helps keep the original date in "Column1_Original_Date" for future use, which is often useful.
It’s worth noting that solving this could become more complex if you need to adjust macros and related code later.
Very positive feedback on your efforts. You’ve shown significantly more work and dedication than many others with similar queries. While developing macros can be a great way to automate repetitive tasks, it’s not the main goal when decisions rely on clear data comparisons.
When details grow complicated, it’s wise to pause and address the core problem.
Occam's Razor.
I appreciate the feedback, Mr. Ralston. I was concerned about being overly detailed yet struggling with being too vague at times. While I can manage to provide concise information, I sometimes find myself hesitating between being fully explicit and leaving gaps that might confuse the reader.
I understand you were seeking a clearer version of my first paragraph about the original issue. I initially referred to "two date columns," which represent the actual inputs and key elements. We can certainly add additional support columns if needed, though it's generally better to keep things simpler when possible.
Let’s rephrase the columns as "Date Due" and "Date Completed." The goal is to track a sequence: items become due, then marked as completed, then due again, then completed once more—over an extended or indefinite period. This means both dates will likely be updated repeatedly by the user. My current restriction in user input is: if a due date already exists and it’s later than the completed date, I won’t update the due date, since the item is still pending.
Off the top of my head, I could create a third column labeled "ALERT" to signal errors or irregularities. Alternatively, I can apply conditional formatting or use alerts within cells to highlight incorrect entries. A more robust solution would help avoid mistakes and make it easier to spot issues quickly.
As you noted in earlier sections, I’m not struggling with reading or writing data, nor with applying logic or conditions. The challenge lies in ensuring the macro responds accurately to user input—especially when using keyboard shortcuts like <CTRL + ;>—and avoiding unnecessary complexity for such tasks.
Each of these points is connected to the specific actions I plan to implement. I’m open to exploring both approaches, but ideally one would suffice to achieve the desired outcome.
Please let me know if I’ve misunderstood anything, as I’m trying to make this clearer.
The condition that only allows the two date columns—Date Due and Date Complete—remains unchanged.
And no additional design freedom is permitted from your side.
If the due date is later than the completion date, the input for Date Due should be disabled.
This decision could trigger a popup or an alternative action.
Depending on the overall rules, you might choose to update Date Complete instead.
Macros aren’t necessary in this case.
Another possibility is to lock the relevant date cells automatically when the sheet opens, with a color indicator if needed.
For reference:
- https://www.extendoffice.com/documents/e...value.html
- https://howtoexcelatexcel.com/blog/highl...la-friday/
Considering "Protect" as a solution.
It’s worth noting that this approach can be confusing and requires time to understand the logic, especially when anticipating user actions.
😵
Keep in mind we’re viewing through a very limited view here and can’t fully grasp everything.
Other ideas might exist.
Not really, I did mention the points you made:
In the original post, I was already addressing the issue, but the main concern is:
When linking the macro to the "content changed" event, it doesn't activate with the hotkey for inserting dates <CTRL + ;>, making the macro ineffective.
When linked to the "formulas calculated" event, it runs in all cells instead of the intended three, which seems to affect performance negatively.
I appreciate the references about locks. I’ll take them into account, but currently it looks like the problem is more fundamental.
The method used involves inserting dates using the provided link, which sets Date Due to the current date. This doesn’t match expectations. Other date functions are also in use. The dates are entered either directly into the spreadsheet cells or through a different spreadsheet format. The workflow starts with entering Date Due and leaves Date Complete empty until it’s deemed complete. Dates remain static, not tied closely to the system date. Occasionally someone adjusts Date Due by creating a new entry and clearing Date Complete, causing the cycle to restart. Due Due is always less than or equal to Date Complete. The process needs adjustment to clearly reflect the intended steps.
This would indicate the current date rather than what I expected.
It appears there might be a terminology mismatch, maybe "Date Assigned" is more fitting than "Date Due".
What alternative date-related functions are being utilized?
Uncertain about the purpose, but if the input was intended, perhaps the shortcut is <CTRL+;>, and sometimes F2 followed by manual entry.
Where exactly should the dates be entered?
Directly within the two columns labeled "Date Assigned" and "Date Completed" would be ideal.
From a process perspective, I imagine:
Creating a new sheet with three rows for three tasks, such as "charge my phone". At first, both fields are empty.
At some point, when the phone runs low, I enter a value in "Date Assigned" using <CTRL+;>, which works well.
Later, after charging, I go to "Date Completed" and press <CTRL+;> again, typing a date.
A few days later, the phone is low again, so I return to "Date Assigned" and use the shortcut.
I charge it on the same day but forget or get distracted, so I type a value into "Date Completed".
The next day, when I recall or have free time, I try to update "Date Completed" by pressing F2 and entering yesterday’s date.
If I accidentally type an incorrect year like 2021, I should see an error because the system expects the assigned date to be higher than the completed one.
This inconsistency makes me realize a mistake was made due to a prompt appearing suddenly, and the "Date Completed" value stays unchanged—something that could be helpful or preferable.
A week later, the battery drops again, so I go back to "Date Assigned" and press the shortcut.
If my computer’s clock is incorrect, it might assign a date lower than either "Date Assigned" or "Date Completed", triggering an error just like before, without altering the value I tried.
I’d like to confirm whether this clarification accurately reflects your needs.
The core problem lies in relying on the hotkey for dates and handling any discrepancies between Date Assigned and Date Completed.
You are able to achieve this. You can likely succeed without needing explicit prompts. The goal is simply to notify you about any date-related errors or oversights.
There are three fields: "Date Assigned", "Date Completed", and "Task" (actually cells, but we can refer to them as fields).
Apply Conditional Formatting to highlight the Task field.
Create rules based on these conditions:
- If Date_Completed or Date Assigned is less than today, mark Task red.
- If Date_Completed is greater than or equal to Date Assigned, mark Task green.
Other color schemes like orange can also be used for clarity.
You decide the colors and logic as you prefer. This will give you instant feedback on date issues.
If some fields are empty, you can use IF-THEN-ELSE logic.
Additional date functions such as TODAY() or NOW() can be helpful.
The benefit is consolidating all logic inside the Task field, relying only on the linked Date Assigned and Date Completed data.
This avoids needing to track dependencies on how dates were entered.
Search online for "Excel Cell conditional formatting" to find many examples.
Review a few and adjust your criteria accordingly.
Start with a basic test sheet to understand the setup before refining.
You might consider wrapping the color logic in a custom function for easier reuse.
Sorting and filtering by color can be done via external tools like Time Atlas.
I have a simpler approach using the same steps. I record monthly, quarterly, semi-annual, and annual financial data.
When entering a statement date, if it falls within 30 or 90 days of today, the date updates automatically from black to red (preferably font color over cell color).
This makes it easy to spot missed entries or incorrect dates.
(😳)
I'm used to working with conditional formatting and have applied it often. I mentioned at the start that while you've just described, I opted for macros instead. The reasoning here includes some points already discussed: conditional formatting can cause data loss temporarily, sometimes it slows down or encounters issues, in LibreOffice there isn't built-in support for precedence or having multiple formats run together, and this effort is part of a broader goal to understand more about macros by exploring various methods to reach the same result.
It seems you're exploring the idea behind "Conditional formatting entails temporary loss of data." Essentially, it appears you're thinking about keeping the last recorded date or entries while updating and verifying the date fields, then reverting any incorrect values. You might need a trigger when focusing on each date field. I'm not very familiar with LibreOffice's features regarding multiple conditional formats. Keep looking for more guidance.