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

Alminc

New Member
Joined
Mar 30, 2018
Messages
20
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.

Code:
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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
   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

New Member
Joined
Mar 30, 2018
Messages
20
Try
Code:
   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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 30, 2018
Messages
20

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
Forgot something
Code:
      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
Code:
Sub chk()
Application.EnableEvents = True
End Sub
 

Alminc

New Member
Joined
Mar 30, 2018
Messages
20
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,109,247
Messages
5,527,625
Members
409,777
Latest member
jamilowella

This Week's Hot Topics

Top