VBA Create dynamic data validation which refers to another spreadsheet

mkh12ab

New Member
Joined
Apr 9, 2021
Messages
15
Office Version
  1. 2019
Platform
  1. Windows
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.
 
I think it need reference to which sheet. Try
Set rnglist = ws3.Range(ws3.Cells(2, ring + 2), ws3.Cells(eRow, ring + 2))
spot on, once again thanks i really appriciate the help. now i can almost see the finish line :)



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

' !!!! rng = "cells(ActiveCell, ActiveCell.Offset(100, 0))"

' Create named range for validation list.
eRow = ws1.Cells(2, ring + 2).End(xlDown).Row
Set rnglist = ws1.Range(ws1.Cells(2, ring + 2), ws1.Cells(eRow, ring + 2))
wb.Names.Add Name:=dropdownTekst, RefersTo:=rnglist



'indsæt columns for Projekt reference
Ark4.Visible = xlSheetVisible
Ark4.Activate
Columns(ring + 3).Insert
Range("C1").Select
ActiveCell.Offset(0, ring) = dropdownTekst

ws3.Range(ws3.Cells(1, ring)).Validation.Delete <--- Error as explained above.
ws3.Range(ws3.Cells(1, ring)).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=dropdownTekst <--- Error as explained above.
Ark4.Visible = xlSheetHidden

can you help me 1 last time?

Would love to know what i am doing wrong with the validation part?
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I get confused. What is Ark3 and Ark4. You set ws1 = Ark3 as if it is a worksheet. Since you defined it as ws1, why still refer it to Ark3 not just ws1.

You do not need to activate sheet unless it is really necessary because you may run into error. For example, ws1.Activate. Now you have ws1 active. Say you want to select a range in another sheet like ws2.Range("A1").Select you will get an error.

You can just refer Ark4.Columns(ring +3).insert
Ark4.Range("C1").Offset(0,ring) = dropdownTekst

instead of
Ark4.Activate
Columns(ring + 3).Insert
Range("C1").Select
ActiveCell.Offset(0, ring) = dropdownTekst

in my example
wb.Names.Add Name:="List1", RefersTo:=rngList
so
wb.Names.Add Name:="dropdownTekst", RefersTo:=rnglist

Formula1:="=List1"
so
Formula1:="=dropdownTekst"

I'm not sure if this solve the problem but try to correct the syntax first.
 
Upvote 0
I get confused. What is Ark3 and Ark4. You set ws1 = Ark3 as if it is a worksheet. Since you defined it as ws1, why still refer it to Ark3 not just ws1.

You do not need to activate sheet unless it is really necessary because you may run into error. For example, ws1.Activate. Now you have ws1 active. Say you want to select a range in another sheet like ws2.Range("A1").Select you will get an error.

You can just refer Ark4.Columns(ring +3).insert
Ark4.Range("C1").Offset(0,ring) = dropdownTekst

instead of
Ark4.Activate
Columns(ring + 3).Insert
Range("C1").Select
ActiveCell.Offset(0, ring) = dropdownTekst

in my example
wb.Names.Add Name:="List1", RefersTo:=rngList
so
wb.Names.Add Name:="dropdownTekst", RefersTo:=rnglist

Formula1:="=List1"
so
Formula1:="=dropdownTekst"

I'm not sure if this solve the problem but try to correct the syntax first.
thanks for the advice hope the below is a little less messy

Set wb = ActiveWorkbook
Set ws1 = Ark3
Set ws2 = Ark2
Set ws3 = Ark4

' Create named range for validation list.
eRow = ws1.Cells(2, ring + 2).End(xlDown).Row
Set rnglist = ws1.Range(ws1.Cells(2, ring + 2), ws1.Cells(eRow, ring + 2))
wb.Names.Add Name:=dropdownTekst, RefersTo:=rnglist

'indsæt columns and datavalidation for Ark4
ws3.Visible = xlSheetVisible
ws3.Columns(ring + 3).Insert
ws3.Range("C1").Offset(0, ring) = dropdownTekst

ws3.Range(ws3.Cells(1, ring)).Validation.Delete
ws3.Range(ws3.Cells(1, ring)).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=dropdownTekst


the error occours at the Validation part.
 
Upvote 0
thanks for the advice hope the below is a little less messy

Set wb = ActiveWorkbook
Set ws1 = Ark3
Set ws2 = Ark2
Set ws3 = Ark4

' Create named range for validation list.
eRow = ws1.Cells(2, ring + 2).End(xlDown).Row
Set rnglist = ws1.Range(ws1.Cells(2, ring + 2), ws1.Cells(eRow, ring + 2))
wb.Names.Add Name:=dropdownTekst, RefersTo:=rnglist

'indsæt columns and datavalidation for Ark4
ws3.Visible = xlSheetVisible
ws3.Columns(ring + 3).Insert
ws3.Range("C1").Offset(0, ring) = dropdownTekst

ws3.Range(ws3.Cells(1, ring)).Validation.Delete
ws3.Range(ws3.Cells(1, ring)).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=dropdownTekst


the error occours at the Validation part.
I know but did you try to put missing quote in the red code as mentioned

Formula1:="=dropdownTekst"

on this line
ws3.Range(ws3.Cells(1, ring)).Validation.Delete
I'm not sure why. I don't know if removing ws3 will solve like below

ws3.Range(Cells(1, ring)).Validation.Delete
ws3.Range(Cells(1, ring)).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=dropdownTekst"
 
Upvote 0
Solution
I know but did you try to put missing quote in the red code as mentioned

Formula1:="=dropdownTekst"

on this line
ws3.Range(ws3.Cells(1, ring)).Validation.Delete
I'm not sure why. I don't know if removing ws3 will solve like below

ws3.Range(Cells(1, ring)).Validation.Delete
ws3.Range(Cells(1, ring)).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=dropdownTekst"
i have made it so far so i now can run the full macro.

Set wb = ActiveWorkbook
Set ws1 = Ark3
Set ws2 = Ark2
Set ws3 = Ark4

' Create named range for validation list.
eRow = ws1.Cells(3, ring + 2).End(xlDown).Row
Set rnglist = ws1.Range(ws1.Cells(3, ring + 2), ws1.Cells(eRow, ring + 2))
wb.Names.Add Name:=dropdownTekst, RefersTo:=rnglist

'indsæt columns for Projekt reference
ws3.Visible = xlSheetVisible
ws3.Columns(ring + 3).Insert
ws3.Range("C1").Offset(0, ring) = dropdownTekst

ws3.Range("C1").Offset(1, ring).Validation.Delete
ws3.Range("C1").Offset(1, ring).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=dropdownTekst
ws3.Visible = xlSheetHidden

then i run the macro i get 1 option in my dropdown, which is = to the text in DropdownTekst.
is it possible to make the data a list like in rnglist??
 
Upvote 0
I know but did you try to put missing quote in the red code as mentioned

Formula1:="=dropdownTekst"

on this line
ws3.Range(ws3.Cells(1, ring)).Validation.Delete
I'm not sure why. I don't know if removing ws3 will solve like below

ws3.Range(Cells(1, ring)).Validation.Delete
ws3.Range(Cells(1, ring)).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=dropdownTekst"
Hi Zot
got the solution

ws3.Range("C1").Offset(1, ring).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=" & dropdownTekst

i really appriciated your help through this :D thanks alot for all the assist
 
Upvote 0
Good to hear that you got it solved
 
Upvote 0
Can make it dynamic like this
eRow = ws2.Range("B1").End(xlDown).Row

VBA Code:
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
Maybe it is from a year ago, but the article is very helpful, and easy to understand. Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top