How do you Align text center in a cell using VBA?

Arcticwarrio

Active Member
Joined
Dec 6, 2005
Messages
439
i've tried a few things but cant quite get my head round it.

your help will be much appreciated

I have the following code,

i would like to select columns A:W and center the text

Cheers.

Code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
          Sh.Range("A1") = "W/O"
          Sh.Range("B1") = "CUSTOMER"
          Sh.Range("C1") = "DETAILS"
          Sh.Range("D1") = "CUST PART NO"
          Sh.Range("E1") = "STATUS"
          Sh.Range("F1") = "NOTES"
          Sh.Range("G1") = "DEPARTMENT"
          Sh.Range("H1") = "DATE"
          Sh.Range("I1") = "CUST ORDER NO"
          Sh.Range("J1") = "DEL NO"
          Sh.Range("K1") = "QTY"
          Sh.Range("L1") = "SALE PRICE"
          Sh.Range("M1") = "CARRIAGE OUT"
          Sh.Range("N1") = "TOTAL SALES"
          Sh.Range("O1") = "INT CODE"
          Sh.Range("P1") = "SUPPLIER"
          Sh.Range("Q1") = "COST PRICE"
          Sh.Range("R1") = "CARRIAGE IN"
          Sh.Range("S1") = "TOTAL HRS"
          Sh.Range("T1") = "LABOUR COST"
          Sh.Range("U1") = "TOTAL COST"
          Sh.Range("V1") = "GROSS PROFIT"
          Sh.Range("W1") = "GROSS PROFIT"
With Sh.Columns("A")
    .ColumnWidth = 9.71
End With
With Sh.Columns("B")
    .ColumnWidth = 24.29
End With
With Sh.Columns("C")
    .ColumnWidth = 47.71
End With
With Sh.Columns("D")
    .ColumnWidth = 16
End With
With Sh.Columns("E")
    .ColumnWidth = 13.57
End With
With Sh.Columns("F")
    .ColumnWidth = 24.71
End With
With Sh.Columns("G")
    .ColumnWidth = 15.43
End With
With Sh.Columns("H")
    .ColumnWidth = 8.57
End With
With Sh.Columns("I")
    .ColumnWidth = 16.29
End With
With Sh.Columns("J")
    .ColumnWidth = 8.41
End With
With Sh.Columns("K")
    .ColumnWidth = 7.71
End With
With Sh.Columns("L")
    .ColumnWidth = 12.43
End With
With Sh.Columns("M")
    .ColumnWidth = 15
End With
With Sh.Columns("N")
    .ColumnWidth = 13.71
End With
With Sh.Columns("O")
    .ColumnWidth = 12.14
End With
With Sh.Columns("P")
    .ColumnWidth = 23.29
End With
With Sh.Columns("Q")
    .ColumnWidth = 13.14
End With
With Sh.Columns("R")
    .ColumnWidth = 13.71
End With
With Sh.Columns("S")
    .ColumnWidth = 11.29
End With
With Sh.Columns("T")
    .ColumnWidth = 11.29
End With
With Sh.Columns("U")
    .ColumnWidth = 13
End With
With Sh.Columns("V")
    .ColumnWidth = 14.86
End With
With Sh.Columns("W")
    .ColumnWidth = 14.86
End With
With Sh.Range("A:W").Font
    .Size = 8
End With
With Sh.Range("1:1").Font
    .Bold = True
End With


          
          
End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
sorry to bother you all again, but is the any way to turn the autofilter on too?

eg manualy i highlight row 1 then Data>Filter>AutoFilter
 
Upvote 0
Code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    With Sh
        With .Range("a1:w1")
            .Value = Array("W/O", "CUSTOMER", "DETAILS", "CUST PART NO", "STATUS", _
                           "NOTES", "DEPARTMENT", "DATE", "CUST ORDER NO", "DEL NO", _
                           "QTY", "SALE PRICE", "CARRIAGE OUT", "TOTAL SALES", _
                           "INT CODE", "SUPPLIER", "COST PRICE", "CARRIAGE IN", _
                           "TOTAL HRS", "LABOUR COST", "TOTAL COST", _
                           "GROSS PROFIT", "GROSS PROFIT")

            .ColumnWidth = Array(9.71, 24.29, 47.71, 16, 13.57, 24.71, 15.43, _
                                 8.57, 16.29, 8.41, 7.71, 12.43, 15, 13.71, 12.14, _
                                 23.29, 13.14, 13.71, 11.29, 11.29, 13, 14.86, 14.86)

            .HorizontalAlignment = xlCenter
            With .Font
                .Size = 8
                .Bold = True
            End With
        End With
    End With
End Sub
 
Upvote 0
cheers veyselemre,

i've never used arrays before but that shortens it a lot :D

slightly changed to order of it to suit

Code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    With Sh
        With .Range("a1:w1")
            .Value = Array("W/O", "CUSTOMER", "DETAILS", "CUST PART NO", "STATUS", _
                           "NOTES", "DEPARTMENT", "DATE", "CUST ORDER NO", "DEL NO", _
                           "QTY", "SALE PRICE", "CARRIAGE OUT", "TOTAL SALES", _
                           "INT CODE", "SUPPLIER", "COST PRICE", "CARRIAGE IN", _
                           "TOTAL HRS", "LABOUR COST", "TOTAL COST", _
                           "GROSS PROFIT", "GROSS PROFIT")

            .ColumnWidth = Array(9.71, 24.29, 47.71, 16, 13.57, 24.71, 15.43, _
                                 8.57, 16.29, 8.41, 7.71, 12.43, 15, 13.71, 12.14, _
                                 23.29, 13.14, 13.71, 11.29, 11.29, 13, 14.86, 14.86)

            With .Font
                .Size = 8
                .Bold = True
            End With
            With .Range("a:w")
                        .HorizontalAlignment = xlCenter
            End With
        End With
    End With
    
With ActiveWindow
    .SplitColumn = 3
    .SplitRow = 1
    .FreezePanes = True

End With
End Sub
 
Upvote 0
Code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    With Sh
        With .Range("a1:w1")
            .Value = Array("W/O", "CUSTOMER", "DETAILS", "CUST PART NO", "STATUS", _
                           "NOTES", "DEPARTMENT", "DATE", "CUST ORDER NO", "DEL NO", _
                           "QTY", "SALE PRICE", "CARRIAGE OUT", "TOTAL SALES", _
                           "INT CODE", "SUPPLIER", "COST PRICE", "CARRIAGE IN", _
                           "TOTAL HRS", "LABOUR COST", "TOTAL COST", _
                           "GROSS PROFIT", "GROSS PROFIT")

            .ColumnWidth = Array(9.71, 24.29, 47.71, 16, 13.57, 24.71, 15.43, _
                                 8.57, 16.29, 8.41, 7.71, 12.43, 15, 13.71, 12.14, _
                                 23.29, 13.14, 13.71, 11.29, 11.29, 13, 14.86, 14.86)

            .EntireColumn.HorizontalAlignment = xlCenter
            With .Font
                .Size = 8
                .Bold = True
            End With
        End With
        .Range("D2").Select
        ActiveWindow.FreezePanes = True
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top