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 data, 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.
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 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 range B3 to B5, and will involve some read & write against range C3 to E5.
-
I’m hoping macro is small & clear enough to not need comments or elaboration, but if desired or necessary then I’ll definitely provide.
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.