VBA Create dynamic data validation which refers to another spreadsheet

[mkh12ab]

Hi i am trying to creat a datavalidation in VBA.

i am creating 2 new columns in my reference spreadsheet there i need the data from. (Sheet2) Columns unknow --> rows should be excel down (i guess name magement would be recommended)
i would like the dropdown list to be danymic since more data could be added on later.

the datavalidation or dropdown list should be shown on my front page (Sheet1)

Any suggestions and/or help would be highly appriciated.

 

[Zot]

The code will create Validation List in range A1 in Sheet1 and get the validation list in Sheet2. I use Named Range for the list and you can define the list location in this line
Set rngList = ws2.Range("B1", "B5")

Sub DataValid()

Dim rngList As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

' Create named range for validation list.
Set rngList = ws2.Range("B1", "B5")
wb.Names.Add Name:="List1", RefersTo:=rngList

' Clear location of any Data Validation
ws1.Range("A1").Validation.Delete
ws1.Range("A1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=List1"

End Sub

 

[mkh12ab]

Thanks for the fast reply. somehow i guess i am not managin it right? rngList = ws2.range("B1", "B5") i need to do dynamic?

any ideas?

 

[Zot]

Can make it dynamic like this
eRow = ws2.Range("B1").End(xlDown).Row

Sub DataValid()

Dim eRow As Long
Dim rngList As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

' Create named range for validation list.
eRow = ws2.Range("B1").End(xlDown).Row
Set rngList = ws2.Range("B1", "B" & eRow)
wb.Names.Add Name:="List1", RefersTo:=rngList

' Clear location of any Data Validation
ws1.Range("A1").Validation.Delete
ws1.Range("A1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=List1"

End Sub

 

[mkh12ab]

Godmorning and once again thanks for the reply

atm i have following:

Columns(((ring - 1) * 2) + 1).Insert
Columns(((ring - 1) * 2) + 1).Insert

is it possible somehow to make:

eRow = ws2.Range("B1").End(xlDown).Row
Set rngList = ws2.Range("B1", "B" & eRow)

Refering to --> Active.Columns.offset(ring)
so instead of being locked at column B i can get the dropdown at the new columns?

 

[Zot]

What you meant by locked? If you want to have dynamic number of rows in different column, just change the B with new column letter.

Looks like you have number as column reference. Instead if Range("B1").End(xlDown).Row you can also use Cells(1,2).End(xlDown).Row where 1 refers to row1 and 2 refers as column2 (which is B. Can also be written as Cells(1,"B"))

 

[mkh12ab]

What you meant by locked? If you want to have dynamic number of rows in different column, just change the B with new column letter.

Looks like you have number as column reference. Instead if Range("B1").End(xlDown).Row you can also use Cells(1,2).End(xlDown).Row where 1 refers to row1 and 2 refers as column2 (which is B. Can also be written as Cells(1,"B"))

Hi Zot

thanks for the answer. sorry if i am making it a bit confusing but i am not used to work alot with VBA or english.

i guess this is exactly what i am looking for. hope i once more can ask for your assist?
having a little issue with following line in VBA

Set rnglist = ws2.Cells(2, ring + 1, eRow, ring + 1)

The message i get is:
Compile error
Wrong number of arguments or invalid property assignment.

 

[Zot]

Cells is to defined a single range. You can define range like this too. Try this

ws2.Range(Cells(2,ring+1),Cells(eRow,ring+1))

 

[mkh12ab]

Cells is to defined a single range. You can define range like this too. Try this

ws2.Range(Cells(2,ring+1),Cells(eRow,ring+1))

thanks good to know

i get a feeling that i am getting close, but right now i am getting an error.
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

any ideas? codes is as shown below.

Set wb = ActiveWorkbook
Set ws1 = Ark3
Set ws2 = wb.Sheets("Skema")
Set ws3 = Ark4

' Create named range for validation list.

eRow = ws3.Cells(2, ring + 2).End(xlDown).Row
Set rnglist = ws3.Range(Cells(2, ring + 2), Cells(eRow, ring + 2)) <--- Error as explained above.
wb.Names.Add Name:=dropdownTekst, RefersTo:=rnglist

 

[Zot]

I think it need reference to which sheet. Try
Set rnglist = ws3.Range(ws3.Cells(2, ring + 2), ws3.Cells(eRow, ring + 2))