Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button to Swap Cells
I have a button that has the following VB connected with it. The function
will only swap the values of two cells and not the formats. I would like the formats to be swapped as well. Any ideas? Private Sub CommandButton1_Click() Dim vTemp As Variant With Selection If .Count < 2 Then MsgBox "2 cells only." Else If .Areas.Count = 2 Then vTemp = .Areas(1).Cells.Value .Areas(1).Cells.Value = .Areas(2).Cells.Value .Areas(2).Cells.Value = vTemp Else vTemp = .Cells(1).Value .Cells(1).Value = .Cells(2).Value .Cells(2).Value = vTemp End If End If End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button to Swap Cells
How about:
Sub swap_um() Set r3 = Range("Z100") i = 1 For Each r In Selection If i = 1 Then Set r1 = r If i = 2 Then Set r2 = r If i = 3 Then Exit For i = i + 1 Next r1.Copy r3 r2.Copy r1 r3.Copy r2 End Sub this uses Z100 as a scratchpad. -- Gary''s Student - gsnu200782 "Prohock" wrote: I have a button that has the following VB connected with it. The function will only swap the values of two cells and not the formats. I would like the formats to be swapped as well. Any ideas? Private Sub CommandButton1_Click() Dim vTemp As Variant With Selection If .Count < 2 Then MsgBox "2 cells only." Else If .Areas.Count = 2 Then vTemp = .Areas(1).Cells.Value .Areas(1).Cells.Value = .Areas(2).Cells.Value .Areas(2).Cells.Value = vTemp Else vTemp = .Cells(1).Value .Cells(1).Value = .Cells(2).Value .Cells(2).Value = vTemp End If End If End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button to Swap Cells
Thanks Gary, it works perfect!
"Gary''s Student" wrote: How about: Sub swap_um() Set r3 = Range("Z100") i = 1 For Each r In Selection If i = 1 Then Set r1 = r If i = 2 Then Set r2 = r If i = 3 Then Exit For i = i + 1 Next r1.Copy r3 r2.Copy r1 r3.Copy r2 End Sub this uses Z100 as a scratchpad. -- Gary''s Student - gsnu200782 "Prohock" wrote: I have a button that has the following VB connected with it. The function will only swap the values of two cells and not the formats. I would like the formats to be swapped as well. Any ideas? Private Sub CommandButton1_Click() Dim vTemp As Variant With Selection If .Count < 2 Then MsgBox "2 cells only." Else If .Areas.Count = 2 Then vTemp = .Areas(1).Cells.Value .Areas(1).Cells.Value = .Areas(2).Cells.Value .Areas(2).Cells.Value = vTemp Else vTemp = .Cells(1).Value .Cells(1).Value = .Cells(2).Value .Cells(2).Value = vTemp End If End If End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button to Swap Cells
Here's a version that combines both concepts and cleans up after itself.
Sub SwapCells() Set r3 = Range("T3") ' Or whereever else isn't being used i = 1 With Selection If .Count < 2 Then MsgBox "2 cells only." Else For Each r In Selection If i = 1 Then Set r1 = r If i = 2 Then Set r2 = r If i = 3 Then Exit For i = i + 1 Next r1.Copy r3 r2.Copy r1 r3.Copy r2 r3.Clear End If End With End Sub "Prohock" wrote: Thanks Gary, it works perfect! "Gary''s Student" wrote: How about: Sub swap_um() Set r3 = Range("Z100") i = 1 For Each r In Selection If i = 1 Then Set r1 = r If i = 2 Then Set r2 = r If i = 3 Then Exit For i = i + 1 Next r1.Copy r3 r2.Copy r1 r3.Copy r2 End Sub this uses Z100 as a scratchpad. -- Gary''s Student - gsnu200782 "Prohock" wrote: I have a button that has the following VB connected with it. The function will only swap the values of two cells and not the formats. I would like the formats to be swapped as well. Any ideas? Private Sub CommandButton1_Click() Dim vTemp As Variant With Selection If .Count < 2 Then MsgBox "2 cells only." Else If .Areas.Count = 2 Then vTemp = .Areas(1).Cells.Value .Areas(1).Cells.Value = .Areas(2).Cells.Value .Areas(2).Cells.Value = vTemp Else vTemp = .Cells(1).Value .Cells(1).Value = .Cells(2).Value .Cells(2).Value = vTemp End If End If End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
print 20 cells with a command button | Excel Discussion (Misc queries) | |||
How can I add values in 2 cells using a command button | Excel Programming | |||
sum cells using command button ADD | Excel Programming | |||
Using Command Button to copy cells | Excel Discussion (Misc queries) | |||
Non Working Cells after command button | Excel Programming |