Running A Macro When Any Validation List Item is Selected

[Pony08]

I need to know what code I would need to insert into a Workbook tab (under "View Code") that would run a simple macro regardless of which item is selected in a validation list? The macro name is "UpdateDMReport"

[AlphaFrog]

Try something like this. Change the A1 to the cell that has the DV list.

Please Login or Register  to view this content.

[Pony08]

Thx for the quick response! The validation list is at cell "C5"....I replaced "A1" with "C5" and nothing happens when you select any of the list choices. I am using Excel 2010...if that makes a difference. Thoughts?

[AlphaFrog]

Did you put the code in the worksheet code module (not the workbook code module)? Right-click on the worksheet tab and select View Code.

[Pony08]

Yes...I right clicked on the worksheet tab, selected View Code, pasted the code in and changed A1 to C5. I am stumped.

code.png

[AlphaFrog]

Use "C5" and not "$c$5". It matters in this case.

[Pony08]

I tried it both as "C5" and "$C$5" and neither worked. A created a button and assigned the macro to that button and it runs fine so the issue is not the macro.

[AlphaFrog]

Run this macro once to ensure the worksheet events are enabled.

Please Login or Register  to view this content.

[Pony08]

I ran the code and then tried the validation box...no change

[AlphaFrog]

Did you put "C5" with a capital C?

What is your code for UpdateDMReport ?

[Pony08]

Here is the Macro Code...


Sub UpdateDMReport()
'
' UpdateDMReport Macro
'

'
' Stop screen changes (flickering) from being viewed

Application.ScreenUpdating = False

Sheets("Combined").Visible = True
Sheets("Combined").Select
Columns("AA:AU").Select
Selection.Copy
Columns("AY:AY").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BW5").Select
Application.CutCopyMode = False

ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Range("BW502").Select

ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("BW803").Select

ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
Sheets("Combined").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("DM View").Select
Range("E14").Select

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Range("C5:D10").Select

Application.ScreenUpdating = True

End Sub

[AlphaFrog]

As a test, try this and see if the msgbox pops up when you change the DV list.

Please Login or Register  to view this content.

[Pony08]

yes...the box opens up and says triggered

[Pony08]

I figured it out from your code...I changed it to this and it works! Thanks for all your help!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "C5" Then
If Target.Value <> "" Then Run "UpdateDMReport"
End If
End Sub

[AlphaFrog]

yes...the box opens up and says triggered

This should work. I'm stumped as well.

Please Login or Register  to view this content.

Is UpdateDMReport in a standard code module e.g. Module1?

Also, for future reference, surround your pasted code with CODE tags.

[Pony08]

I changed call to run and it worked!