Auto-Center as you go

paquirl

Board Regular
Joined
Oct 12, 2010
Messages
226
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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:

Code:
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
 
Upvote 0
its useful initially to input data and see if it aligns left or right (text or number), and when happy from testing just set the columns once to centre. pasting into it will often change the default
Code:
Private Sub Worksheet_Activate()
    Cells.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub
 
Upvote 0
Try placing the following sub into ThisWorkbook code section:
Code:
Sub Workbook_SheetActivate(ByVal ws As Object)
    ws.Cells.HorizontalAlignment = xlCenter
End Sub
 
Upvote 0
All 3 of these ideas failed testing. :(

and yes, pasting from other workbooks is involved, as well as new data entry
 
Last edited:
Upvote 0
Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
In the upper left corner of the screen double click on "ThisWorkbook"
Paste the below code in the VBA edit window

And now all sheet will be set to center all text.

If you paste in data you will have to reactivate the sheet to set pasted values to centered.

Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Cells.HorizontalAlignment = xlCenter
End Sub
 
Last edited:
Upvote 0
you have a greater knowledge of what occurred than we can ever have, mine needs to be on the worksheet code (not thisworkbook) section, then you need to select another sheet and back to the one with the code so that WORKSHEET ACTIVATE can actually occur. Also Marcos MUST be ENABLED. Does that make sense
 
Upvote 0
Tell us what you mean all this failed. You should consider maybe your doing something wrong.
All 3 of these ideas failed testing. :(

and yes, pasting from other workbooks is involved, as well as new data entry
 
Upvote 0
All 3 of these ideas failed testing. :(

and yes, pasting from other workbooks is involved, as well as new data entry

Pasting can bring across other formatting which could destroy you centring plan, hence corrective sheet changes all the time. Often it changes the eligible font/size as well
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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