VBA warning message when specific drop down selection is made

[Shadkng]

Hi,would the following be possible with VBA...I would like a warning message to pop up when a particular selection is made from a drop down list in a worksheet, or in a specific range if it must. However, I want the message to pop up only the first time that selection is made, so suppressed after that. Would the selection have to be exact or could it be based on a string or prefix of the name in the selection? I would not want the message to pop up if that selection is existing when opening a saved workbook. This is not a super important thing so I wouldn't want you to put a lot of effort if needed. Thanks

 

[Shadkng]

I experimented doing the following. Below is the code I modified.

Instead of using the "hideme" sheet I am using a cell on the active sheet.
Then I disabled the line "ActiveSheet.Range("B3") = 1" so the warning would be repetitive and that was ok.
Then I tried using a range of C24:C1000 and ran into trouble. When copying lines down I would get the error "run time error 13, type mismatch".

It would be helpful if I can have the error anywhere in a range. Generally we start our sheet with one line of data in row 24 and then copy down as many rows as needed. Then we modify different cells in those rows. The column for the warnings as you know is C. Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C24:C1000")) Is Nothing And ActiveSheet.Range("B3") <> 1 Then
    If UCase(Target) Like "GAL*" Or UCase(Target) Like "SA-36*" Or UCase(Target) Like "SA-45*" Then
        MsgBox "**YOU MAY WANT TO SELECT CONTINUOUS CHAIN**"
       ActiveSheet.Range("B3") = 1    'voided this line and ran into the error
    End If
End If
End Sub

 

[Scott T]

If you do not use ActiveSheet.Range("B3") = 1 then there is no reason to check the cell at the start of the code.
The only time I get "run time error 13, type mismatch" is when trying to delete multiple cells. The code below will check the number of cells that are being changed and will skip the check if multiple cells are selected.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C24:C1000")) Is Nothing And Target.Cells.Count = 1 Then
    If UCase(Target) Like "GAL*" Or UCase(Target) Like "SA-36*" Or UCase(Target) Like "SA-45*" Then
        MsgBox "**YOU MAY WANT TO SELECT CONTINUOUS CHAIN**"
       'ActiveSheet.Range("B3") = 1    'voided this line and ran into the error
    End If
End If
End Sub

 

[Shadkng]

Hi, modified the code as follows to give a choice about the warning. I'm new at VBA so don't know if it's written perfectly. I'm still getting the error when B3 is empty and I try to copy down lines or delete lines, and the error comes regardless if any item is selected in C24:C1000. I know you explained it above but I'm clear on the problem. Can we solve the error? thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C24:C1000")) Is Nothing And ActiveSheet.Range("B3") <> 1 Then
    If UCase(Target) Like "GAL*" Or UCase(Target) Like "SA-36*" Or UCase(Target) Like "SA-45*" Then
         response = MsgBox("YOU MAY NEED CONTINUOUS CHAIN. SELECT *YES* TO STOP FUTURE WARNINGS OR *NO* TO CONTINUE WARNINGS.", vbYesNo)
    If response = vbYes Then
    ActiveSheet.Range("B3") = 1
    If response = vbNo Then
    Exit Sub
    End If
    End If
    End If
End If
End Sub

 

[Scott T]

You need to check if the number of cells that are changing is greater then 1, if it is more then run then do not run the check.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C24:C1000")) Is Nothing And ActiveSheet.Range("B3") <> 1 And Target.Cells.Count = 1 Then
    If UCase(Target) Like "GAL*" Or UCase(Target) Like "SA-36*" Or UCase(Target) Like "SA-45*" Then
         response = MsgBox("YOU MAY NEED CONTINUOUS CHAIN. SELECT *YES* TO STOP FUTURE WARNINGS OR *NO* TO CONTINUE WARNINGS.", vbYesNo)
        If response = vbYes Then
            ActiveSheet.Range("B3") = 1
        End If
    End If
End If
End Sub

Since there is not other code to skip you do not need to check if no was selected and to exit the sub. If you need it you would use else since you only have yes or no if it is not yes it must be no.

If response = vbYes Then
    ActiveSheet.Range("B3") = 1
Else
    Exit Sub
End If