Solved

Worksheet Crashes when Worksheet Change VBA event is run

Posted on 2012-04-12
11
344 Views
Last Modified: 2012-04-13
Option Explicit


Sub WorkshleetChange()
    
    If (ActiveSheet.Cells(8, 6).Value = "SAME SKIDS") Then
        On Error Resume Next
        ActiveSheet.Unprotect "1234"
        Range("F10").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
        On Error GoTo 0
        
        Range("f10").FormulaR1C1 = _
            "=IFERROR(R14C6*R16C6*R18C6*R20C6,""ENTER ALL DIMENSIONS"")"
        
        On Error Resume Next
        Range("F10").Select
        Selection.Locked = True
        Selection.FormulaHidden = True
        ActiveSheet.Protect "1234"
        ActiveSheet.EnableSelection = xlUnlockedCells
        On Error GoTo 0
        
    ElseIf (ActiveSheet.Cells(8, 6).Value = "VARIOUS SKIDS") Then
        Cells(14, 6).Value = ""
        Cells(16, 6).Value = ""
        Cells(18, 6).Value = ""
        Cells(20, 6).Value = ""
        On Error Resume Next
        Range("F10").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
        Range("F10").Value = ""
        On Error GoTo 0
    End If
    
End Sub

Open in new window


Hi,

In the above code

F12:H12 are merged (called cell(8,6) in some places)

The cell F6 or cell (8,6)  has a drop down menu. I want some things to be changed when different drop down options are chosen.

Please note that the worksheet crashed prior to me adding whatever is inside 'On error resume next'

So whenever I change the drop down menu, the worksheet crashes (prior to crashing the function seems to execute currently - then instantly crashes)

Any help on finding a cure is appreciated!
Thanks!
0
Comment
Question by:Shanan212
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37840117
Please state exactly what you mean by "worksheet crashes".
0
 
LVL 81

Expert Comment

by:byundt
ID: 37840468
The reference to ActiveSheet.Cells(8, 6).Value is actually to cell F8, not cell F6. Was that a typo?
0
 
LVL 81

Expert Comment

by:byundt
ID: 37840470
Are you able to post a sanitized version of your workbook? Being able to reproduce the problem is the first step in us being able to help you find a solution.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Author Comment

by:Shanan212
ID: 37840473
Appologies!

Worksheet is at workplace (I am at home now) and it does connect to few important info. I can of course make a simple version but prefer to see if a solution can be found before that.

What I mean it crashes is that "Microsoft Excel has encountered a problem" window pops up (the excel in background white out) and excel restarts.

And yes, its a type for F6
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37841009
Well, your code goes into an infinite loop - that's why the crash.

Your Worksheet_Change code is changing data on the worksheet and nothing is in your code that would change the value in F8, so it just keeps seeing the same "SAME SKIDS" or "VARIOUS SKIDS" and keeps making changes, etc.

Set Application.EnableEvents = FALSE while you make changes, then make sure its set back to TRUE.  If anything can generate an error, you might want to generate an error handler to ensure that events are turned back to TRUE or develop some other approach, because if something messes up you might be wondering why your Worksheet_Change() event is no longer running (that's been a head scratcher for me before, until "duh - events are off" :P

Here's your revised code - I blocked it from the top and back on at the bottom, for simplicity at this point.

PS - there's no real need to select cells in your code (that also triggers potential events, but is also inefficient).  I took a bit of editorial license with your code - hope you don't mind.  Otherwise, just disable events before you make the worksheet change, then re-enable.


Option Explicit
Sub WorksheetChange()
    
    Application.EnableEvents = False
    
    If (ActiveSheet.Cells(8, 6).Value = "SAME SKIDS") Then 'Range("F8")
        On Error Resume Next
        ActiveSheet.Unprotect "1234"
        With Range("F10")
            .Locked = False
            .FormulaHidden = False
        
            On Error GoTo 0
        
            .FormulaR1C1 = _
                "=IFERROR(R14C6*R16C6*R18C6*R20C6,""ENTER ALL DIMENSIONS"")"
            On Error Resume Next
        
            .Select
            .Locked = True
            .FormulaHidden = True
        End With
        
        ActiveSheet.Protect "1234"
        ActiveSheet.EnableSelection = xlUnlockedCells
        On Error GoTo 0
        
    ElseIf (ActiveSheet.Cells(8, 6).Value = "VARIOUS SKIDS") Then 'Range("F8")
        Cells(14, 6).Value = vbNullString
        Cells(16, 6).Value = vbNullString
        Cells(18, 6).Value = vbNullString
        Cells(20, 6).Value = vbNullString
        On Error Resume Next
        With Range("F10")
            .Locked = False
            .FormulaHidden = False
            .Value = vbNullString
        End With
        On Error GoTo 0
    End If
    
    Application.EnableEvents = True
End Sub

Open in new window


Cheers,

Dave
0
 
LVL 13

Author Comment

by:Shanan212
ID: 37841025
Thanks Dave!

I will test it out first thing in the morning and let you know!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37841032
It will work - I tested it.  But, I always suspect infinite loops involving event when crashes are involved and just doing cell manipulation. Perhaps that's because I've generated my share, lol.

Also -

ActiveSheet.Cells(8,6) is equivalent to:

Cells(8,"F") - because the default worksheet prefix is always the active sheet.  However, I like prefixing just about everything as well - that way there's no confusion.

Also equivalent to ActiveSheet.Range("F8") or Range("F8") <- to me, seeing the F8 this is much more easy to read ;)  However, perhaps you use column numbers instead?  

Dave
0
 
LVL 13

Author Comment

by:Shanan212
ID: 37841045
Aah!

I suspected infinite loops as

- I didnt see any errors before crash
- My computer is i7, only way it would crash is infinite loop (previous experience)
- And the code worked as intended just before crashing

Its just I didnt know where to look for that.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37841056
Welcome to the club - may you have enough experience with crashes to then see them all before they happen.  I probably create a crash event at least a few times a week, lol.

A saying I'm fond of misquoting: "People get the experience they need just after they needed it" or something like that.

Dave
0
 
LVL 13

Author Comment

by:Shanan212
ID: 37841060
I get you :)

Good night!
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 37842980
Thanks Dave!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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 …

739 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