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.

See http://www.contextures.com/xlNames01.html#Dynamic for the solution of
your problem.

Let us know if you are still not unable to get what you want..

--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"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.

Create a Dynamic Named Range to expand as you add more data.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Mon, 20 Oct 2008 11:17:01 -0700, 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.

Sheeloo and Gord,

Thanks for quick replies, however, your solution is not helping me! I know
how to create the named range and reference it, etc... what i need is the VB
code which should be part of the Worksheet_Change subtoutine. I was hoping
I get help there.

Thanks

"Gord Dibben" wrote:

Create a Dynamic Named Range to expand as you add more data.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Mon, 20 Oct 2008 11:17:01 -0700, 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.

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.

1. Replace the following lines
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
with
If lReply = vbNo Then
'reselect previous cell so that a new date can be provided.
Target.Value = ""
Target.Select

2. It will execute everytime you change a cell value
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Labkhand" wrote:

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.

Hi Sheeloo,

Thanks for your solution. It resolved my issue partially. When I select
NO, it goes back to the active cell so that a new date can be re-entered (So
far so good), BUT, after I enter a new date in the cell, it doesn't
re-display the MSGBOX! Cursor just moves to the next cell without doing any
thing! Why?


"Sheeloo" wrote:

1. Replace the following lines
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
with
If lReply = vbNo Then
'reselect previous cell so that a new date can be provided.
Target.Value = ""
Target.Select

2. It will execute everytime you change a cell value
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Labkhand" wrote:

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.

Sorry, I should have pointed this out yesterday but...

Target is the NEW cell selected... hence the code is not working...
so if you enter any value in A1 and press enter (and you are set to move to
A2) then target is A2...

You will have to use something like the solution at
http://www.java2s.com/Code/VBA-Excel...hangeevent.htm
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Labkhand" wrote:

Hi Sheeloo,

Thanks for your solution. It resolved my issue partially. When I select
NO, it goes back to the active cell so that a new date can be re-entered (So
far so good), BUT, after I enter a new date in the cell, it doesn't
re-display the MSGBOX! Cursor just moves to the next cell without doing any
thing! Why?

Sheeloo,

Sorry, can you please be more specific on which part of your suggessted code
I would need to incorporate into my code? I am confused! :(

"Sheeloo" wrote:

Sorry, I should have pointed this out yesterday but...

Target is the NEW cell selected... hence the code is not working...
so if you enter any value in A1 and press enter (and you are set to move to
A2) then target is A2...

You will have to use something like the solution at
http://www.java2s.com/Code/VBA-Excel...hangeevent.htm
--
Always provide your feedback so that others know whether the solution worked
or problem still persists ...


"Labkhand" wrote:

Hi Sheeloo,

Thanks for your solution. It resolved my issue partially. When I select
NO, it goes back to the active cell so that a new date can be re-entered (So
far so good), BUT, after I enter a new date in the cell, it doesn't
re-display the MSGBOX! Cursor just moves to the next cell without doing any
thing! Why?