Attaching autorun macro to data valadation

QuestionEdit

QUESTION: I have a question about auto starting a macro from a Data validation dropdown menu. I am on2007. I have ask this question from another expert and received a very complicated answer. There must be an easier way. Maybe an example would better serve this question. Obviously the example will be less complex than the actual situation, but hopefully you will get the jest of it. EG: On sheet One I have a data validation drop down menu for the year I want to see data from. 1997-2010 is in my data validation drop down menu. When 1997 is selected from this box, on sheet 1, I need a macro to automatically start a copy and paste sequence from sheet 2, and sheet 3 certain data for that particular year and put that data into the final sheet 4 which has the form on it. I have the data validation dropdown and I have the macros for each year created I just need to know how I can get the macros to automatically run when the end user selects a year from the data validation dropdown menus by the year on sheet 1. Where can I attach the macros for each individual year?

ANSWER: Well, this is one of those cases where I agree with you in terms of, it sounds like there IS an easy way, IF I am understanding you correctly.  :)

If your sheet is named Sheet1, and if the cell where they select that is A1, THEN: Place this code not in a standard module but in the module behind the worksheet where the user is selecting the data validation. (if you know what I mean cool if not post a follow up).

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A1")) Is Nothing Then

 Select Case Target.Value

Case 1997 'here, put your code for 1997 macro.

Case "1998" 'here, put your code for 1998 macro.

Case Else Exit Sub

End Select

End If End Sub

---------- FOLLOW-UP ----------

QUESTION: Is there a way to force the macro to run in background and keep the user from visually seeing the macro run? IE: The Macro switching from sheet to sheet as the copy and paste performs its tasks. I would like it to run in the background and leave the user on the sheet that holds the Data Validation drop down. Is this possible?

ANSWER: Near the beginning of your code, perhaps the first line actually, put

Application.ScreenUpdating = False

BE SURE**** to turn it back on at the end of the code. No one wants theirapplication all funky LOL

At end of code, before end sub

Application.ScreenUpdating = True

Now if they are still seeing screens you don't want them to, it's because your code maybe is actually activating and selecting sheets. If that's the case, post your code again and we can go from there.

---------- FOLLOW-UP ----------

QUESTION: Ok. Last few lines should read:

End If Application.ScreenUpdating = True End Sub

Then the final question is there a way to delay the macro from running by 1/2 of a sec.

AnswerEdit

Yes that's correct. Exactly there and no where else.

If it is before the End Select, then it will only go back to True in a certain case.

If it is between End Select and End If, then it will only go back to True depending on the If statement about A1.

So yes is the answer.

Whenever you want the code to pause, use this:

   Application.Wait (Now + TimeValue("0:00:01"))

In this line I used as an example, the time it waits is 1 second.

If you find that syntax to be confusing, you can also us:

Application.Wait Now + TimeSerial(0, 0, 01