Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Msgbox Code Help Please....
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Msgbox Code Help Please....
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 "kmwhitt" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Msgbox Code Help Please....
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 "kmwhitt" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Msgbox Code Help Please....
You are a GENIUS!!! I can't thank you enough! Works 100%....
Thanks, Kevin "JLatham" wrote: 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 "kmwhitt" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Msgbox Code Help Please....
Glad to hear that it actually did what you needed to be done.
Enjoy! "kmwhitt" wrote: You are a GENIUS!!! I can't thank you enough! Works 100%.... Thanks, Kevin "JLatham" wrote: 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 "kmwhitt" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Msgbox Code Help Please....
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,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 "JLatham" wrote: Glad to hear that it actually did what you needed to be done. Enjoy! "kmwhitt" wrote: You are a GENIUS!!! I can't thank you enough! Works 100%.... Thanks, Kevin "JLatham" wrote: 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 "kmwhitt" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text formatting | Excel Worksheet Functions | |||
Can someone help me put psuedo code into actual excel macro?? | Excel Discussion (Misc queries) | |||
VBA code using if then and msgbox | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions |