Dynamic drop down list with array source

On the beginning I would say it is not good solution. I’ve spent so many hours to improve my VBA code in the end chose simplest solution. Despite Excel will save data from array to sheet via drop down list and dynamic list will be removed is very likely list error will appear when workbook is opening.

Error says: „Removed Feature: Data validation from /xl/worksheets/…”. After my experiences to make proper code I think error will always appeared because array is not constant – it is saved in memory. When Excel is opening dynamic list is trying to get data from the source but source has not been created yet.

I’ve tried to remove dynamic list when Excel closed but even though list has been removed code still was pasted to VBA console. Even dynamic list is not running when Excel starts error is showing. It is strange for me.

The best solution and the simplest one is add source of dynamic drop down list in the Excel sheet. Error “Removed Feature: Data validation from /xl/worksheets/…’ is disappearing.

Adding  dynamic drop down list with array source:

Dim LastRow As Long

‘ Finding last row in needed range
ThisWorkbook.Sheets("SheetName").Select
With ThisWorkbook.Sheets("SheetName")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

' Cleaning previous dynamic drop down list, more columns than real list range
ThisWorkbook.Sheets("PSheetName").Range(Cells(10, 5), Cells(LastRow, 11)).Select
With Selection.Validation
   .Delete
   .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
   :=xlBetween
   .IgnoreBlank = True
   .InCellDropdown = True
   .ShowInput = True
   .ShowError = True
End With

' Adding dynamic drop down list to column E
ThisWorkbook.Sheets("Plik Wsadowy - Obrotówka").Range(Cells(10, 5), Cells(LastRow, 5)).Select
With Selection.Validation
   .Delete
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
   xlBetween, Formula1:=Join(AktywaPasywaArray(), ",")
   .IgnoreBlank = True
   .InCellDropdown = True
   .InputTitle = "Please choose item:"
   .ErrorTitle = "Wrong item:"
   .InputMessage = "Please choose item from the list."
   .ErrorMessage = "Typed item is not allowed."
   .ShowInput = True
   .ShowError = True
End With
End Sub

Cleaning drop down list:

' Cleaning previous dynamic drop down list, more columns than real list range
ThisWorkbook.Sheets("PSheetName").Range(Cells(10, 5), Cells(LastRow, 11)).Select
With Selection.Validation
   .Delete
   .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
   :=xlBetween
   .IgnoreBlank = True
   .InCellDropdown = True
   .ShowInput = True
   .ShowError = True
End With

Delete commas from drop down list. Source element can’t have commas:

'Source: https://stackoverflow.com/questions/30724178/create-data-validation-list-when-some-of-the-values-have-commas

Dim dList As String

dList = Range("B14").Value

'~~> Replace comma with a similar looking character
dList = Replace(dList, ",", Chr(130))

With Range("D14").Validation
   .Delete
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=dList
   .IgnoreBlank = True
   .InCellDropdown = True
   .InputTitle = ""
   .ErrorTitle = ""
   .InputMessage = ""
   .ErrorMessage = ""
   .ShowInput = True
   .ShowError = True
End With

193 thoughts on “Dynamic drop down list with array source

  1. Pingback: collision repair

  2. Pingback: http://144.91.94.12/

  3. Pingback: https://www.terrasmart.at/wp/wp-content/uploads/ed

  4. Pingback: https://wazobet.com/

  5. Pingback: ambien 5mg pill for overnight shipping

  6. Pingback: cach soi keo

  7. Pingback: order flexeril online in usa canada uk australia without prescription nextday shipping

  8. Pingback: danh de online uy tin

  9. Pingback: new-solarmovie

  10. Pingback: soi cau de kep

  11. Pingback: ty le chap bong da

  12. Pingback: french bulldog puppies for sale near me in CA ON MA CO OH PA SC MS TN FL UT NH VA AL TX

  13. Pingback: UNICC SHOP

  14. Pingback: game bai poker doi thuong

  15. Pingback: Hotels in Downtown Dallas TX

  16. Pingback: eatverts.com

  17. Pingback: thenaturalpenguin.com

  18. Pingback: Buy roxicodone online next day shipping for sale legally no script

  19. Pingback: he has a good point

  20. Pingback: Immediate Edge

  21. Pingback: bitcoin evolution review

  22. Pingback: Anita Bath

  23. Pingback: Buy California sunshine LSD 225ug blotter Tabs Online for sale near me in USA Canada UK Australia overnight delivery cheap

  24. Pingback: qiuqiu99

  25. Pingback: mơ thấy con mèo đánh con gì

  26. Pingback: nằm mơ thấy con rết là điềm gì

  27. Pingback: mơ thấy ao nước đánh con gì

  28. Pingback: mơ thấy xe tải

  29. Pingback: Pomskies for Sale

  30. Pingback: bichon frise puppies for sale near me

  31. Pingback: Pomeranian breeders

  32. Pingback: Parrots for Sale

  33. Pingback: 홀덤사이트

  34. Pingback: intanqq

  35. Pingback: SSCN BKN 2021 Kabupaten Serdang Bedagai

  36. Pingback: DevOps Strategy

  37. Pingback: Sex dolls for sale

  38. Pingback: Panzer Arms BP-12

  39. Pingback: con ruoi so may

  40. Pingback: mơ thấy gấu

  41. Pingback: trò chuyện với người nổi tiếng

  42. Pingback: mơ thấy kim cương

  43. Pingback: nằm mơ thấy con chí

  44. Pingback: nằm mơ thấy bị gãy răng

  45. Pingback: cung kim ngưu hợp với cung nào nhất

  46. Pingback: nhân mã khi thích ai đó

  47. Pingback: con số may mắn của cự giải

  48. Pingback: mơ thấy xương người

  49. Pingback: mơ bị đòi nợ đánh con gì

  50. Pingback: nữ thiên bình hợp với cung nào

  51. Pingback: cung bảo bình nữ hợp với cung nào

  52. Pingback: help with writing essay

  53. Pingback: custom essays writing

  54. Pingback: definition essay help

  55. Pingback: dissertation guidelines

  56. Pingback: essay writing service discount

  57. Pingback: where to buy a research paper

  58. Pingback: will someone write my paper for me

  59. Pingback: thesis editing service

  60. Pingback: thesis proposal writing service

  61. Pingback: online pharmacy

  62. Pingback: canadian pharmacy cialis

  63. Pingback: tadalafil for sale

  64. Pingback: cialis over the counter

  65. Pingback: how long does viagra stay in your system

  66. Pingback: where to buy viagra

  67. Pingback: can women take viagra

  68. Pingback: how does viagra work

  69. Pingback: canada pharmacy reviews

  70. Pingback: Gyne-Lotrimin

  71. Pingback: best price prescription drugs

  72. Pingback: viagra canada

  73. Pingback: viagra

  74. Pingback: cialis paypal bezahlen

  75. Pingback: cialis online no prescription australia

  76. Pingback: can i buy cialis in uk

  77. Pingback: cialis rush

  78. Pingback: original cialis low price

  79. Pingback: buy cialis online europe

  80. Pingback: how to buy cialis online from canada

  81. Pingback: ordering cialis online australia

  82. Pingback: monthly cost of cialis without insurance

  83. Pingback: xomiuqfj

  84. Pingback: buy cialis online at lowest price

  85. Pingback: viagra how to take

  86. Pingback: wat doet viagra precies

  87. Thanks for tutorial.
    I creted data validation list with VBA.
    Using the VBA code snippet, drop-down lists that filled with unique and the sorted alphabetically values can be created.
    The items of the drop-down lists can be populated in from the same sheet or from other sheet.
    When a modify is made to list items (add, delete, etc.), this modify can automatically be seen in the drop-down lists.

    https://eksi30.com/vba-dynamic-data-validation-list-with-unique-distinct-sorted-values/

  88. Pingback: how to use azithromycin for chlamydia

  89. Pingback: blue sildenafil with 88

  90. Pingback: what is cialis 5 mg used for

  91. Pingback: cheap viagra or cialis

  92. Pingback: buy viagra

  93. Pingback: viagra generico mejor precio

  94. Pingback: buy cialis tadalafil uk

  95. Pingback: best custom essay writing services

  96. Pingback: how to write a research introduction

  97. Pingback: i need someone to write my essay

  98. Pingback: essay help online

  99. Pingback: why is business ethics important free essay samples

  100. Pingback: can i buy cialis in uk

  101. Pingback: will my insurance cover viagra

  102. Pingback: can i buy cialis in uk

  103. Pingback: buy augmentin 1000 mg

  104. Pingback: furosemide 40 mg prices

  105. Pingback: azithromycin 1g buy online

  106. Pingback: stromectol

  107. Pingback: 2.5 albuterol

  108. Pingback: cialis pills

  109. Pingback: cialis wikipedia

  110. Pingback: 100 mg viagra

  111. Pingback: what is cialis

  112. Pingback: staxyn vs viagra

  113. Pingback: buy cialis

  114. Pingback: generic levitra

  115. Pingback: levitra vs cialis

  116. Pingback: cialis samples

  117. Pingback: viagra online purchase

  118. Pingback: what is doxycycline

  119. Pingback: prednisolone nursing implications

  120. Pingback: tadalafil tablets

  121. Pingback: clomid buy

  122. Pingback: 5mg cialis

  123. Pingback: sophia viagra nude

  124. Pingback: no prescription viagra

  125. Pingback: dapoxetine alza

  126. Pingback: sildenafil 100mg

  127. Pingback: diflucan for thrush

  128. Pingback: loviagraosn viagra

  129. Pingback: accidental viagra

  130. Pingback: synthroid and aspirin

  131. Pingback: alternatives to viagra

  132. Pingback: cialis viagra

  133. Pingback: professional thesis writing service

  134. Pingback: price propecia costco

  135. Pingback: sildenafil cost walmart

  136. Pingback: cialis vs viagra vs levitra how hard

  137. Pingback: cialis and melenona

  138. Pingback: cialis mexico

  139. Pingback: sildenafil citrate problems

  140. Pingback: are viagra generics safe

  141. Pingback: get viagra

  142. Pingback: viagra in panama city

  143. Pingback: viagra 100 mg best price

  144. Pingback: 35 mg viagra

  145. Pingback: viagra online donde comprar

  146. Pingback: cheapest sildenafil india

  147. Pingback: neurontin abuse potential

  148. Pingback: can i order cialis online in canada

  149. Pingback: cheap viagra prices south africa

  150. Pingback: metformin supplement

  151. Pingback: cheap generic tadalafil 5mg

  152. Pingback: cialis video

  153. Pingback: wellbutrin vs paxil

  154. Pingback: sanofi plaquenil coronavirus

  155. Pingback: cefdinir vs amoxicillin

  156. Pingback: will amoxicillin help a uti

  157. Pingback: order azithromycin

  158. Pingback: psoriatic arthritis celebrex

  159. Pingback: generic name for celebrex

  160. Pingback: keflex coupoons liquid

  161. Pingback: cephalexin 7 years old

  162. Pingback: duloxetine hcl ec

  163. Pingback: how dangerous is cymbalta

  164. Pingback: canada cialis online

  165. Pingback: buy cialis online best price

  166. Pingback: Zakhar Berkut

  167. Pingback: 4569987

  168. Pingback: buy cialis tadalafil tablets

  169. Pingback: news news news

  170. Pingback: buy tadalafil tablets

  171. Pingback: buy online viagra

  172. Pingback: psy

  173. Pingback: psy2022

  174. Pingback: projectio-freid

  175. Pingback: tinder Profile bio

  176. Pingback: cash advance lenders torrance

  177. Pingback: kinoteatrzarya.ru

  178. Pingback: topvideos

  179. Pingback: video

  180. Pingback: is sildenafil 20 mg the same as viagra?

  181. Pingback: afisha-kinoteatrov.ru

  182. Pingback: Ukrainskie-serialy

  183. Pingback: site

  184. Pingback: free dating south african dating sites

  185. Pingback: cialis 10mg reviews

  186. Pingback: cialis duration of action

  187. Pingback: cialis generic overnite

  188. Pingback: top

  189. Pingback: meijer online pharmacy

  190. Pingback: what's in cialis

  191. Pingback: pharmacy chains in canada

  192. Pingback: viagra online canadian pharmacy

  193. Pingback: comparison of 10 mg cialis price

Leave a Reply

Your email address will not be published. Required fields are marked *