Results 1 to 10 of 10

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

This is a discussion on How do you Align text center in a cell using VBA? within the Excel Questions forums, part of the Question Forums category; i've tried a few things but cant quite get my head round it. your help will be much appreciated I ...

  1. #1
    Board Regular
    Join Date
    Dec 2005
    Location
    St.Helens UK
    Posts
    439

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

    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

  2. #2
    Board Regular Lewiy's Avatar
    Join Date
    Jan 2007
    Location
    Hyrule
    Posts
    4,281

    Default

    Try this:
    Code:
    Columns("A:W").HorizontalAlignment = xlCenter
    Give a man a fish, he'll eat for a day.
    Teach a man to fish, he'll eat for a lifetime.
    Give a man religion, he'll die praying for a fish.

  3. #3
    Board Regular
    Join Date
    Nov 2005
    Posts
    255

    Default

    Columns("A:W").HorizontalAlignment = xlCenter

  4. #4
    Board Regular
    Join Date
    Apr 2004
    Posts
    6,818

    Default

    Centre everything in col A:W, or just the titles ?
    Yesterday I felt on top of the world. Today its falling in on me.

  5. #5
    Board Regular
    Join Date
    Dec 2005
    Location
    St.Helens UK
    Posts
    439

    Default

    center everything

  6. #6
    Board Regular
    Join Date
    Dec 2005
    Location
    St.Helens UK
    Posts
    439

    Default

    sussed it, thanks to Lewiy & Big Monkey

    Sh.Columns("A:W").HorizontalAlignment = xlCenter

  7. #7
    Board Regular
    Join Date
    Dec 2005
    Location
    St.Helens UK
    Posts
    439

    Default

    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

  8. #8
    New Member
    Join Date
    Mar 2006
    Posts
    17

    Default

    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

  9. #9
    Board Regular
    Join Date
    Dec 2005
    Location
    St.Helens UK
    Posts
    439

    Default

    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

  10. #10
    New Member
    Join Date
    Mar 2006
    Posts
    17

    Default

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com