Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
2 Questions John Calder New Users to Excel 18 August 24th 06 04:17 AM
code not unique find latest date Barbara Wiseman Excel Discussion (Misc queries) 3 December 11th 05 08:50 AM
VLOOKUP for Zip Code Ranges JerseyJR Excel Worksheet Functions 2 September 6th 05 06:37 PM
Conform a total to a list of results? xmaveric Excel Discussion (Misc queries) 1 August 21st 05 07:22 PM
Macro for changing text to Proper Case JPriest Excel Worksheet Functions 3 August 8th 05 09:31 PM


All times are GMT +1. The time now is 12:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"