vba script to align text to the center

olybobo · #1 05-19-2016, 04:40 PM

ello experts;

Please help. I have googled this and tried so many solutions. I am using excel 2010. I am not sure whether this is a bug or not. Can someone please see my code below. I would like to write a vba code that gets ran after a pivot table refresh. the code is meant to center the text in column e. see my code below. it is not centering the text in column e and i do not know why.

Sub req_alignment()

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myRange As Range
Dim cellContent As String
Dim cellLen As Integer

Set myRange = Range("E:E")
If Not Intersect(Target, myRange) Is Nothing Then
cellContent = Target
cellLen = Len(cellContent)
If (cellLen < 5) Then
Target.HorizontalAlignment = xlCenter
Else
Target.HorizontalAlignment = xlLeft
End If
End If

End Sub

Philb1 · #2 05-26-2016, 01:09 AM

You should turn off enable events because unless the code causes it, it prevents the macro from getting stuck in a infinite loop and as it's an event change macro, it prevents the change from being picked up when the macro has made any change to the worksheet causing it to run again.. I removed the cellcontent variable, it's not needed and added error control to ensure enable events is always switched back on.
Otherwise it works as it should on my computer
Cheers

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myRange As Range
Dim cellLen As Long

On Error GoTo TheExit ' Error control

Set myRange = Range("E:E")

    If Not Intersect(Target, myRange) Is Nothing Then
    
            Application.EnableEvents = False ' Prevents inadvertent looping etc
            cellLen = Len(Target)
        
            If (cellLen < 5) Then
                Target.HorizontalAlignment = xlCenter
            Else
                Target.HorizontalAlignment = xlLeft
            End If
            
        GoTo TheExit
        
    Else
    
        GoTo TheExit
    
    End If

TheExit:
Application.EnableEvents = True
Exit Sub

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Justify left center and right center on same line	DiGug	Word	3	10-02-2014 02:43 PM
Unable to vertically center align texts in table cells?	tinfanide	Word	3	11-24-2013 06:37 AM
How to align And center the content of the column in a table?	Jamal NUMAN	Word	1	05-02-2011 05:47 PM
Macro that will go to line # and center text	marge	Word VBA	0	09-10-2010 12:30 PM
how to center text in multiple columns	galiwock	Excel	1	05-12-2010 09:02 AM