I have a problem with the data validation with lists in Excel.
Please have a look at my sample spreadsheet and my code.
The workbook has two sheets: name and customer.
I defined a name (called name) in name and I used data validation with lists in the sheet 'customer'. I set the data validation to accept data from name.only. So far, so good.
It can happen that I want to change the name of a customer, or whathever is in a name. This is, after all, only an example. Unfortunately, once I've changed the value, it is not automatically updated in the the lists. I have to go through each cell, or in this example: row, that contains data validation/list and change the value manually by selecting it from the pulldown menu. That's ok if I only have a few records, but it will be cumbersome once the spreadsheet is populated with a lot of data.
I decided to write a VBA code which does this job, and it works. I put the code into the worksheet name. But there is a bug: Every time I add a new name in name, the new value is added to the first empty row and each row till number 65536. Annoying.
Any ideas what I'm doing wrong? How would you fix this?
Thanks a lot
Option ExplicitDim strName As StringPrivate Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("Name")) Is Nothing Then If Target.Count = 1 Then strName = Target End IfEnd SubPrivate Sub Worksheet_Change(ByVal Target As Range) Dim rngZelle As Range Dim strStart As String Dim strNeu As String If Not Intersect(Target, Range("Name")) Is Nothing Then If Target.Count = 1 Then strNeu = Target With Worksheets("Customer").Columns(1) Set rngZelle = .Find(strName, lookat:=xlWhole) If Not rngZelle Is Nothing Then strStart = rngZelle.Address Do .Cells(rngZelle.Row, 1) = strNeu Set rngZelle = .FindNext(rngZelle) If rngZelle Is Nothing Then Exit Do Loop While Not rngZelle Is Nothing And rngZelle.Address <> strStart End If End With End If End If End Sub
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.