Hello. I’m working on a spreadsheet to help me estimate parts quotes for my job. I have it working well for my needs, but I have run into what appears to be a bug.
Version: 7.4.2.3 (x64) / LibreOffice Community
Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL
I have setup my sheet to be protected except for cells that I can fill out with part information, Part numbers, prices, quantities, etc. I also have two buttons that run macros. One clears all the cells and the other calculates subtotals in the gaps between lists of parts. What’s happening is, random cells become protected as I use the sheet. It seems to only happen in column B. When this happens, my clear macro won’t work until I find the protected cell and uncheck protect cell in the format cell screen. The Subtotal macro, however, will edit the protected witch I find strange. Is this user error or is something stange happening?
Clear Marco:
Sub clear()
oCtrl = ThisComponent.CurrentController
oCtrl.Select(oCtrl.ActiveSheet.GetCellRangeByName("A3:D100"))
ThisComponent.getCurrentSelection.ClearContents(23)
oCtrl.Select(oCtrl.ActiveSheet.GetCellRangeByName("F3:F100"))
ThisComponent.getCurrentSelection.ClearContents(23)
oCtrl.Select(oCtrl.ActiveSheet.GetCellRangeByName("A3"))
End Sub
Subtotal Macro:
REM ***** BASIC *****
Sub caculate_subtotal()
oCtrl = ThisComponent.CurrentController
subTotal = 0
For i = 3 to 100
rem Scann Document for blank line
If oCtrl.ActiveSheet.GetCellRangeByName("E"+CStr(i)).Value = 0 Then
If subTotal > 0 Then
rem Print, then clear, subtotal on blank line.
oCtrl.ActiveSheet.GetCellRangeByName("B"+CStr(i)).setString("Subtotal: $"+Format(subTotal,"0.00"))
subTotal = 0
end If
else
rem if line is not blank, add value to subtotal
subTotal = subTotal + oCtrl.ActiveSheet.GetCellRangeByName("E"+CStr(i)).Value
end If
next
End Sub
Cell B5 has became protected in the attached document.
quote.ods (17.3 KB)