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.