Macro or code to insert Shape (Say Smiley face) in XL?

[Giles]

Want to insert smiley face shape when long macro is complete. Apparently XL
2007 won't allow one to record this. Help appreciated, but I am a novice with
VBA nothing too complicated please. Thanks

 

[Chip Pearson]

A simple way is to use the character "J" in the WingDings font. Below,
change

Set R = Range("C3")
to set R to the desired cell.


Sub AAA()
Dim R As Range
Dim S As String
Dim N As Long

Set R = Range("C3") '<<<<<<<<< CHANGE AS NEEDED
If R.HasFormula = True Then
Exit Sub
End If
If R.Cells.Count > 1 Then
Exit Sub
End If
S = R.Text
N = Len(R.Value)
If StrComp(R.Characters(N, 1).Font.Name, "WingDings", vbTextCompare) = 0
Then
If Asc(Right(S, 1)) = 74 Then
' already have a smiiley
Exit Sub
End If
End If
S = S & " " & Chr(74)
R.Value = S
R.Characters(N + 2, 1).Font.Name = "WingDings"
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)