Excel VBA toggle

  • Thread starter Thread starter mark1
  • Start date Start date
M

mark1

I'm trying to make a merge cell toggle in VBA. So far I
have:
Selection.MergeCells = Not Selection.MergeCells
Selection.WrapText = Not Selection.WrapText
Selection.HorizontalAlignment = xlCenter

The Not statement is used for the first two toggle
commands, but I don't know how to do the last one
(xlCenter). How do I make the toggle for that one so that
it goes back to left align? Thanks!!
 
Mark,

No straight shift, but you could use

With Selection
.MergeCells = Not .MergeCells
.WrapText = Not .WrapText
If .HorizontalAlignment = xlCenter Then
.HorizontalAlignment = xlLeft
Else
.HorizontalAlignment = xlCenter
End If
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sometimes, there's more than one cell selected.

You could either amend Bob's code to just work with the first cell of the
selection (or even activecell) or use the first cell to dictate to the others in
the selected area.


Option Explicit
Sub testme1()
With ActiveCell
.MergeCells = Not .MergeCells
.WrapText = Not .WrapText
If .HorizontalAlignment = xlCenter Then
.HorizontalAlignment = xlLeft
Else
.HorizontalAlignment = xlCenter
End If
End With


Sub testme2()
With Selection
.MergeCells = Not .Cells(1).MergeCells
.WrapText = Not .Cells(1).WrapText
If .Cells(1).HorizontalAlignment = xlCenter Then
.HorizontalAlignment = xlLeft
Else
.HorizontalAlignment = xlCenter
End If
End With
End Sub

But be careful with that last one--you may merge a bunch of cells that you
didn't mean to.
 
Just one way...

Sub Demo()
With Selection
.HorizontalAlignment = -.HorizontalAlignment - 8239
End With
End Sub


Sub ForReference()
Dim n
n = xlCenter + xlLeft

With Selection
.HorizontalAlignment = n - .HorizontalAlignment
End With
End Sub
 
Back
Top