+ Reply to Thread
Results 1 to 6 of 6

Worksheet_Change - Error with nested If-then inside Case

  1. #1
    Registered User
    Join Date
    09-14-2010
    Location
    Park City, UT
    MS-Off Ver
    2010
    Posts
    19

    Worksheet_Change - Error with nested If-then inside Case

    Hello. I have an issue where Excel 2007 is crashing while running the following code. Here is what I ultimately want to achieve:

    In cell C18, I have a drop-down list with 4 values (5 if you include the default blank). Once the user selects from that list, I want VB code to analyze each cell in range H4:H33. If any of those cells are blank, then make the cell next to it blank else make it "no".

    Simply put, any time a user changes the value in C18, it should run the code once to make a select group of cells either "no" or blank.

    Please Login or Register  to view this content.
    Last edited by m1notaur; 09-24-2010 at 10:46 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Worksheet_Change - Error with nested If-then inside Case

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here


    You need to disable events as your function is altering cells which will cause the event to fire again.

    Also you jump out of the event on the Else case without turning screen updating back on.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    09-14-2010
    Location
    Park City, UT
    MS-Off Ver
    2010
    Posts
    19

    Re: Worksheet_Change - Error with nested If-then inside Case

    Thanks, Andy - new to this.

  4. #4
    Registered User
    Join Date
    09-14-2010
    Location
    Park City, UT
    MS-Off Ver
    2010
    Posts
    19

    Re: Worksheet_Change - Error with nested If-then inside Case

    So I did some more revisions and I most definitely have determined that the error is directly related to "writing" new information in the cells (ie: setting their value to either "no" or "")

    Here is an update to the code. Take note that in the function, "UpdateYesNoCells()", I have two changes to the cells that do not impact their Value... this code works just fine. The issue, once again, is that I want to write either "no" or leave it blank (bold below):

    Please Login or Register  to view this content.
    Last edited by m1notaur; 09-23-2010 at 12:14 PM.

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Worksheet_Change - Error with nested If-then inside Case

    You did not add the code I suggested to stop the event from firing when changing the Value property in the routine.

    Please Login or Register  to view this content.
    Also you know that in the change event the active cell will not be the cell that has just changed. Because in order to change you need to exit the cell and when you exit the cell it is no longer the active cell.

  6. #6
    Registered User
    Join Date
    09-14-2010
    Location
    Park City, UT
    MS-Off Ver
    2010
    Posts
    19

    Re: Worksheet_Change - Error with nested If-then inside Case

    Andy,

    Thank you very much for your help. I knew it was going to be as simple as you described: Application.EnableEvents... I have been looking for that since I started coding VB a few weeks ago.

    RE: ActiveCell issue when changing to another cell to write code - yeah... thank you - figured this out and changed up my code to ensure this wouldn't be an issue.

    Once again, thank you very much.

    -Tom

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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