Thread: Code Woes
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
kmwhitt kmwhitt is offline
external usenet poster
 
Posts: 36
Default Code Woes

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