Msgbox Code Help Please....

[Guest]

When a user double clicks on cell F6 one of two things can happen based upon
whether or not the text in cell C5 contains the string "MDF":

1) If "MDF" is found within C5, a check mark is placed in the cell. I
already have code set up to place a Marlette check box in several different
cells including this one.

2) If "MDF" is not found within C5, an error message pops up and states",
This option is not availabe for (actual contents of C5). Please change the
doorstyle to MDF Painted to proceed.

Would someone please help me write this code?

Thanks,

Kevin

 

[Guest]

BTW -

Here is the code currently being used to place the check mark:

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

Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("D12:G549,F5:F8")) Is Nothing Then
With Target
If .Value = "a" Then
..Value = ""
Else
..Value = "a"
..Font.Name = "Marlett"
End If
End With
End If

sub_exit:
Application.EnableEvents = True
End Sub

I don't know if what I want to do has to be tied in with this......

Thanks again,

Kevin

 

[Guest]

Yes, that code most definitely has something to do with it. Without changing
it, it always places a checkmark in F6 when you double-click there.

Here's my modification of it , as a complete replacement.

What I've done is add a second check to see if the contents of C5 contains
"MDF" (uppercase) anywhere in it at all. If it does NOT then the message
pops up AND then checkmark in F6 is erased. But if "MDF..." was found in C5,
nothing really happens (checkmark remains).

Finally, I added a Cancel=True statement to bounce you out of those cells
when you double-click on them rather than leaving you in them in edit mode.
Just delete that line if you really want to stay in there.

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

Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("D12:G549,F5:F8")) 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("F6")) Is Nothing Then
If InStr(UCase(Range("C5")), "MDF") = 0 Then
Target.Value = "" ' erase check mark?
MsgBox "This option is not availabe for " & _
Range("C5") & ". Please change the doorstyle " & _
"to MDF Painted to proceed."
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

 

[Guest]

You are a GENIUS!!! I can't thank you enough! Works 100%....

Thanks,

Kevin

 

[Guest]

Glad to hear that it actually did what you needed to be done.

Enjoy!

 

[Guest]

JLatham:

Hello again. Your code was really awesome, but now that I have protected my
worksheet this is not working:

' remove if you want to remain in the
' double-clicked cell
Cancel = True
End If

Any ideas for me? Perhaps the problem is that I have chopped the code up
too much based upon the msgbox code you gave me. Here is the code in
entirety. I would appreciate it if you would check it out for me.

Thanks again,

Kevin

Code in Entirety:

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