kate84

Hi All,

I'm trying to make an excel sheet where:
- a cell in sheet 1 has a list of options for the user to choose from, but where the user can also enter his own entry
- sheet 2 contains the list. when the user adds an entry, it's added to the list, and the list automatically alphabetizes itself.

I've gotten this to work, but for some reason, it always stops working. I think that right after I code it, save it and test it, it works. But then if I exit out of the code and start it over, it seems to stop working.

I'd really appreciate your help! Thanks!

Kate

1) Add a list of names to column L of sheet 2. Go to insert->name->define and name it 'List1' saying that it refers to '=OFFSET(Sheet2!$L$1,0,0,COUNTA(Sheet2!$L:$L),1)'. Same thing for column R, making the name='List2' and referring to: '=OFFSET(Sheet2!$R$1,0,0,COUNTA(Sheet2!$R:$R),1)'.

2) Select column L of sheet 1, go to data->validation, select allow "list" , source: "List1", turn error alert off. Same thing for column M, but with source: "List2"

3) Right click on "Sheet 1", go to "View code" and enter:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 12 Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("List1"), Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Worksheets("Sheet2").Range("List1").Cells(Worksheets("Sheet2").Range("List1").Rows.Count + 1, 1) = Target
End If
End If
End If
If Target.Column = 13 Then
If IsEmpty(Target) Then Exit Sub
If WorksheetFunction.CountIf(Worksheets("Sheet2").Range("List2"), Target) = 0 Then
lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion)
If lReply = vbYes Then
Worksheets("Sheet2").Range("List2").Cells(Worksheets("Sheet2").Range("List2").Rows.Count + 1, 1) = Target
End If
End If
End If
End Sub

4) Right click on "Sheet 2", go to "View code" and enter:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Range("List1").Sort key1:=Range("List1")(1), _
order1:=xlAscending

Range("List2").Sort key1:=Range("List2")(1), _
order1:=xlAscending

ErrHandler:
Application.EnableEvents = True
End Sub

David M58

You've done a fine job with this code.

In the code for Sheet2, you have this line of code:

Application.EnableEvents = False

and then at the end of the code:

Application.EnableEvents = True

Here's a word of caution. One thing that can cause the program to stop working is the code being interrupted after setting EnableEvents to False, before it has a chance to set it back to True.

kate84

Thanks for your help. That seems to be working. I was wondering if you had advice about one other issue that I'm having:

In sheet 2, I have a list L1. In sheet 1, I have a column (L) that allows the user to select names from the list. In sheet 2, I'd then like to count the number of occurrences of each of the specific names from the list in sheet 1. I am accomplishing this by the following formula:

=COUNTIF(Sheet1!L:L,L1)

However, I also have another column (O) that may be filled in with a name or may be left empty. I'd like to repeat the above calculation of counting the number of occurrences of the name in column L, but only if there is a name selected in column O. Initially, in worksheet 3, I made every cell an if statement. So for a cell in row i, column j, I'd say, if there was a value is row i, column O of sheet 1, make the cell equal to the value in row i, column j of sheet 1. Otherwise, set it to 0. Then I could use the same countif statement from above, but just refer to sheet3. However, this made my file size absolutely huge.

Do you know of another way to do this?

Thank you!

Kate

David M58

Here's some information you might find useful.

For example, let's suppose that the user has made the following entries on Sheet1 in column L and column O:


Let's suppose that Sheet2 contains the following data in column L:


For each item in column L on Sheet2, if you wanted to show the number of times that item appears on the Sheet1 list, but only counting those occurrences where an entry was made in column O on Sheet1, you could put the following array formula in cell K1 on Sheet2:


Remember to use Ctrl-Shift-Enter to enter array formulas (you do not type the { and } characters).

In the above example, cell K1 should show 1 since although Atlanta appears on the Sheet1 list twice (in L2 and L4), a corresponding entry in column O appears only once for Atlanta. The formula for cell K2 should return 2 since both Sheet1 entries for Baltimore have a corresponding entry in column O. The formula for cell K3 should return zero since Boston is not entered on Sheet1.

kate84

Thanks!