Assigning Macros to Drop Down Lists in Excel

VBA

Assigning macros to Form Controls is easy and to Active X controls is easier because they expose themselves to a list of events which can trigger the running of VBA code.

The picture below shows the list of events available for an active X Combo Box

Such events are not available for the Drop Down Data Validation (In cell) Lists.

However we can use Worksheet_Change event to assign macros to Drop Down Lists. In this post we will learn how to do this

In this example we have Monthly, Quarterly and Yearly data for a Company. Also we have a drop down list in Cell A1. So our objective is that …when a user selects an item from the drop down, only relevant columns should be displayed and the rest should be hidden.

The animated GIF below shows our desired RESULT

Following is the VBA Worksheet code used to achieve this task

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TrigerCell As Range

Set Triggercell = Range("A1")

If Not Application.Intersect(Triggercell, Target) Is Nothing Then
    If Triggercell.Value = "Months" Then
      Columns("D:U").Hidden = True
      Columns("D:O").Hidden = False
    ElseIf Triggercell.Value = "Quarters" Then
      Columns("D:U").Hidden = True
      Columns("P:S").Hidden = False
    ElseIf Triggercell.Value = "Years" Then
      Columns("D:U").Hidden = True
      Columns("T:U").Hidden = False
    End If
 End If
End Sub

Following are the key points in this Code

  1. We do not want the code to run on every Worksheet Change Event…. but only when value in Cell A1 changes (via drop down)
  2. So first we have defined our Trigger Cell as A1
  3. Next we use Application.Intersect(Triggercell, Target) method to ensure that Columns.hidden procedure runs only when Triggercell is changed

 

Download the Excel file by clicking here and play with it