Code Woes

[Guest]

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,D912,J9:J12,L9:L11,P16555,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

 

[Guest]

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.

 

[Guest]

Why not just unprotect the sheet at the beginning of the code and reprotect
at the end or just specify the UserInterfaceOnly:=True (depending on your
version of Excel). The issue you are describing is that you are trying to
manipulate a protected sheet. This invokes the error handler and skips you
right out tof the procedure...

Acitvesheet.protect UserInterfaceOnly:=True

 

[Guest]

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.

 

[Guest]

Why not just unprotect the sheet at the beginning of the code and reprotect
at the end or just specify the UserInterfaceOnly:=True (depending on your
version of Excel). The issue you are describing is that you are trying to
manipulate a protected sheet. This invokes the error handler and skips you
right out tof the procedure...

Acitvesheet.protect UserInterfaceOnly:=True

 

[Guest]

Very good catch! For some reason I'd read it backwards. Yes, either
unprotect it and reprotect later, either that or unlock all cells in both
sections of code that might change. But that probably defeats your purpose.

While locked cells that are on a protected sheet can be changed by a formula
in them, they cannot be changed from code or by direct operator action.

What's happening is that your On Error GoTo sub_exit statement is causing
the 'early out' of the routine when changes are attempted to locked cells
while the sheet is protected.

If your sheet has no password, these lines of code will unprotect and then
re-protect the sheet:
(unprotect)
ActiveSheet.Unprotect
(back in to protected mode)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

if you are using a password, then you will have to provide it exactly as
entered:

ActiveSheet.Unprotect Password:="myPassword"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
Password:="myPassword"

 

[Guest]

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

 

[Guest]

Jim, Thanks for the suggestion! I am a complete novice - I just started
trying to code in Excel about 4 days ago. I am running Excel 2003. Since I
am such a beginner, would you give me specifics on how to accomplish what you
suggest?
(i.e. - how to unprotect/protect the sheet using code).....

Thanks again,

Kevin

 

[Guest]

Thank you, J......

The cells that are being changed are unlocked during worksheet protection
and I still get errors.

The worksheet is protected. If I enter the code to unprotect a passworded
worksheet (mine is) doesn't that mean someone viewing the code is able to
open the code and view the password? Is there a workaround?

Thanks yet again!

Kevin

 

[Guest]

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,D912,J9:J12,L9:L11,P16555,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.

 

[Guest]

Files has been sent your way!

Thanks,

Kevin

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,D912,J9:J12,L9:L11,P16555,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.

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