Version: 7.5.2.2 (X86_64) / LibreOffice Community
Build ID: 53bb9681a964705cf672590721dbc85eb4d0c3a2
CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
sub MyTest
dim dispatcher as object
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
REM ↓↓↓UnProtect Sheet
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "Protect"
args13(0).Value = false
dispatcher.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:Protect", "", 0, args13())
REM ↑↑↑
REM ↓↓↓ Force ReCalculate
dispatcher.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:CalculateHard", "", 0, Array())
REM ↑↑↑
dim InValue as string
dim AT,BI as string
dim a,b,c,StartRow,StartColumn
dim UnUsed,PasteColumn
ThisComponent.getCurrentController().getActiveSheet().getCellRangeByName("AT41:AT91").clearContents(3)
REM ↓↓↓ReSet BI 1 to 4
UnUsed = 1
for c = 4 to 51 REM make 51
ThisComponent.getCurrentController().getActiveSheet().getCellByPosition(60,UnUsed+2).setValue(c-3)
UnUsed = UnUsed +1
next
REM ↑↑↑
REM ↓↓↓Start loops to get UnUsed numbers
UnUsed = 4
for b = 55 to 59 REM make 59 BD to BH
StartRow = b
StartColumn = 3
for c = 4 to 51 REM make 51
UnUsed = ThisComponent.getCurrentController().getActiveSheet().getCellByPosition(StartRow,StartColumn).getValue
If UnUsed = 0 then
c = 51
else
ThisComponent.getCurrentController().getActiveSheet().getCellByPosition(60,UnUsed+2).setValue("0")
End if
StartColumn = StartColumn +1
next
next
REM ↑↑↑
REM ↓↓↓ Transfer UnUsed to AT42
StartRow = 3
PasteColumn = 41
for a = 42 to 91 REM make 91
UnUsed = ThisComponent.getCurrentController().getActiveSheet().getCellByPosition(60,StartRow).getValue
if UnUsed = 0 then
StartRow = StartRow + 1
Else
ThisComponent.getCurrentController().getActiveSheet().getCellByPosition(45,PasteColumn).setValue(UnUsed)
PasteColumn = PasteColumn + 1
StartRow = StartRow + 1
End if
next
REM ↑↑↑
REM ↓↓↓ GoTo AU$!
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$AU$41"
dispatcher.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, args10())
REM ↑↑↑
REM ↓↓↓ Protect sheet
dispatcher.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:GoToCell", "", 0, args10())
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "Protect"
args12(0).Value = true
dispatcher.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:Protect", "", 0, args12())
REM ↑↑↑
End sub
Suspect button portion in ()
Standard.Module1.MyTest (document, Basic)
[erAck: edited to format as code, see This is the guide - How to use the Ask site? - #6 by erAck]