I have created a macro to open a msgbox if the user selects a specific
date from a drop down list. However, currently it will only run if
the user clicks off that cell and then back on it. I want it to run
immediately if the user selects that specific date, or at least run as
soon as they click off that cell (they shouldn't have to click back on
it to get the msgbox to pop up).
The code I am currently using in the specific worksheet is as follows:

Private Sub Worksheet_SelectionChange (ByVal Target as Range)
If Target.Address="$H$4" Then
Application.EnableEvents = True
If Target.Value = "1/4/2008" Or Target.Value =
"2/8/2008" Or Target.Value = "3/7/2008" Or Target.Value = "4/4/2008"
Or Target.Value = "5/9/2008" Or Target.Value = "6/6/2008" Or
Target.Value = "7/4/2008" Or Target.Value = "8/8/2008" Or Target.Value
= "9/5/2008" Or Target.Value = "10/3/2008" Or Target.Value =
"11/7/2008" Or Target.value = "12/5/2008" Then
Msg.Box "This is a New Month,Click 'New Month' Button before
Proceeding"
Range ("H4").Select
Else
End If
End If
End Sub


The Drop Down lists come from named ranges on a different tab that
show up dependant on todays date (If todays date is in the Month of
August, then the August list would appear, if today date is in the
month of November, then the November list would appear).

I also would like to be able to get another macro to run (a simple
sort macro) automatically when the user goes to save the file - usllay
they cahnge the name of the file when they save it).

OK - thats about it for now. Not sure how difficult this task is, but
if anyone can help, I would really appreciate it.
Thanks!

Use Worksheet_Change instead of SelectionChange
Private Sub Worksheet_Change(ByVal Target As Range)

As for resorting on save, use the BeforeSave event of the ThisWorkbook
module.

bjohnson wrote:
I have created a macro to open a msgbox if the user selects a specific
date from a drop down list. However, currently it will only run if
the user clicks off that cell and then back on it. I want it to run
immediately if the user selects that specific date, or at least run as
soon as they click off that cell (they shouldn't have to click back on
it to get the msgbox to pop up).
The code I am currently using in the specific worksheet is as follows:

Private Sub Worksheet_SelectionChange (ByVal Target as Range)
If Target.Address="$H$4" Then
Application.EnableEvents = True
If Target.Value = "1/4/2008" Or Target.Value =
"2/8/2008" Or Target.Value = "3/7/2008" Or Target.Value = "4/4/2008"
Or Target.Value = "5/9/2008" Or Target.Value = "6/6/2008" Or
Target.Value = "7/4/2008" Or Target.Value = "8/8/2008" Or Target.Value
= "9/5/2008" Or Target.Value = "10/3/2008" Or Target.Value =
"11/7/2008" Or Target.value = "12/5/2008" Then
Msg.Box "This is a New Month,Click 'New Month' Button before
Proceeding"
Range ("H4").Select
Else
End If
End If
End Sub


The Drop Down lists come from named ranges on a different tab that
show up dependant on todays date (If todays date is in the Month of
August, then the August list would appear, if today date is in the
month of November, then the November list would appear).

I also would like to be able to get another macro to run (a simple
sort macro) automatically when the user goes to save the file - usllay
they cahnge the name of the file when they save it).

OK - thats about it for now. Not sure how difficult this task is, but
if anyone can help, I would really appreciate it.
Thanks!