Copying comments along with information in Cell 2

[kmvargo]

I have a spreadsheet that I use to track personel changes in my office. I use a macro that swaps information in 2 different cells that are selected. I would also like it to swap the cells comments when I use this macro. Can anyone modify this macro to do this?

Here is the macro -

Sub SwapSelections()
Dim rCell1 As Range
Dim rCell2 As Range
Dim strg1 As String, strg2 As String

If Selection.Cells.Count > 2 Or Selection.Cells.Count < 2 Then
MsgBox "Your selection should only contain 2 cells", vbCritical
End
End If


If Selection.Areas.Count > 1 Then
Set rCell1 = Selection.Areas(1).Cells(1, 1)
Set rCell2 = Selection.Areas(2).Cells(1, 1)

ElseIf Selection.Rows.Count > Selection.Columns.Count Then
Set rCell1 = Selection.Range("A1")
Set rCell2 = Selection.Range("A2")
Else
Set rCell1 = Selection.Range("A1")
Set rCell2 = Selection.Range("B1")
End If

With rCell1
strg1 = .Value
inCl1 = .Interior.Color
End With
With rCell2
strg2 = .Value
inCl2 = .Interior.Color
End With

With rCell1
.Value = strg2
.Interior.Color = inCl2
End With
With rCell2
.Value = strg1
.Interior.Color = inCl1
End With
End Sub

----------------------------------------------------------

Thanks!

 

[GlennUK]

You would be better off posting this in the VBA Visual Basic For Applications (Microsoft) forum than here.

The attributes you are looking for are:

.Comment.Text



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Loomah]

Sub SwapSelections()
Dim rCell1 As Range
Dim rCell2 As Range
Dim strg1 As String, strg2 As String
[b]Dim sCom1 As String, sCom2 As String[/b]
Dim inCl1, inCl2

If Selection.Cells.Count > 2 Or Selection.Cells.Count < 2 Then
 MsgBox "Your selection should only contain 2 cells", vbCritical
 End
End If


    If Selection.Areas.Count > 1 Then
           Set rCell1 = Selection.Areas(1).Cells(1, 1)
           Set rCell2 = Selection.Areas(2).Cells(1, 1)
           
    ElseIf Selection.Rows.Count > Selection.Columns.Count Then
           Set rCell1 = Selection.Range("A1")
           Set rCell2 = Selection.Range("A2")
    Else
           Set rCell1 = Selection.Range("A1")
           Set rCell2 = Selection.Range("B1")
    End If

[b]On Error Resume Next[/b]
    With rCell1
      [b]sCom1 = .Comment.Text
      .Comment.Delete[/b]
      strg1 = .Value
      inCl1 = .Interior.Color
    End With
    With rCell2
      [b]sCom2 = .Comment.Text
      .Comment.Delete[/b]
      strg2 = .Value
      inCl2 = .Interior.Color
    End With
    
    With rCell1
      [b].AddComment sCom2[/b]
      .Value = strg2
      .Interior.Color = inCl2
    End With
    With rCell2
      [b].AddComment sCom1[/b]
      .Value = strg1
      .Interior.Color = inCl1
    End With
End Sub

Is one way, but Glenn is right.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[kmvargo]

Loomah,
Thanks for the code. It does what I neeed it to do.

Also, I will follow your's & Glenn's advise & post in the VB section next time. (Didn't think of that to begin with and even though I know a bit about Excel, the VB people intimidate me! )

Thanks again.

 

[kmvargo]

Also after posting this, I didn't want to cross-post.

 

[GlennUK]

Sure thing. I wanted to get the message across for next time, rather than have you cross-post. But if you had have got stuck with the macro, you might have needed the VBA peoples help.

Luckily Loomah has done it all for you



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Loomah]

kmvargo
That's fair enough re cross posting. I don't think it's the end of the world posting a VBA question in this forum but there is a specific forum for such questions.

As for us being intimidating there, most of the top contributers in the VBA forum are also top MVPs in this forum. There might be some exceptions but most of us don't bite (much)!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[TonyJollans]

Don't bite??, Don't bite!! Of course we don't bite!!






Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[kmvargo]

GlennUK,
Did cross my mind to go to the VB section for this but glad you pointed me there. There is alot of useful info there that I might have missed otherwise. Thanks for the heads up.

Loomah,
Thanks for the code. After looking at it and comparing it to the original it made me look into adding some other features I wanted. I added some really basic tweaks but atleast it is a start on using VB. I'm not as intimidated as I was earlier and I haven't even gotten bitten (TonyJollans) yet! :-D