horizontal alignment

**redtwo** · 06-07-2010, 01:59 PM

I had a macro in excel 2003 which rotated horizontal alignment of the selection.

Sub text_align()  'assign macro to Ctrl+q
    With Selection
        If .HorizontalAlignment = xlLeft Then
            .HorizontalAlignment = xlCenter
        ElseIf .HorizontalAlignment = xlCenter Then
            .HorizontalAlignment = xlRight
        ElseIf .HorizontalAlignment = xlRight Then
            .HorizontalAlignment = xlLeft
        End If
    End With
End Sub

However, in 2007 something changed. Now it seems there is a NULL problem and I can not get into the loop. I changed to this:

Sub text_align()  'assign macro to Ctrl+q
    With Selection
        If .HorizontalAlignment = xlLeft Or IsNull(.HorizontalAlignment) Then
            .HorizontalAlignment = xlCenter
        ElseIf .HorizontalAlignment = xlCenter Then
            .HorizontalAlignment = xlRight
        ElseIf .HorizontalAlignment = xlRight Then
            .HorizontalAlignment = xlLeft
        End If
    End With
End Sub

and now it only rotates between center and right (i never get left align). I use this for entire column selections (with headers) and with multiple range selections. Does anyone have any ideas. Does excel 2007 now see a selection range differently?

Thanks in advance
Heather

**DonkeyOte** · 06-07-2010, 02:24 PM

Perhaps try:

Sub text_align()
With Selection
    Select Case .HorizontalAlignment
        Case -4108
            'center to right
            .HorizontalAlignment = -4152
        Case -4152
            'right to left
            .HorizontalAlignment = -4131
        Case Else
            .HorizontalAlignment = -4108
    End Select
End With
End Sub

though you might want to base the test on the first cell in the selection:

.Cells(1).HorizontalAlignment

**redtwo** · 06-07-2010, 02:43 PM

Sorry, I forgot to mention that I had tried cells(1) and this gets stuck in the center loop. Your version works more often than mine does. I think it may have to do with things being auto filtered but I can not seem to find the trend. I will use your version for now but it does not always work.

Thanks
Heather

**DonkeyOte** · 06-07-2010, 02:45 PM

If you need further assistance specific to functionality already in place you will be best served posting a sample file that illustrates the problem in action.

(to attach a file click the paperclip icon in the reply window - if you can't see it click GoAdvanced)