create dynamic ddbox in excel with vb

[john gault]

i am a noob.

need a pointer to create a ddbox on the fly, based on reading and sorting a list
I have to sort and removal of duplicates routine, but need to be able to dynamically size and create the ddbox. not on a sheet with linked cells.
can it be done?

thanks in advance

[peejay]

Try this,
I have a workbook with 2 sheets on it.
Sheet1 is called Database, sheet2 is called Reviewdata

On Database sheet
I input a list of names into column a. (which can be added to or subtracted from at ay time)

I have defined a variable called “namelist” as follows =database!$a$10:$a$1000
(this is the original definition & is not relevant to the use of the list later)
(insert--------names--------define)

I have the following formula in cell A3 ------ =”a10” (this cell is then hidden)

I have the following formula in A4 --------- ="A"&COUNTA(A10:A1000)+10-1 (this cell is then hidden)
(this will find out where the bottom of the list is (max =1000) and use this position later

On reviewdata sheet
I have done a data validation cell in A3 using -------- allow list, source =namelist
---- ensure “in cell drop down” & “ignore blanks” check boxes are checked.

Then I have the following code on the reviewdata sheet activation -------- right click the sheet tab and view code. Select Activate in right hand drop down box.


Private Sub Worksheet_Activate()

Dim namelist As Range
Dim namelist1
Dim topname, btmname

topname = Worksheets("database").Range("a3").Value
btmname = Worksheets("database").Range("a4").Value
namelist1 = topname & ":" & btmname

Worksheets("database").Range(namelist1).Name = "namelist"

Range("a1").Select

End Sub

This sub re-defines the range “namelist” used as the data validation EVERY time the sheet is activated. Therefore if the basic data is on another sheet you cannot

Hope this works for you, I know it could be tidier but its working for me so I moved on and left it as it is for now.