Solved

Excel VBA - spreadsheet locking every time Sheet_Change event code runs

Posted on 2012-04-11
2
309 Views
Last Modified: 2012-04-11
Hi.
My spreadsheet is locking every time Sheet_Change event code runs when I change a cell
Excel end up failing. The two offending lines are
  Sh.Cells(6, 6) = oCountNew
   Sh.Cells(8, 6) = oCountUsed



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim oCount_New As Integer
    Dim oCount_Used As Integer
   
    If Target.Column = 6 And Sh.Name <> "SUMMARY" Then
     
        oCountUsed = Application.WorksheetFunction.CountIf(Range("F22:F1000"), "U")
        oCountNew = Application.WorksheetFunction.CountIf(Range("F22:F1000"), "N")
        Sh.Cells(6, 6) = oCountNew
        Sh.Cells(8, 6) = oCountUsed
       
    End If
   
End Sub
0
Comment
Question by:murbro
2 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
Comment Utility
Since the code is changing cells it calls itself.

To stop that happening add this at the start,
Application.EnableEvents = False

Open in new window

and this at the end.
Application.EnableEvents = True

Open in new window


It's important that you add that at the end, if you don't no other events will be triggered after the code is finished.
0
 

Author Closing Comment

by:murbro
Comment Utility
Thanks very much
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now