Need to auto-populate a list.
 

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.

Need to auto-populate a list.
 

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.

Need to auto-populate a list.
 

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.

Need to auto-populate a list.
 

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.

Need to auto-populate a list.
 

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.

Need to auto-populate a list.
 

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.

Need to auto-populate a list.
 

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.

Need to auto-populate a list.
 

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?

Need to auto-populate a list.
 

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?

Need to auto-populate a list.
 

I meant you will have to redesign your code using the ideas given in the
sample code.
If you are sure the user will enter after typing the data and you have
settings which take the cursor to the next cell in the same column then you
can use the following;
'--------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
'Changed to
If Target.Cells.Count 1 Or IsEmpty(Target.Offset(-1, 0)) 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(xlU p).Select
'Above two lines changed to
Target.Offset(-1, 0).Select
Selection.Value = ""
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
'--------------------

"Labkhand" wrote:

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?

Need to auto-populate a list.
 

Hi Sheeloo,

Your newest solution makes things worst! Let's say users enetr a good date
in cell A@, then cursor moves to A#...then they enter a new date and the
prompt asks if the new date should be added to the list....user selects "N"
then the value in cell A2 is wiped out and the invalid date still remains in
cell A3.

What should have happend was the value of cell A3 should be wiped out and
then user enters a new date and the prompt should ask if the date needs to be
added to the list. But this is tricky and is not working! ;-(


"Sheeloo" wrote:

I meant you will have to redesign your code using the ideas given in the
sample code.
If you are sure the user will enter after typing the data and you have
settings which take the cursor to the next cell in the same column then you
can use the following;
'--------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
'Changed to
If Target.Cells.Count 1 Or IsEmpty(Target.Offset(-1, 0)) 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(xlU p).Select
'Above two lines changed to
Target.Offset(-1, 0).Select
Selection.Value = ""
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
'--------------------

"Labkhand" wrote:

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?

Need to auto-populate a list.
 

sorry i have a few typos....

A@ = A2
A# = A3

"Labkhand" wrote:

Hi Sheeloo,

Your newest solution makes things worst! Let's say users enetr a good date
in cell A@, then cursor moves to A#...then they enter a new date and the
prompt asks if the new date should be added to the list....user selects "N"
then the value in cell A2 is wiped out and the invalid date still remains in
cell A3.

What should have happend was the value of cell A3 should be wiped out and
then user enters a new date and the prompt should ask if the date needs to be
added to the list. But this is tricky and is not working! ;-(


"Sheeloo" wrote:

I meant you will have to redesign your code using the ideas given in the
sample code.
If you are sure the user will enter after typing the data and you have
settings which take the cursor to the next cell in the same column then you
can use the following;
'--------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
'Changed to
If Target.Cells.Count 1 Or IsEmpty(Target.Offset(-1, 0)) 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(xlU p).Select
'Above two lines changed to
Target.Offset(-1, 0).Select
Selection.Value = ""
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
'--------------------

"Labkhand" wrote:

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?

Need to auto-populate a list.
 

My post on 10/21 was WRONG... I must have made some mistakes while testing...
Let us go back to the basics...

I tested with the following simple code and it is working fine...
so essentially you just have to use
If lReply = vbNo Then
Target.Value = ""
End If

'Tested code
Private Sub Worksheet_Change(ByVal Target As Range)

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

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
Target.Value = ""
Target.Select
End If

End Sub"Labkhand" wrote:

sorry i have a few typos....

A@ = A2
A# = A3

"Labkhand" wrote:

Hi Sheeloo,

Your newest solution makes things worst! Let's say users enetr a good date
in cell A@, then cursor moves to A#...then they enter a new date and the
prompt asks if the new date should be added to the list....user selects "N"
then the value in cell A2 is wiped out and the invalid date still remains in
cell A3.

What should have happend was the value of cell A3 should be wiped out and
then user enters a new date and the prompt should ask if the date needs to be
added to the list. But this is tricky and is not working! ;-(


"Sheeloo" wrote:

I meant you will have to redesign your code using the ideas given in the
sample code.
If you are sure the user will enter after typing the data and you have
settings which take the cursor to the next cell in the same column then you
can use the following;
'--------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
'Changed to
If Target.Cells.Count 1 Or IsEmpty(Target.Offset(-1, 0)) 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(xlU p).Select
'Above two lines changed to
Target.Offset(-1, 0).Select
Selection.Value = ""
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
'--------------------

"Labkhand" wrote:

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?

Need to auto-populate a list.
 

I am not sure how it is working for you since it is not for me. After going
back to a cell following responding NO to the add to list prompt, when i
enter the new date, it doesn't re-display the ADD TO LIST prompt...cursor
just moves to the cell below the activecell!!!!

Can you please retest your code?

Thanks


"Sheeloo" wrote:

My post on 10/21 was WRONG... I must have made some mistakes while testing...
Let us go back to the basics...

I tested with the following simple code and it is working fine...
so essentially you just have to use
If lReply = vbNo Then
Target.Value = ""
End If

'Tested code
Private Sub Worksheet_Change(ByVal Target As Range)

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

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
Target.Value = ""
Target.Select
End If

End Sub"Labkhand" wrote:

sorry i have a few typos....

A@ = A2
A# = A3

"Labkhand" wrote:

Hi Sheeloo,

Your newest solution makes things worst! Let's say users enetr a good date
in cell A@, then cursor moves to A#...then they enter a new date and the
prompt asks if the new date should be added to the list....user selects "N"
then the value in cell A2 is wiped out and the invalid date still remains in
cell A3.

What should have happend was the value of cell A3 should be wiped out and
then user enters a new date and the prompt should ask if the date needs to be
added to the list. But this is tricky and is not working! ;-(


"Sheeloo" wrote:

I meant you will have to redesign your code using the ideas given in the
sample code.
If you are sure the user will enter after typing the data and you have
settings which take the cursor to the next cell in the same column then you
can use the following;
'--------------------
Private Sub Worksheet_Change(ByVal Target As Range)

'If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
'Changed to
If Target.Cells.Count 1 Or IsEmpty(Target.Offset(-1, 0)) 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(xlU p).Select
'Above two lines changed to
Target.Offset(-1, 0).Select
Selection.Value = ""
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
'--------------------

"Labkhand" wrote:

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?

Need to auto-populate a list.
 

Pl. send me a mail to me (add hotmail.com to to_sheeloo to get my id) so that
I can send a working copy of the sample code..

Did you add the following to the IF loop
If lReply = vbNo Then
Target.Value = ""
Target.Select
End If

I missed Target.Select but it was there in the tested code...
"Labkhand" wrote:

I am not sure how it is working for you since it is not for me. After going
back to a cell following responding NO to the add to list prompt, when i
enter the new date, it doesn't re-display the ADD TO LIST prompt...cursor
just moves to the cell below the activecell!!!!

Can you please retest your code?

Thanks

Need to auto-populate a list.
 

Thanks, I just sent you an email.

"Sheeloo" wrote:

Pl. send me a mail to me (add hotmail.com to to_sheeloo to get my id) so that
I can send a working copy of the sample code..

Did you add the following to the IF loop
If lReply = vbNo Then
Target.Value = ""
Target.Select
End If

I missed Target.Select but it was there in the tested code...
"Labkhand" wrote:

I am not sure how it is working for you since it is not for me. After going
back to a cell following responding NO to the add to list prompt, when i
enter the new date, it doesn't re-display the ADD TO LIST prompt...cursor
just moves to the cell below the activecell!!!!

Can you please retest your code?

Thanks

Need to auto-populate a list.
 

Thanks very much for all your help Sheeloo. Your solution resolved my issues.

"Sheeloo" wrote:

Pl. send me a mail to me (add hotmail.com to to_sheeloo to get my id) so that
I can send a working copy of the sample code..

Did you add the following to the IF loop
If lReply = vbNo Then
Target.Value = ""
Target.Select
End If

I missed Target.Select but it was there in the tested code...
"Labkhand" wrote:

I am not sure how it is working for you since it is not for me. After going
back to a cell following responding NO to the add to list prompt, when i
enter the new date, it doesn't re-display the ADD TO LIST prompt...cursor
just moves to the cell below the activecell!!!!

Can you please retest your code?

Thanks