Solved

Update Values in Excel Data Validation

Posted on 2011-03-09
4
333 Views
Last Modified: 2012-05-11
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






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

Open in new window

Data-Validation.xls
Name.png
dropdown.jpg
Adding-a-new-name1.jpg
Adding-a-new-name2.jpg
0
Comment
Question by:Massimo Scola
  • 2
4 Comments
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 100 total points
ID: 35088653
Just stepping back one.
If you are adding to a named range, you can have it automatically extend by inserting a cell within the range.  however, if you insert at the end, it doesn't seem to recognize that it's part of the range so I usually insert just above the end then move the last item up and add me new item (or always leave the last cell in the named range blank.)

For example, named range "name"="A1:A3":

A1="item1"
A2="item2"
A3="item3"

If you simply add something to A4, it's not included in the range.  however, if you insert at A3, you get this (just with the insert and shift cells down):
"name"="A1:A4":

A1="item1"
A2="item2"
A3=""
A4="item3"

Now you copy A4 to A3 and put your new info into A4.  Your name range is now updated without changing anything else.

--
similarly if you start with "name"="A1:A4":

A1="item1"
A2="item2"
A3="item3"
A4=""

then go to A4 and insert cell down and you get:

"name="A1:A5"
A1="item1"
A2="item2"
A3="item3"
A4=""
A5=""

Now it's even easier since you just put your new value into A4.  The only issue is that you have a blank entry at the end of your named range, but I think you can tell it to ignore empty cells when you use this in a data validation list.
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 400 total points
ID: 35088685
What if you change code as per below. Also more efficient to use a dynamic named range: define "name" as =OFFSET(Name!$A$1,1,0,COUNTA(Name!$A:$A)-1,1)
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
               Do
                  .Cells(rngZelle.Row, 1) = strNeu
                  Set rngZelle = .FindNext(Target)
               Loop While Not rngZelle Is Nothing
            End If
         End With
      End If
   End If
   End Sub

Open in new window

0
 

Author Closing Comment

by:Massimo Scola
ID: 35089176
fantastic!
0
 

Author Comment

by:Massimo Scola
ID: 35089180
StephenJR: =OFFSET(Name!$A$1,1,0,COUNTA(Name!$A:$A)-1,1) is indeed the most efficient way!

Thanks a lot to both of you guys

Massimo
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA: create a write log procedure 8 63
Formula returning #N/A 9 31
Checking references in VBA 3 20
Slicers by Groups in Excel 7 16
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Whatever be the reason, if you are working on web development side,  you will need day-today validation codes like email validation, date validation , IP address validation, phone validation on any of the edit page or say at the time of registration…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question