Results 1 to 3 of 3

Help regarding SendKeys command in Macro turning off NumLock key

Threaded View

  1. #1
    Registered User
    Join Date
    07-14-2011
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Help regarding SendKeys command in Macro turning off NumLock key

    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.
    Last edited by morant05; 07-14-2011 at 12:19 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1