+ Reply to Thread
Results 1 to 4 of 4

Worksheet events going crazy

Hybrid View

jbr863 Worksheet events going crazy 10-09-2016, 10:21 AM
TMS Re: Worksheet events going... 10-09-2016, 11:18 AM
AlphaFrog Re: Worksheet events going... 10-09-2016, 11:20 AM
jbr863 Re: Worksheet events going... 10-09-2016, 11:01 PM
  1. #1
    Registered User
    Join Date
    10-02-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Worksheet events going crazy

    Hi team,
    Hoping you can help. When I open a new workbook, add the word "Bob:" in a random cell - for testing purposes - and run the following code...
    Private Sub Worksheet_Change(ByVal Target As Range)
        Sheet1.Cells.Find("Bob:").Activate
    End Sub
    ...things work fine. However, when I amend the code with one additional line...
    Private Sub Worksheet_Change(ByVal Target As Range)
        Sheet1.Cells.Find("Bob:").Activate
        Sheet1.Range("D2").Value = Sheet1.Range("D2").Value + 100
    End Sub
    ...not only do I get a runtime error 1004 that highlights "Sheet1.Cells.Find("Bob:").Activate", but the black, rectangular cursor disappears from the worksheet view. I can still type into cells, but I can't see the cell that's been selected until I start typing and after I hit "Enter", the entered text appears in a different cell than the one into which I appeared to be typing.

    To confirm, the event fires (Application.EnableEvents = True) but the execution breaks when basic operations are included. I've noted this problem in Excel 2013 32-bit (version 15.0.4859.1000) and Excel 2010 32-bit (version 14.0.7173.5000). This wasn't a problem 2 months ago but I couldn't identify any particular Windows Update for Office that might be the culprit. Any help would be appreciated!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,131

    Re: Worksheet events going crazy

    You're making a change to the sheet within the Worksheet Change Event handler. So it will loop. Switch off event handling before making the changes.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,652

    Re: Worksheet events going crazy

    When the code adds 100 to D2, that causes the WorksheetChange event to trigger again. Then it adds 100 again to D2 and triggers itself yet again.... creating a loop.

    This suspends the events, adds 100 to D2, then re-enables events. The procedure should not trigger itself.

    Private Sub Worksheet_Change(ByVal Target As Range)
        Sheet1.Cells.Find("Bob:").Activate
        Application.EnableEvents = False
        Sheet1.Range("D2").Value = Sheet1.Range("D2").Value + 100
        Application.EnableEvents = True
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    10-02-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Worksheet events going crazy

    Bonehead simple but I missed it. This one is solved. Thanks guys!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Automate Worksheet with Worksheet Events
    By davesexcel in forum Tips and Tutorials
    Replies: 0
    Last Post: 04-19-2015, 10:10 AM
  2. [SOLVED] Worksheet Events
    By werko in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-17-2014, 06:12 AM
  3. Worksheet Change and Worksheet Activate Events Reprotecting Automatically
    By excelnewb02 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-21-2012, 08:44 PM
  4. MAcro to Copy Worksheet and Worksheet Events
    By max57 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-28-2009, 08:12 PM
  5. Worksheet Codename - Crazy Error!
    By EphesiansSix in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-23-2006, 08:55 PM
  6. Worksheet Events
    By DCSwearingen in forum Excel General
    Replies: 2
    Last Post: 05-24-2006, 05:42 PM
  7. worksheet events and a little more help
    By tinkerbellsmyhoe in forum Excel General
    Replies: 5
    Last Post: 04-27-2006, 10:50 AM

Tags for this Thread

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