Massimo Scola
asked on
Update Values in Excel Data Validation
Hello everyone
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
Massimo
Name.png
dropdown.jpg
Adding-a-new-name1.jpg
Adding-a-new-name2.jpg
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
Massimo
Option Explicit
Dim strName As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("Name")) Is Nothing Then
If Target.Count = 1 Then strName = Target
End If
End Sub
Private 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
Data-Validation.xlsName.png
dropdown.jpg
Adding-a-new-name1.jpg
Adding-a-new-name2.jpg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
StephenJR: =OFFSET(Name!$A$1,1,0,COUN TA(Name!$A :$A)-1,1) is indeed the most efficient way!
Thanks a lot to both of you guys
Massimo
Thanks a lot to both of you guys
Massimo
ASKER