This looks familiar! Although it seems to have grown some?

I'd move a couple of things around just for starters:
I'd put my Application.EnableEvents = False statement right after the first
IF statement, and I'd probably immediately follow it with the Cancel = True
Statement.

But in reality those shouldn't make any difference since I don't see any way
to jump out of the routine before the Sub_Exit: line anyhow.

But to get a handle on what might be going wrong, start by making the
On Error GoTo sub_exit
statement just a comment by placing a ' in front of it as
'On Error GoTo sub_exit

This way if there's an error in the code, you'll see it as you start testing
by double-clicking in various areas.

Also, if during a session, you've ever encountered an error during the code
execution or interrupted it, then because you've given it the
Application.EnableEvents = False
statement, double-clicks and change events won't be recognized until they
are re-enabled. You can do that by typing
Application.EnableEvents = True [Enter] in the immediate window.
(once it's in there you can just put cursor at the end of it and press
[Enter] again to reenable events at any time.

I'm not sure how the _Change() code is interacting with the
_BeforeDoubleClick code, but at first glance, I don't see why it should be.


"kmwhitt" wrote:

After protecting my worksheet, this code only works sometime - that is the
conditional statements. Also, Cancel=True does not seem to be working as the
cursor remains in the field after double clicking the selection. Here's the
code in its entirety: (It works fine when I UNprotect the worksheet).
Please advise. Thanks!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("D16:G555,D9:D12,J9:J12,L9:L11,P16:P555,L5") )
Is Nothing Then
With Target
If .Value = "a" Then
.Value = ""
Else
.Value = "a"
.Font.Name = "Marlett"
End If
End With

'new code to add
If Not Intersect(Target, Range("D11")) Is Nothing Then
If InStr(UCase(Range("C9")), "MDF") = 0 Then
Target.Value = "" ' erase check mark?
MsgBox "This option is not availabe for '" & _
Range("C9") & "'. Please change the doorstyle " & _
"to 'MDF Painted' to proceed."
End If
End If

If Not Intersect(Target, Range("D10")) Is Nothing Then
If InStr(Range("C12"), "Glaze") < 0 Then
Target.Value = "" ' erase check mark?
MsgBox "It is not necessary to choose 'With Glaze' " & _
"when pricing a Biltmore Finish. Please change your finish " & _
"selection to 'Paint Only'."
End If
End If

If Not Intersect(Target, Range("L9")) Is Nothing Then
If InStr(Range("C11"), "Flat/Flat") = 0 Then
Target.Value = "" ' erase check mark?
MsgBox "This option is not availabe for '" & _
Range("C9") & "' in a '" & _
Range("C11") & "' panel configuration. " & _
"Please select a 'Flat/Flat' panel configuration to proceed."
End If
End If

If Not Intersect(Target, Range("L9")) Is Nothing Then
If InStr(Range("C9"), "Bombay") < 0 Then
Target.Value = "" ' erase check mark?
MsgBox "This option is not availabe for '" & _
Range("C9") & "'. Please select an appropriate doorstyle to proceed."
End If
End If

If Not Intersect(Target, Range("L10")) Is Nothing Then
If InStr(Range("C10"), "Stain") < 0 Then
Target.Value = "" ' erase check mark?
MsgBox "The natural maple melamine interior and exterior is already
included with your species selection of '" & _
Range("C10") & "'."
End If
End If
' remove if you want to remain in the
' double-clicked cell
Cancel = True
End If

sub_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Me.Range("C9")) Is Nothing Then
Me.Range("C10,C11,C12").Value = ""
End If

If Not Intersect(Target, Me.Range("C10")) Is Nothing Then
Me.Range("C12").Value = ""
End If
End Sub