How do we set a column width

How do we set column width for a column in a worksheet in a workbook? I have a set of half dozen or so cells designed to input various such widths, scale, etc. I want to auto-apply those to column widths in the same sheet. Better still if I could connect that with a Calc EH, in which such formatting happened auto-magically. I dipped my toe in the Conditional formatting for starters, but I do not think it is quite what I need for this purpose. Thanks!

You must work with macros.

Can you clarify? What is Calc EH? Are you wanting to take the widths of existing columns and apply to other columns? Can you upload an example with comments on what you want with column widths in the document?

Event Handlers… when I enter a width in a cell, I want to auto-apply that to a column. To further elaborate, whether by static entry, or formula. Or at the very least when an overall trigger condition is observed, i.e. a range changes.

This would seem to be what you are looking for. It’s just a macro that loops through a designated named range to find column names and widths and applies them accordingly. Notice that the named range is pulled via a sheet reference, so you have to make sure that the WidthTable named range reference is scoped to the sheet in the Named Range Manager. Sheet scope for the WidthTable allows having different tables on different sheets while still using the same macro.

Option Explicit

Sub SetColumnWidths()
	Dim Sheet As Object
	Dim WidthTable As Variant
	Dim Column As Object
	Dim TableRow As Variant
	Dim ColumnName As String
	Dim ColumnWidth As Long
	Sheet = ThisComponent.CurrentController.Activesheet
	WidthTable = Sheet.NamedRanges.getByName("WidthTable").ReferredCells.DataArray
	For Each TableRow In WidthTable
		If TableRow(0) <> "" Then
			ColumnName = TableRow(0) & ":" & TableRow(0)
			ColumnWidth = TableRow(1)
			Column = Sheet.getCellRangeByName(ColumnName).getColumns().getByIndex(0)
			Column.Width = 100 * ColumnWidth 'Internal unit is 1/100 mm
End Sub

SoftColumnWidths.ods (19.4 KB)

Edit: When you duplicate a sheet, apparently the sheet events do not duplicate. So in the example Sample 2 does not fire the event, but Sample 1 does.