List is not updating automatically

[HaroonSid]

dear sir
there is a sample file

in this file my data validation list is not sorting automatically

[apo]

Hi..

I am not exactly sure what you want..

But you could start by changing the formula in the NAME_SID Named Range to:

=OFFSET(ENTRY!$C$10,0,0,COUNTA(ENTRY!$C:$C)-6,1)

This will make it dynamic so there are no blank spaces at the bottom of your Validation List..

[HaroonSid]

apo
i don't want to remove space at the bottom of my validation list

i want to update sort my list

take a look at the sample and add a entry in c62.......the massage box will ask for yes or no,.....if u click on ok.........that name will add in data validation list C10:C59.....and should be sort according to A to Z

thanx for reply

[apo]

Its because your Events are disable in the Worksheet_Change Event when the new value is added (via the confirmation in your messagebox).. in your range (C10:C59)..

Therefore.. the sorting code is not triggered..

btw.. you should still change the formula in your named range to make it dynamic.. who wants a heap of blanks at the bottom of a combo..?

[HaroonSid]

dear apo

i will us formula in my named range to make it dynamic

but how i enable events in the worksheet_change..............i want to sorting code is triggered

plz help

[apo]

Hi..

You just needed to re-enable Events when your new value is inserted into the range("C10:C59").. this then in turn fires your sorting code..

See attached..

[HaroonSid]

Thanx u so much

apo
and
http://www.excelforum.com/
this is working perfectly............that's what i want...