Help Fix this spreadsheet

  • Thread starter Thread starter nc
  • Start date Start date
N

nc

http://www.contextures.com/DataValListAddSort.xls

The following problems are,

1. When adding a new item in the list on the "Data
Validation" sheet, an item of the "NameList" on
the "Lists" sheet is deleted, so that the dropdown list
items is always six.

2. When a dropdown list item on "Data Validation" is
subsequently deleted, items of the list on the "Lists"
sheet are deleted.

Thanks.
 
Hi

to fix number 1

add the following line of code in

your code: i = ws.Cells(Rows.Count, 1).End(xlUp).Row
add this in on the next line: i = i + 1

i can't get the problem mentioned in number 2 to occur

Hope this helps
Cheers
JulieD
 
Thanks loads Julie.

When I add your fix to the code the number 2 problem does
not occur.

If I wanted to delete an item on the "Lists" sheet, what
code do I need to add so that this list is resorted so
that no blanks appears on the dropdown list on the "Data
Validation" sheet.

Cheers.
 
Hi nc

i'm having trouble with automating the resort ... the code for the resort is
quite straight forward, but i don't know where to put it so that the resort
happens automatically if a fruit is deleted.
***code for resort
Application.ScreenUpdating = False
Sheets("Lists").Select
Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
Application.ScreenUpdating = True

***end code

but if i put it on the worksheet_change event it is called when you add a
piece of fruit and doesn't work because the sheet doesn't have focus ...
maybe someone else can assist with this problem.

Cheers
JulieD
 
If you put it behind the worksheet, you'll have to fully qualify your ranges.

columns("a:a") generally refers to column A of the activesheet, but when it's
behind the worksheet, it refers to the sheet owning the code.

Application.ScreenUpdating = False
with Sheets("Lists").columns("a:a")
.sort Key1:=.cells(1), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with
Application.ScreenUpdating = True

(I eliminated some of the .selects. And by using the With/End with, I could
qualify the range:

.cells(1) belongs to the previous With
--here, it's sheets("lists").columns("a:a") (same as A1)
 
I had a name problem (#Ref!) with Debra Dalgleish's sample workbook (and she'll
pay for that <vbg>), but after I fixed that, it worked fine.

Did you alter more of the code?

Namelist was changed from:
=OFFSET(Lists!#REF!,0,0,COUNTA(Lists!$A:$A),1)
to
=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)
 
Thanks! The cheque is in the mail.

Dave said:
I had a name problem (#Ref!) with Debra Dalgleish's sample workbook (and she'll
pay for that <vbg>), but after I fixed that, it worked fine.

Did you alter more of the code?

Namelist was changed from:
=OFFSET(Lists!#REF!,0,0,COUNTA(Lists!$A:$A),1)
to
=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)
Click to expand...
 
I'm not sure what changed, but maybe the OP can grab a fresh copy to see if
works the way he or she wants.
 
I fixed the broken name, and deleted an unused module.

Dave said:
I'm not sure what changed, but maybe the OP can grab a fresh copy to see if
works the way he or she wants.
Click to expand...
 
Dave

Thanks. I found that the easiet way was just to delete
the cell using shift cell up or delete the whole row.
 
Back
Top