Dynamic Range

[Joanne]

Using MSOffice 2003

I have a ws named lists. Col A has my list that I am using in the data
validation technique to get drop boxes on my working spreadsheet. It is
working well as is, but I want the user to be able to add to the list.

When I add an item thru the drop box, it does not get added to the
column in the Lists ws.

I do have error alert unchecked in the data validation dialog box.

Here is my syntax for making the list dynamic - I suspect something is
incorrect here, but don't know what it is.

=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)

Thanks for your time
Joanne

 

[Debra Dalgleish]

You can use programming to add new items to the list. There's a sample
file here:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0012 - Update Validation List'

 

[Joanne]

Debra Dalgleish wrote:
I lifted the code right out of sample spreadsheet in DV0012 and pasted
it into my worksheet.
I changed Target.column to 1 instead of 3 because my data is in col A
I changed "NameList" to "JobDescription" where necessary because that is
the name of my range.
'Lists' is the name of my ws with my list on it so I left that alone.
Still, it will not add any new data to the list.
What am I missing here please?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lists")
If Target.Column = 1 And Target.Row > 1 Then
If Application.WorksheetFunction.CountIf(ws.Range("JobDescription"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("A" & i).Value = Target.Value
ws.Range("JobDescription").Sort Key1:=ws.Range("A1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If

End Sub

Thanks for your help - it is much appreciated
Joanne

 

[Debra Dalgleish]

Did you paste the code into the worksheet module for the sheet with the
data?
Are macros enabled in the workbook?

 

[Joanne]

Debra Dalgleish wrote:
Yes, the code is pasted in the worksheet 'invoices' which is where I am
using the data validation drop list technique. It is using the 'change'
declaration of the worksheet.
The sort for sending empty entries to the end of the list is pasted on
the 'lists' worksheet using 'SelectionChange' as declaration.

Should I have pasted these sub routines in the workbook instead of on
the individual sheets, or should I maybe have declared a new module and
put them in there?

Thanks for your help Debra
Joanne

 

[Debra Dalgleish]

The Change event code should be on the Invoices sheet, and it includes a
sort after the new item is added to the list. You shouldn't need a
separate procedure on the Lists sheet to sort the items.

Does the sample workbook work correctly when you try it?

 

[Joanne]

Debra Dalgleish wrote:

Yes, your sample app works beautifully.

I have the change event code on my 'lists' ws because that is also on
your 'lists' ws. I found it looking around at yours to see why the heck
mine doesn't work and yours does, and thinking that might be the
problem, I put it on my 'lists' ws.

Thanks