Run macro from Data validation result

[redspanna]

Quick question...

Data validation box sits in cell A1

whatever the use selects from the list I want 'macro1' to run

any ideas??

thanks

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    Application.EnableEvents = False
    Call Macro1
    Application.EnableEvents = True
End If
End Sub

 

[redspanna]

having trouble with this

The date validation list sits in cell ref G10
I want the following code to run when a name is chosen from that list.

My current code is this...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$10" Then
Application.ScreenUpdating = False

'Clear old data first
    Sheets("Lateness database").Select
    Range("E15:G150").Select
    Selection.ClearContents
    Range("E15").Select
    
'Get new selected data from filter
    Sheets("Lateness data").Select
    Columns("A:D").Select
    Selection.AutoFilter Field:=1, Criteria1:=Sheets("Lateness database").Range("G10")
    Selection.Copy
    Sheets("Lateness database").Select
    Range("D14").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A7").Select

End If
End Sub

but the code breaks at ..

Columns("A:D").Select

however if I run the code above usining a button..it works fine!!

any ideas??

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$10" Then
Application.ScreenUpdating = False

'Clear old data first
    Sheets("Lateness database").Range("E15:G150").ClearContents
'Get new selected data from filter
    Sheets("Lateness data").Columns("A:D").AutoFilter Field:=1, Criteria1:=Sheets("Lateness database").Range("G10")
    Sheets("Lateness data").Columns("A:D").Copy
    Sheets("Lateness database").Range("D14").PasteSpecial Paste:=xlValues
End If
End Sub

 

[VoG]

Should be

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$10" Then
Application.ScreenUpdating = False

'Clear old data first
    Sheets("Lateness database").Range("E15:G150").ClearContents
'Get new selected data from filter
    Sheets("Lateness data").Columns("A:D").AutoFilter Field:=1, Criteria1:=Sheets("Lateness database").Range("G10")
    Sheets("Lateness data").Columns("A:D").SpecialCells(xlCellTypeVisible).Copy
    Sheets("Lateness database").Range("D14").PasteSpecial Paste:=xlValues
End If
End Sub