Auto-Center as you go

[paquirl]

Looking for code to add to "This Workbook" module which will auto center text (normal centering horizontally) in every column of every sheet as you input data, so you never have to manually center a column.

 

[burns14hs]

There's probably a more elegant way to do this, and I'm not sure why the action would ever need to be repeated so often but this should accomplish what you're looking for:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Application.ScreenUpdating = False
    
    ActiveWorkbook.Names.Add Name:="MyOrigPlace", RefersTo:=Selection
    
    Cells.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Application.Goto Reference:="MyOrigPlace"
    ActiveWorkbook.Names("MyOrigPlace").Delete
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub

I'm not sure how all those could have failed... In the original one that I posted, you literally cannot input anything without it being horizontally centered automatically. It even centers everything that wasn't centered previously. Any added sheets act the same way, and even a copy/paste realigns to center...

If you have placed the above code in "ThisWorkbook" and have macros activated, there is no reason it would not work

Samesies for pretty much all the other solutions suggested to you as well.

 

[Jaafar Tribak]

Can't you just pre-format the columns instead of using VBA ?