I thank you very very much for your replying, but I tried a new approach.
I tried the following code line on CHANGE EVENT
if len(trim(oActiveCell.string)) = 0 then oActiveCell.String ="", but the libreoffice keeps on “thinking” that there is a string on cell.
Maybe it might be a bug.
The topic’s trajectory has changed.
You can enter an empty value in a cell in different ways, for example:
oActiveCell.Formula =""
A few remarks:
- You have true lots of functions and subroutines in a single module of the attached file(s). The module has
Option VBAsupport 1enabled. Be aware of the fact that some of the seemingly common Basic constructs work differently there than in a module without VBA support. - If your problem is that someone may have accidentally entered whitespace into a cell, then your question is a good example of the so-called XY problem.
- Had your question clearly addressed the now supposed issue, I had answered with very few lines of code:
Sub removePlainWhitespace(Optional pRgs)
REM May be called from an onContentChanged handler.
If IsMissing(pRgs) Then pRgs = ThisComponent.CurrentController.Selection
On Local Error Goto fail
relevantRanges = pRgs.queryContentCells(4) REM 4 meaning 'String'
sd = relevantRanges.createSearchDescriptor()
With sd
.SearchRegularExpression = True
.SearchString = "^\s+$"
.ReplaceString = ""
End With
findings = relevantRanges.FindAll(sd)
findings.replaceAll(sd)
fail:
End Sub
- If you want to clean already afflicted sheets, you can also call the Sub per sheet for the complete sheet as the value of pRgs.
- The overall task seems to be clearly one better done based on a DataBase for many relevant reasons. However, that’s not my preferred field.
- Mis(?)using spreadsheets for database tasks is widely done. However, doing it we should at least regard the most basical principles of databasing: The “third NF” can be a guide. Then data tables must not contain evaluation (except some helping columns, probably) or output oriented formatting (“prettyprint” / merging). Backuping and rights management must be well considered. Maintainability must also be ensured for the time when the original developer is not (or no longer) available.
Thanks for your patience
You’ve given me a ideia.
A simple one, but the simpler, the better.
I wrote this following code lines to delete all rows with empty cell.
Sub CheckLastDataRow
Dim lf as String : lf = chr(10)
Dim oDoc As Object : oDoc = ThisComponent
Dim oSheet As Object : oSheet = oDoc.Sheets.getByName(“Gratuita”)
Dim oCursor, oCell as Object
Dim lRow as Long
oCursor = oSheet.createCursor
oCursor.gotoEndOfUsedArea(False)
lRow= oCursor.RangeAddress.EndRow
oCell = oSheet.getCellByPosition(6,lRow) ’ number 6 refers to column “Destinatario”
Do While Len(Trim(oCell.String)) = 0
oSheet.rows.RemoveByIndex(lRow,1)
lRow = lRow - 1
oCell = oSheet.getCellByPosition(6,lRow)
Loop
End Sub
REALLY THANKS FOR YOUR HELP…You´re GREAT.