Shanan212
asked on
Worksheet Crashes when Worksheet Change VBA event is run
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
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!
Please state exactly what you mean by "worksheet crashes".
The reference to ActiveSheet.Cells(8, 6).Value is actually to cell F8, not cell F6. Was that a typo?
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Dave!
I will test it out first thing in the morning and let you know!
I will test it out first thing in the morning and let you know!
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
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
ASKER
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.
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.
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
A saying I'm fond of misquoting: "People get the experience they need just after they needed it" or something like that.
Dave
ASKER
I get you :)
Good night!
Good night!
ASKER
Thanks Dave!