Excel - Run macro when item is selected from dropdown list

October 2016

Microsoft Office is a commercial Office Software with organized servers, services and desktop applications for Windows and Mac OS X operating systems. This Office Software contains an application called Microsoft Excel which is a commercial spreadsheet application featuring macro programming, calculations, etc. A macro is a series of instructions in Excel, which performs a particular task. If you want to perform a specific task repetitively then you can automate that task with a macro. A drop down list allows you to enter the data into a spreadsheet from a pre-set list of entries. It makes the data entry simpler and confines the number of positions to enter the data. You can run a different macro for every selection in the drop down list.


I have been able to create a macro that will populate the cell in the A column with the date & the Cell in the B column with the time (with relative references so it will do it on whatever row the cursor is in) and have called it datetimeentry (it's a module)</gras>

Sub datetimeentry()     
' datetimeentry Macro     
ActiveCell.Value = Date     
ActiveCell.Offset(0, 1).Range("A1").Select     
ActiveCell.Value = Time     
ActiveCell.Offset(0, 2).Range("A1").Select     
End Sub     
  • I have also created a dropdown list in column C to choose from. What I want to do is this:
  • I want to make the macro run once I have selected something from the dropdown list. This macro should run on the same row as the cell that I have it picked from.

ie: if I select from the dropdown list in cell c1156 than I want the macro to run and insert the date in A1156 & time in B1156 then jump to cell D1156 for me to enter a value in.


Well first let me say, failure is a very valid option :P

You need to define this function:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Let's say the sheet that we are talking about is sheet1
  • So open the VBE and double click on sheet1 on the project explorer
  • Paste this routine and read the comments in the code about blank

Private Sub Worksheet_Change(ByVal Target As Range)    

    'here 3 is column number 3 which is column C    
    If (Target.Column <> 3) Then Exit Sub    
    Application.EnableEvents = False    
    thisrow = Target.Row    

    If Target.Value = "" Then    
        'now you can comment out the code if it does not make sense    
        'but the idea is if the cell in column C has been cleared out,    
        'corresponding date and time should be cleared out too    
        Cells(thisrow, "A") = ""    
        Cells(thisrow, "B") = ""    
        Cells(thisrow, "A") = Date    
        Cells(thisrow, "B") = Time    
    End If    
    Application.EnableEvents = True    
End Sub

Note that

Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Run macro when item is selected from dropdown list » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.