Solved: Specifying column width in sheet

Hi,
I have taken following code from a webpage which uses a sub to resize particular columns. Is it possible to make this work as a worksheet_change event? ie on opening the sheet automatically resize the column or columns to a predetermined width - only interested in columns really.

[VBA]Sub SetColumnWidthMM(ColNo As Long, mmWidth As Integer)
' changes the column width to mmWidth
Dim w As Single
If ColNo < 1 Or ColNo > 255 Then Exit Sub
Application.ScreenUpdating = False
w = Application.CentimetersToPoints(mmWidth / 10)
While Columns(ColNo + 1).Left - Columns(ColNo).Left - 0.1 > w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth - 0.1
Wend
While Columns(ColNo + 1).Left - Columns(ColNo).Left + 0.1 < w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth + 0.1
Wend
End Sub
Sub SetRowHeightMM(RowNo As Long, mmHeight As Integer)
' changes the row height to mmHeight
If RowNo < 1 Or RowNo > 65536 Then Exit Sub
Rows(RowNo).RowHeight = Application.CentimetersToPoints(mmHeight / 10)
End Sub
Sub ChangeWidthAndHeight()
SetColumnWidthMM 1, 20 '1st column size same as 10.14(76 pixels)
SetRowHeightMM 1, 6 '1st row size same as 16.50(22 pixels)
End Sub[/VBA]

thanks for your help

regards

jon

the short answor is yes

the lines of code your interested in is

Columns(1).ColumnWidth =

or

range("A1").ColumnWidth =

Thanks - actually may be better with workbook_open event.

regards

jon

Maybe I'm not following, but just go to your ThisWorkbook module and enter...

[vba]Private Sub Workbook_Open()
Call SetColumnWidthMM(1, 20)
End Sub[/vba]

Edit: Oh, by the way, I would alter two lines of code...
[VBA]Sub SetColumnWidthMM(ColNo As Long, mmWidth As Integer)
' changes the column width to mmWidth
Dim w As Single
If ColNo < 1 Or ColNo > ThisWorkbook.Sheets(1).Columns.Count Then Exit Sub
Application.ScreenUpdating = False
w = Application.CentimetersToPoints(mmWidth / 10)
While Columns(ColNo + 1).Left - Columns(ColNo).Left - 0.1 > w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth - 0.1
Wend
While Columns(ColNo + 1).Left - Columns(ColNo).Left + 0.1 < w
Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth + 0.1
Wend
End Sub

Sub SetRowHeightMM(RowNo As Long, mmHeight As Integer)
' changes the row height to mmHeight
If RowNo < 1 Or RowNo > ThisWorkbook.Sheets(1).Rows.Count Then Exit Sub
Rows(RowNo).RowHeight = Application.CentimetersToPoints(mmHeight / 10)
End Sub[/VBA]

This ensures the code will stay dynamic with multiple versions of Excel in lieu of the column/row expansion of the newer versions.

HTH

Sorry - probably best if I explain what I'm trying to achieve.

I have a workbook with 18 sheets. 12 of the sheets are formatted exactly the same, the other 6 (each one) are formatted differently.

I have another workbook which is formatted the same as book 1 from which info is copied & pasted in to book 2 (and then other actions performed).

I find that even using pastespecial some of the formatting gets altered.

What I wanted to try & do was set the format of each sheet (in both books) 'behind the scenes' so to speak.

e.g sheet 1 column 1 size 20.00, col 2 size 15.00, col 3 size 2 etc etc
sheet 2 column 1 size 17.00 etc etc

Is this possible by entering code in each sheet (is there a worksheet_open event?) or in a workbook_open event referencing each sheet?

thanks

Hi again,

This is what I have so far

[VBA]Option Explicit
Private Sub workbook_open()
Application.ScreenUpdating = False
Worksheets("SUMMARY").Activate
Columns("A:A").ColumnWidth = 10
Columns("B:B").ColumnWidth = 16
Columns("C:C").ColumnWidth = 16
Columns("D:D").ColumnWidth = 16
Columns("e:e").ColumnWidth = 8
Columns("f:f").ColumnWidth = 12

Worksheets("CMF").Activate
Columns("A:A").ColumnWidth = 20.71
Columns("B:B").ColumnWidth = 9.43
Columns("C:C").ColumnWidth = 1.29
Columns("D:D").ColumnWidth = 8
Columns("e:e").ColumnWidth = 17.71
Columns("i:i").ColumnWidth = 10.43
Columns("j:j").ColumnWidth = 11.29
Columns("k:k").ColumnWidth = 6
Columns("r:r").ColumnWidth = 10.43
Columns("s:s").ColumnWidth = 11.29
Columns("t:t").ColumnWidth = 6
Columns("u:u").ColumnWidth = 10.43
Columns("v:v").ColumnWidth = 11.29
Columns("w:w").ColumnWidth = 6

End Sub[/VBA]

I have 11 other worksheets identical format to "CMF" named e.g. "ETF", "EUF" - anyway to do some kind of loop so I don't have to retype the format for each named sheet?

thanks a lot

Jon

try something like this

Code:

---

Option Explicit
Private Sub workbook_open()
Application.ScreenUpdating = False
With Worksheets("SUMMARY")
    .Columns("A").ColumnWidth = 10
    .Columns("B").ColumnWidth = 16
    .Columns("C").ColumnWidth = 16
    .Columns("D").ColumnWidth = 16
    .Columns("E").ColumnWidth = 8
    .Columns("F").ColumnWidth = 12
End With
Call standardcwidth("CMF")

End Sub


Private Sub standardcwidth(a As String)
With Worksheets(a)
    .Columns("A").ColumnWidth = 20.71
    .Columns("B").ColumnWidth = 9.43
    .Columns("C").ColumnWidth = 1.29
    .Columns("D").ColumnWidth = 8
    .Columns("E").ColumnWidth = 17.71
    .Columns("I").ColumnWidth = 10.43
    .Columns("J").ColumnWidth = 11.29
    .Columns("K").ColumnWidth = 6
    .Columns("R").ColumnWidth = 10.43
    .Columns("S").ColumnWidth = 11.29
    .Columns("T").ColumnWidth = 6
    .Columns("U").ColumnWidth = 10.43
    .Columns("V").ColumnWidth = 11.29
    .Columns("W").ColumnWidth = 6
End With
End Sub

---

Ah I see

Just call the sub and replace "CMF" with the sheetname each time

thanks

Jon