Run Macro From Drop Down List

[BettyBoop0916]

Hi there,

I have a small problem. I am trying to have a macro run from a drop down list that I created through data validation.

The macro I am trying to run is as follows:

Sub GasAndOil()
ActiveSheet.Unprotect
Rows(ActiveCell.Row & ":" & ActiveCell.Row + 5).Select
Selection.Delete
Worksheets("Data").Visible = True
Sheets("Data").Select
Sheets("Data").Range("C9:I15").Select
Selection.Copy
Sheets(Index).Select
Selection.Insert
Worksheets("Data").Visible = False
ActiveSheet.Protect
End Sub

Now...I want this macro to run when I select "(5675) Gas & Oil - Regina" from a drop down list. This drop down list can be in any row in column A...it really just depends on where I put my cursor.

Does anyone have any ideas on how I can have it call this macro?

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.Column = 1 And Target.Value = "(5675) Gas & Oil - Regina" Then
    Application.EnableEvents = False
    Call GasAndOil
    Application.EnableEvents = True
End If
End Sub

 

[BettyBoop0916]

Thanks so much! It works perfectly - the only issue I'm having now is that every time I insert a new row using a button I've created, it says "Type Mismatch"

This is the macro I've assigned to the button:

Private Sub CommandButton2_Click()
ActiveSheet.Unprotect
Worksheets("Data").Visible = True
Sheets("Data").Select
Sheets("Data").Range("C1:I6").Select
Selection.Copy
Sheets(Index).Select
Selection.Insert Shift:=xlDown
Worksheets("Data").Visible = False
ActiveSheet.Protect
End Sub

And it gets hung up on the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Value = "(5675) Gas & Oil - Regina" Then
Application.EnableEvents = False
Call GasAndOil
Application.EnableEvents = True
End If
End Sub

Any suggestions?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 And Target.Value = "(5675) Gas & Oil - Regina" Then
Application.EnableEvents = False
Call GasAndOil
Application.EnableEvents = True
End If
End Sub

 

[BettyBoop0916]

That's awesome!!!! Thanks, that solved the problem.