I apologize in advance if I omit some info..please let me know if I do.
Basically, I have a spreadsheet with a macro that will automatically open a Comment box whenever a cell is populated with any characters. However, the macro uses a SendKeys command, which, from what I gather around the web, can be problematic. And mine is. it runs off the NumLock key when the macro is enabled. Now, overall, it's not a big thing to click the NumLock key to re-activate it, but I was hoping for a solution to this. I've tried a few ways (based on what I found on the web) to re-write this, but no luck. Can some help me with this? Here is the macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Static sName As String
Dim iLen As Long
If Len(sName) = 0 Then sName = Application.UserName & ":"
With Target(1)
If Intersect(.Cells, Range("B1:Q35")) Is Nothing Then Exit Sub
If .HasFormula Then Exit Sub
If .Value = Cells(.Row, "AB").Value Then
If bHasComment(.Cells) Then .Comment.Delete
Else
.Select
If Not bHasComment(.Cells) Then
.AddComment
Else
iLen = Len(.Comment.Shape.TextFrame.Characters.Text)
End If
With .Comment.Shape.TextFrame
.AutoSize = False
.Characters(Start:=iLen + 1).Insert IIf(iLen, vbLf, "") & sName & vbLf
.Characters(Start:=iLen + 1, Length:=Len(sName)).Font.Bold = True
End With
With .Comment
.Visible = True
Application.SendKeys "+{F2}"
.Visible = False
End With
End If
End With
End Sub
Function bHasComment(cell As Range) As Boolean
On Error Resume Next
bHasComment = cell.Comment.Parent.Address = cell.Address
End Function
Any help would be appreciated.
Bookmarks