Creating a basic calendar in LibreOffice Calc

[Spiritual6260]

Hi all. I’m trying to create a calendar that has each date on a separate line with info about said date on the right. This is a picture from Google Sheets but this is my rough idea:

There are two things here that I would like to automate with a macro. I would like “Sat” and “Sun” to always be in bold in column A. I would also like to insert a thick border between every instance of “Sun” and “Mon”.

I am very new to macros in LibreOffice and this all i could manage to write on my own:

Sub Main

	Dim currDoc as object
	Dim currSheet as object
	Dim currCell as object
	
	currDoc = ThisComponent
	currSheet = currDoc.sheets(0)
	
	'Format all *Sat* and *Sun* strings as bold'
	For A = 0 To 100 step 1
		currCell = currSheet.getCellByposition(1, i)
		
		If currCell.string = "Sat" Or "Sun" Then
			currCell.string.Bold	
		End If

	'If current cell is the string *Sun* and the next cell is the string *Mon*,
	'insert a 2.25 thickness bottom border from An to Zn
	For A = 0 To 100 step 1
		currCell = currSheet.getCellByposition(1, i)
		
		End If
	Next
End Sub

I would appreciate any and all help on this issue and feel free to direct me to some sources where i can learn this topic further.

[karolus]

Can be done by →Format →conditional Format no need for clumsy makro-code

[jfn]

I add my vote to Karolus’ suggestion: absolutely no need for any kind of macro.

Calendars are very easy to create and decorate using:

• date cells with operations and formatting (using cell styles!)
• possibly conditional formatting (for instance to display legal holidays in another style)

[Hrbrgr]

https://extensions.libreoffice.org/?q=Calendar&action_doExtensionSearch=Search

[Spiritual6260]

Thank you for your response! I would not have found this feature on my own. But now I have a new question. This is my current table which is made with conditional formatting:

How can i make my bold lines extend farther to the right? So in another way - How can i take the condition from one column and apply the format to other columns? for example if “Sun” then apply Bold border line to 5 next columns?

[KamilLanda]

You can use one loop and fill two multiranges, one multirange for Bold and 2nd for Border. The use of multirange is the fastest for formatting many different cells.

Sub formatCells
	on local error goto bug
	dim oDoc as object, oSheet  as object, oRange as object, i& 
	dim oRanges as object, oRangeAddress as new com.sun.star.table.CellRangeAddress, oRanges2 as object, oRangeAddress2 as new com.sun.star.table.CellRangeAddress
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	
	rem initial info for all ranges in multirange for BOLD
	oRanges=oDoc.createInstance("com.sun.star.sheet.SheetCellRanges") 'object for multirange
	with oRangeAddress
		.Sheet=oSheet.RangeAddress.Sheet 'index of current sheet
		.StartColumn=0 'column A (the indexing is from 0 -> the cell A1=0,0; B1=1,0 etc.)
		.EndColumn=.StartColumn 'same column
	end with
	rem initial info for all ranges in multirange for BORDER
	oRanges2=oDoc.createInstance("com.sun.star.sheet.SheetCellRanges") 'object for multirange
	with oRangeAddress2 'initial info for all ranges in multirange
		.Sheet=oSheet.RangeAddress.Sheet 'index of current sheet
		.StartColumn=0 'column A (the indexing is from 0 -> the cell A1=0,0; B1=1,0 etc.)
		.EndColumn=3 'column D
	end with	
	
	for i=6 to 100 step 7 'from 1st Sat (indexing is like in sheet, so 1st line has 1 etc.)
		rem set current range for BOLD
		with oRangeAddress 'but here, the indexing of rows is from 0, so 6th line has index 5
			.StartRow=i-1 'row with actual Sat
			.EndRow=i 'row with actual Sun
		end with
		oRanges.AddRangeAddress(oRangeAddress, true) 'add the range to the multirange
		
		rem set current range for BORDER
		with oRangeAddress2
			.StartRow=i 'row with actual Sun
			.EndRow=.StartRow 'same row
		end with		
		oRanges2.AddRangeAddress(oRangeAddress2, true)
	next i
	
	rem BOLD
	oRanges.CharWeight=com.sun.star.awt.FontWeight.BOLD
	
	rem BORDER
	dim oBorder as new com.sun.star.table.BorderLine
	with oBorder
		.Color=RGB(200, 0, 150)
		.OuterLineWidth=40
	end with
	oRanges2.BottomBorder=oBorder 'set the border
	
	createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(oDoc.CurrentController.Frame, ".uno:Deselect", "", 0, array()) 'no selection
	exit sub
bug:
	bug(Erl, Erl, Error, "formatCells")
End Sub

And good sources for Basic macros

Macro Guides | LibreOffice Documentation - LibreOffice User Guides

https://help.libreoffice.org/latest/en-US/text/sbasic/shared/main0601.html

https://wiki.documentfoundation.org/Documentation/DevGuide/LibreOffice_Basic

For Python sources please search the English - Ask LibreOffice :-).

[malsasa]

Yes, what you actually need is just Conditional Formatting, my friend, like what friends above said.