Running macros by assigning them to values in drop down list

[ysherriff]

Hi all,

I need help in calling a macro from the selected dropdown. The name of the macro to call is State_Selection.

here is the code based on drop down list

Private Sub Worksheet_Change(ByVal Target As Range)
 
  Select Case Range("State_Selection")
 
  Case "AL"
  Call State_Selection
  Case "CA"
  Call State_Selection
  End Select
End Sub

here is the State_Selection macro code to call

Sub State_Selection()

 Dim selection As String

  State_Selection = Range("State_Selection")
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  Sheets("Segmentation").Activate
  ActiveSheet.Unprotect "ops"
  ActiveSheet.Range("SEGMENTATION_TBL").AutoFilter Field:=1, Criteria1:=State_Selection
  ActiveSheet.Protect "ops"
  Range("B6").Activate
  Sheets("Hospital Discharge").Activate
  ActiveSheet.Unprotect "ops"
  ActiveSheet.Range("DISCH_TBL").AutoFilter Field:=10, Criteria1:=State_Selection
  ActiveSheet.Protect "ops"
  Range("B5").Activate
  Sheets("State Selection").Activate
  
  
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  
 MsgBox "Selected state has been filtered!", vbInformation
  
End Sub

What am I doing wrong? The workbook is attached.

Thanks for your help.

 

[p45cal]

see attached.

 

[ysherriff]

Thank you and I appreciate you cleaning the code.

Thanks again