Need to auto-populate a list.

L

Labkhand

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

Labkhand

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
 
L

Labkhand

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).Range(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(Worksheets(VARS_SHEET).Range(LIST_NAME).Rows.Count + 1, 1).End(xlUp).Offset(1, 0) = Target

Worksheets(VARS_SHEET).Range(LIST_NAME).Resize(Worksheets(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
 
S

Sheeloo

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
 
L

Labkhand

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?
 
L

Labkhand

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! :(
 
S

Sheeloo

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).Range(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
'Above two lines changed to
Target.Offset(-1, 0).Select
Selection.Value = ""
Else

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

Worksheets(VARS_SHEET).Range(LIST_NAME).Resize(Worksheets(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
'--------------------
 
L

Labkhand

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! ;-(
 
L

Labkhand

sorry i have a few typos....

A@ = A2
A# = A3

Labkhand said:
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! ;-(
Click to expand...
 
S

Sheeloo

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
 
L

Labkhand

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
 
S

Sheeloo

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

Labkhand

Thanks, I just sent you an email.

Sheeloo said:
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...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Populate Data from Summary Sheet 5
Auto Populate 1
Need to populate data from different sheet. 1
Auto populate cell 3
How do I auto populate based on a drop down list? 0
Possible Indirect query 1
How to update data from multiple sheets to one specific sheets 4
Filtering of data in a master excel sheet from multiple sheets 3

Top