VBA code takes long to excute

[asher_nk]

Hi all,

I am very new to VBA. I would like my code add a comment box to the active cell based on the text in the active cell. As the user inputs code it should add a comment. However when users move down the sheet and populate the rows it seems that the code takes longer to execute. Any help would be much appreciated.

Thanks.

 

[My Aswer Is This]

If you have some Excel Vba code that is running slow then past your code here so we can see it.
And define slow. I have seen some people on the Forum who thinks 1.1 seconds is slow

 

[asher_nk]

Hi of course below is the code. I have coded to a private sub and then calls a module. Thanks for your help.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("AMMS")) Is Nothing Then  
   
  Call popupnotesAMMS
 
ElseIf Not Intersect(Target, Range("ES")) Is Nothing Then
  
  Call popupnotesES
 
End If

End Sub

Sub popupnotesAMMS()

ActiveCell.ClearComments

    If ActiveCell = "text" Then
        ActiveCell.AddComment "text"
    
        ElseIf ActiveCell = "text" Then
        ActiveCell.AddComment "text"
       
        ElseIf ActiveCell = "text" Then
        ActiveCell.AddComment "text"
       
    End If

ActiveCell(1, 3).ClearComments

    If ActiveCell(1, 3) = "text" Then
        ActiveCell(1, 3).AddComment "text"
   
        ElseIf ActiveCell(1, 3) = "text" Then
        ActiveCell(1, 3).AddComment "text"
       
    End If
   
    Dim xcomment As Comment
    For Each xcomment In Application.ActiveSheet.Comments
    xcomment.Shape.TextFrame.AutoSize = True
    Next
 
End Sub

Sub popupnotesES()

ActiveCell.ClearComments

   
 If ActiveCell = "text" Then
        ActiveCell.AddComment "text"
    
        ElseIf ActiveCell = "text" Then
        ActiveCell.AddComment "text"
       
        ElseIf ActiveCell = "text" Then
        ActiveCell.AddComment "text"
       
    End If

ActiveCell(1, 2).ClearComments

    If ActiveCell(1, 2) ="text" Then
        ActiveCell(1, 2).AddComment "text"
   
 
    If ActiveCell(1, 2) ="text" Then
        ActiveCell(1, 2).AddComment "text"
       
    End If

    Dim xcomment As Comment
    For Each xcomment In Application.ActiveSheet.Comments
    xcomment.Shape.TextFrame.AutoSize = True
    Next

End Sub

I have taken out the text for some clarity as it is really wordy.

 

[My Aswer Is This]

Can you please explain in words what your attempting to do.
And you said:
If Not Intersect(Target, Range("AMMS")) Is Nothing Then

There is no Range("AMMS")

 

[My Aswer Is This]

You looking to see if there are comments in column A or which column

 

[asher_nk]

In the worksheet I have named a range as AMMS (its cells C9:C171) by using the name box. I would like for the code to first check
1. If the activecell is in that range, if it is
2. ,then for a note to be added to that activecell based user inputs (from a dropdown box)

The problem is it takes to long for the code to run. I am assuming it recalculates the comments

 

[Michael M]

You do have the appropriate NamedRanges , "AMMS" & "ES" ??

 

[My Aswer Is This]

I will move on. I believe someone else here will provide a answer.

 

[asher_nk]

Hi yes, AMMS is C7:171 and ES is D9:D171

 

[Michael M]

What happens if you use this
AND Can you please use code tags when posting code....so much easier to read AND debug
Simply click on the VBA button on the reply toolbar, and paste you code where the cursor is flashing

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AMMS")) Is Nothing Then
Application.EnableEvents = False
Call popupnotesAMMS
ElseIf Not Intersect(Target, Range("ES")) Is Nothing Then
Call popupnotesES
End If
Application.EnableEvents = True
End Sub