How to prevent only certain items in dropdown list from triggering action

[Alminc]

Hi guys,

I have two dropdown lists, one for Status (column G), and one for Priority (column H) in one sheet.

Status list items: Avvakta, Underlag saknas, Utgår, Pågår, Klart
Priority list items: Prio 1, Prio 2, Prio 3, Övrigt

Now I've got a code that needs some modification in order to prevent items: Avvakta, Underlag saknas, Pågår, and Prio 2 to trigger any action at all except being selected, but I dont know how to do that.

Please help.

Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim Ans As String
   Dim ws As Worksheet, nextrow As Long
   Dim Sht As String
   
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("H10:H110,G10:G110")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False

   Sht = Target.Value
   Select Case Sht

[COLOR=#669933]'------------------------------------------------------------------------------------------------------------------
'Move Prio 1, Prio 3 and Övrigt to corresponding sheets, but do nothing when Prio 2 is slected. How ??
'------------------------------------------------------------------------------------------------------------------[/COLOR]
      Case "Prio 1"
         If Not Evaluate("isref('" & Sht & "'!a1)") Then Sheets.Add.Name = "Prio 1"
         Set ws = Sheets("Prio 1")
      [COLOR=#669933]'Case "Prio 2"
         'If Not Evaluate("isref('" & Sht & "'!a1)") Then Sheets.Add.Name = "Prio 2"
         'Set ws = Sheets("Prio 2")[/COLOR]
      Case "Prio 3"
         If Not Evaluate("isref('" & Sht & "'!a1)") Then Sheets.Add.Name = "Prio 3"
         Set ws = Sheets("Prio 3")
      Case "Övrigt"
         If Not Evaluate("isref('" & Sht & "'!a1)") Then Sheets.Add.Name = "Övrigt"
         Set ws = Sheets("Övrigt")
[COLOR=#669933]'-------------------------------------------------------------------------------------------------------------------------------------------------------
'Move status Utgår and Klart to sheets Utgår and Klart, but do nothing when Avvakta, Underlag saknas and Pågår are selected. How???
'-------------------------------------------------------------------------------------------------------------------------------------------------------[/COLOR]
      Case "Utgår"
         If Not Evaluate("isref('" & Sht & "'!a1)") Then Sheets.Add.Name = "Utgår"
         Set ws = Sheets("Utgår")
      Case "Klart"
         If Not Evaluate("isref('" & Sht & "'!a1)") Then Sheets.Add.Name = "Klart"
         Set ws = Sheets("Klart")
     
 '----------------------------------------------------------------------------------   
[COLOR=#669900] ' Dont know what to do here....All items are triggering same action below
'---------------------------------------------------------------------------------[/COLOR]
      
                  
   End Select
   Me.Activate
   If MsgBox("Ska Almin flytta ärendet till fliken " & Sht & "?", vbYesNo + vbQuestion) = vbYes Then
      Target.EntireRow.Copy ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
      Target.Select
      Target.EntireRow.Delete
      If MsgBox("Vill du öppna fliken " & Sht & " nu?", vbYesNo + vbQuestion) = vbYes Then Sheets(Sht).Activate
           
   Else
   
      Target.ClearContents
      
   End If
Application.EnableEvents = True
   
End Sub

I've got the original code from forum member Fluff, I am not coder myself.

Please someone help.

 

[Fluff]

Try

   Case "Klart"
      If Not Evaluate("isref('" & sht & "'!a1)") Then Sheets.Add.Name = "Klart"
      Set ws = Sheets("Klart")
 [COLOR=#0000ff]  Case Else
      Exit Sub[/COLOR]
End Select

 

[Alminc]

Try

   Case "Klart"
      If Not Evaluate("isref('" & sht & "'!a1)") Then Sheets.Add.Name = "Klart"
      Set ws = Sheets("Klart")
 [COLOR=#0000ff]  Case Else
      Exit Sub[/COLOR]
End Select

Thank you, I just tried that but now none of the list items is triggering action.

I need items Prio 1, Prio 3, Övrigt, Utgår and Klart to trigger the action, while other items should just be passively selected.

 

[Fluff]

That should not affect the the cases you already have, it should only affect values that you don't have a select case for.

 

[Alminc]

I don't understand

I have all the items on my two dropdown lists, I just want some of the existing items to not trigger action when they are selected from the list, while other items do that.

Can you give me an example code, say I have items A, B, and C on the list, and I want only A and B to trigger action. Item C should only be selected from the list without any action following.

 

[Fluff]

Forgot something

      Case "Klart"
         If Not Evaluate("isref('" & Sht & "'!a1)") Then Sheets.Add.Name = "Klart"
         Set ws = Sheets("Klart")
      Case Else
         [COLOR=#ff0000]Application.EnableEvents = True[/COLOR]
         Exit Sub
   End Select

You may need to run this before it will work

Sub chk()
Application.EnableEvents = True
End Sub

 

[Alminc]

It works now, thank you so much

I have another problem here that nobody has answered yet, so if it's not too much to ask...