Files has been sent your way!

Thanks,

Kevin

"JLatham" wrote:

As long as you absolutely know for certain that what you have in the code is
the same as what's available from the drop-down list(s) then UCase should not
be necessary. I'm just kind of a belt and suspenders man, so I'd put the
UCase() in there - it won't hurt anything, other than taking a few
milliseconds of time to make sure at each encounter of UCase()

There's really no workaround for the password being exposed in the VBA
module other than to protect the module separately, but the danger there is
that if you lose/forget that password, you can't get back into the module at
all, and those passwords are harder to crack than workbook/worksheet
passwords.

Are the cells listed in "D16:G555,D9:D12,J9:J12,L9:L11,P16:P555,L5" that
could be affected by the change of checkmark character all unlocked also?
Any cell that has the potential of being changed by any of the code here has
to either be unlocked or the sheet must be unprotected before the change
takes place.

I really think/thought that Jim Thomlinson was on to something there.

Have you tried commenting out the
On Error GoTo sub_exit
line yet and then tried to get it to fail? If you have, then when it fails
a box will open up offering a chance to their [Debug] or [End]. If you
choose [Debug] it will open up the VB Editor with the line where the error
was thought to have occured (not always absolutely correct, but usually is at
runtime) at will be highlighted. That should be a big clue.

I suppose as a last resort you could send the .xls file to me as an
attachment to an email to HelpFrom @ jlathamsite.com (remove the spaces). I'd
look it over and upon discovering the problem I'd fix and return to you AND
post solution here in this thread so that the knowledge won't be lost.

You don't even have to unprotect the sheet - I can get it open in about 2
seconds: passwords on worksheets are easy to crack <g. Remember that
Protecting worksheets is primarily just to preserve structure and content,
not to actually provide "security" in the usual sense.


"kmwhitt" wrote:

Hi J:

In the cells C9 thru C12 - I have drop-down boxes all dependent upon the
preceding selection. When I change C9, the text in C10 thru C12 show
erroneous data so I am clearing these cells when an event occurs within C9.
Is there a better way to do it?

As far as UCase goes, the values are always going to be the same since the
user must select from a pre-defined drop-down list. Is it OK to remove UCase
under these circumstances? Is there a better way?

I really appreciate all the time you have spent helping me. If I can get
these little glitches taken care of, I am going to have one great pricing
utility....

Thanks again,

Kevin

"JLatham" wrote:

Also, I'm confused about the _Change() event code - it seems to be doing
parts of what the _BeforeDoubleClick() code is also doing, just based on any
change taking place in C9 or C10 (and C9 change can cause a change to C10,
which means the routine is going to enter it twice at those times).

I would also rewrite the _Change() code to look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

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

If Not Intersect(Target, Me.Range("C10")) Is Nothing Then
Application.EnableEvents = False
Me.Range("C12").Value = ""
End If
Application.EnableEvents = True
End Sub
Exactly what is supposed to be happening on this sheet when??


Another thing - you dropped the UCase() instruction after the section
handling MDF - VBA is case sensitive, "Glaze" < "GLAZE" < "glaze" <
"GLaZe" in VBA.




"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