All,

I think I was able to make the VB code work. Here is the code:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub

If WorksheetFunction.CountIf(Worksheets(VARS_SHEET).R ange(LIST_NAME),
Target) = 0 Then
Dim lReply As Long

lReply = MsgBox("The date " & Target & " is not part of the list, do you
wish to add it?", vbYesNo + vbQuestion)

If lReply = vbNo Then
'reselect previous cell so that a new date can be provided.
Worksheets(DETAILS_SHEET).Activate
Worksheets(DETAILS_SHEET).Range("A65536").End(xlUp ).Select
Else

Worksheets(VARS_SHEET).Range(LIST_NAME).Cells(Work sheets(VARS_SHEET).Range(LIST_NAME).Rows.Count + 1, 1).End(xlUp).Offset(1, 0) = Target

Worksheets(VARS_SHEET).Range(LIST_NAME).Resize(Wor ksheets(VARS_SHEET).Range(LIST_NAME).Rows.Count + 1, 1).Name = LIST_NAME

With Worksheets(VARS_SHEET).Range("A2",
Worksheets(VARS_SHEET).Range("A65536").End(xlUp))
.NumberFormat = "mmm-yyyy"
.HorizontalAlignment = xlLeft
.Font.Name = "Times New Roman"
.Font.Size = 9
End With

Worksheets(VARS_SHEET).Activate
Worksheets(VARS_SHEET).Range(LIST_NAME).Select

Selection.Sort Key1:=Worksheets(VARS_SHEET).Range("A2"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Worksheets(DETAILS_SHEET).Activate
End If
End If
End Sub

But I have a new issue! Problem Statement:

The"LReply MSGBOX" has YES, NO options: When I press the NO button, how do
I reselect the last cell (which has the unwanted new date), clear it's the
contents so that a new date can be inserted?

Another issue is that, when I select the "No" option for adding the new
entered date and move the cursor to the previous cell MANUALLY and re-enter a
new date, the "Worksheet_Change" subroutine does not get re-executed! Why?
how can I make it re-execute in this case?

Thanks all

"Labkhand" wrote:

Dear all,

I need to auto-populate a list. I have followed a few discussion links on
how to do this task but I am stuck since the VB code does not work and kept
giving me errors! So I gave up and removed the code! :-( But I really
need to make this work! I would really appreciate it if someone could help
me out. Here is what I have:

On the VARS sheet, I listed multiple dates (NOV-2007, Dec-2007, Jan-2008)
under the columns A2 to A4; with A1 holding the list lable "Available Dates".


Next, I created a list through the DATA-LIST-CREATE LIST (checked My List
Has Headers) option by selecting A1..A4.

Finally, I selected cells A2..A4 and named them "AvailableDates", by using
the INSERT-NAME-DEFINE option.

On my DATA sheet, the Data Validation for all cells in column "A" is the
LIST (=AvailableDates).

Now my problem: I need to auto-populate the "AvailableDates" list which
resides on the VARS sheet whenever I add a new date under the column "A" of
the DATA sheet.


Thanks for any help in advance.