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
EndIf
Next
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.