Dynamic Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joanne - 31 Dec 2007 01:51 GMT

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

Reply to this Message

Debra Dalgleish - 31 Dec 2007 02:06 GMT

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'

> Using MSOffice 2003
>
[quoted text clipped - 14 lines]
> Thanks for your time
> Joanne

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Joanne - 31 Dec 2007 14:33 GMT

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

> You can use programming to add new items to the list. There's a sample
> file here:
[quoted text clipped - 21 lines]
> > Thanks for your time
> > Joanne

Reply to this Message

Debra Dalgleish - 31 Dec 2007 14:39 GMT

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

> I lifted the code right out of sample spreadsheet in DV0012 and pasted
> it into my worksheet.
[quoted text clipped - 55 lines]
>>>Thanks for your time
>>>Joanne

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Joanne - 31 Dec 2007 19:28 GMT

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

> Did you paste the code into the worksheet module for the sheet with the
> data?
[quoted text clipped - 59 lines]
> >>>Thanks for your time
> >>>Joanne

Reply to this Message

Debra Dalgleish - 31 Dec 2007 19:51 GMT

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?

> 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'
[quoted text clipped - 72 lines]
>>>>>Thanks for your time
>>>>>Joanne

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Joanne - 31 Dec 2007 21:52 GMT

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

> 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
[quoted text clipped - 78 lines]
> >>>>>Thanks for your time
> >>>>>Joanne

Reply to this Message