Trigger an event in Excel VBA from a data validation drop down

Question

I have a sheet where I want to give the user a choice of calculation types. The calculation types are done via a list selection in Data validation. Once selected, I want it to trigger an event which will then load the correct cells for that type of selection. How do I detect a data change event on the Data validation drop down or do I need to use the active x control for this?

Code for the worksheet change event not activating:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.count > 1 Then Exit Sub Application.EnableEvents = False On Error GoTo Errortrap '~~> Change it to the relevant string with which you want to compare StringToCheck = "+" If Not Intersect(Target, Range("D47")) Is Nothing Then '~~> Check for the cell value If Target.Value = StringToCheck Then 'setup row to capture addition fields Cells(33, 4).Value = "Input File 1" Cells(33, 4).Value = "Worksheet 1" Cells(33, 4).Value = "Cell 1" Cells(33, 4).Value = "Input File 2" Cells(33, 4).Value = "Worksheet 2" Cells(33, 4).Value = "Cell 2" End If End If LetsContinue: Application.EnableEvents = True Exit Sub Errortrap: MsgBox Err.Description Resume LetsContinue End Sub

This has been covered many times in the past. Do a search in SO. See this code sample that I posted in stackoverflow.com/questions/11953214/… — Siddharth Rout
– Siddharth Rout, Commented Jan 31, 2013 at 6:25
I did a search, and didn't find anything- must have used the wrong search terms — Marc L
– Marc L, Commented Jan 31, 2013 at 6:32
Have updated the comment above with a link. You have to use the Worksheet_Change event to trap the changes to the cell which has a DV. — Siddharth Rout
– Siddharth Rout, Commented Jan 31, 2013 at 6:34
Hmm, now the question is why aren't change events being detected in my workbook? Calculation is set to automatic and I have even added even added "Application.Calculation = xlCalculationAutomatic" to the worksheet.activate event — Marc L
– Marc L, Commented Jan 31, 2013 at 7:03
Maybe because the EnableEvents are switched off? See this link. See point 3 stackoverflow.com/questions/13860894/… Do this. Press CTL G to popup the Immediate Window and then type this in the immediate window ?application.EnableEvents = True and then try again. Also it would help if you update your question with the code that you are trying... — Siddharth Rout
– Siddharth Rout, Commented Jan 31, 2013 at 7:11

Nordik · Accepted Answer · 2015-11-20 12:10:32Z

Your code is fine. I tried it in a new workbook and it does just what it supposed to do.
When you change the value in D47 to "+" (whether by dropdown or manually) it writes six values one after another in a cell D33.

Maybe you meant to write

 Cells(33, 4).Value = "Input File 1" Cells(33, 5).Value = "Worksheet 1" Cells(33, 6).Value = "Cell 1" Cells(33, 7).Value = "Input File 2" Cells(33, 8).Value = "Worksheet 2" Cells(33, 9).Value = "Cell 2"

so the code will fill range D33:I33 rather than writing everything into D33.

Collectives™ on Stack Overflow

Trigger an event in Excel VBA from a data validation drop down

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related