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

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

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

  1. #1
    Board Regular
    Join Date
    Dec 2005
    Location
    St.Helens UK
    Posts
    439
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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,282
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular
    Join Date
    Apr 2004
    Posts
    7,777
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    center everything

  6. #6
    Board Regular
    Join Date
    Dec 2005
    Location
    St.Helens UK
    Posts
    439
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

User Tag List

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