Data Validation – List change

Topic

New Reply

Hi All,

I have a list of names in a list that is applied to data validation from a dynamic range “=OFFSET(Lookups!$C$2,0,0,COUNTA(Lookups!$C$2:$C$50),1)”

I apply this list in numerous places in a number of worksheets via data validation List.

The question is if I change a name in a dynamic range list (say from the word “Black” to “White”) is there any way that this change can be made to make global changes/updates across all worksheets?

I hope that makes sense.

Reagrds

Reply | Quote

Replies

It could be done with code. You’d have to loop through all validated cells, check if the cell value is the old value, and the validation formula is the one you’re looking for, and then update accordingly.

Reply | Quote

Thanks Rory

Sounds like a pretty complex task – may need to be the old manual way (?)

Regards

Reply | Quote

Hi Verada

..this example code will start you off:
Place your cellpointer on any cell that has that data validation list assigned, then run this code:

Code:

Sub updateValidationCells()

Selection.SpecialCells(xlCellTypeSameValidation).Replace _
    What:="black", Replacement:="white"
ActiveSheet.CircleInvalid

End Sub

You would need to repeat this for each applicable worksheet.

An example demo file is attached.
Don’t forget to place the cellpointer on a cell which has the data validation, before running the update vba code.

zeddy

Reply | Quote

Hi folks
I started playing as a learning exercise.
Is there any merit in the following approach?
This is not offered as a solution but more for comment as part of my learning. There will be bugs.

The intent is to loop through cells in the usedRange. (problem #1 I guess)
On each pass check for validation and if conditions are met substitute old value for new value.

As it stands the new value could fail the validation.

42153-validations

Code:

Sub Test()
CheckValidation "red", "green"
End Sub

Sub CheckValidation(sOldValue As String, sNewValue As String)
Dim oCell
For Each oCell In ActiveSheet.UsedRange
    oCell.Select
    With ActiveCell
        If isValidated And .Value = sOldValue Then .Value = sNewValue
    End With
Next
End Sub

Function isValidated()
Dim X As Variant
On Error Resume Next
isValidated = True
X = ActiveCell.Validation.Type ' eight types values 0 to 7
On Error GoTo 0
If IsEmpty(X) Then
    isValidated = False
End If
End Function

Comments are most welcome.
Regards
Geof

Reply | Quote

Hi
Changes made include

[*]Cycling through the Validated Cells collection. No longer using .UsedRange.
[*]Using 2 validation sources in the Lists sheet.

It should operate more quickly now.
Cheers
Geof

Reply | Quote

Hi Geof

Good progress so far.

Data Validation is a great tool, and is used frequently multiple times in Excel workbooks. There are often several validation rules in use on a sheet. My only reservation with your method is that it doesn’t take account of validation cells that may have the same selected value, but from different source lists. It is entirely possible that an entry might appear in two (or more) different lists. For example, you could have a ‘product-colour-list’ which includes ‘red’ as an allowed drop-down choice, and you could have an ‘alert- status-list’ that might also include ‘red’ as an allowed drop-down data validation choice. If you used your method, and changed the value in the ‘product-colour-list’ from ‘red’ to ‘rose-red’, then you could also mistakenly change cells that have ‘red’ as a valid choice from the ‘alert-status-list, to ‘rose-red’, which would now be an invalid choice.

This is why I was using the .SpecialCells(xlCellTypeSameValidation) in my sample routine. So a check should be made in the vba routine to test for the same relevant validation source.

Regarding invalid data circles: These circles will only appear when an entry in a cell does not match the current validation rule for the cell. This invalid entry could occur, for example, if you ‘paste-value’ a ‘disallowed value’ onto the cell that has a data validation rule. Or, as we are discussing here, if you subsequently change/remove entries in a data validation source-list, after selections have already been made from the original source-list.

For those who love to use the keyboard, you can use
[Alt][avi] ; to show circles around invalid-data cells (press [Alt] key, then [a] key, then [v] key, then key)
[Alt][avr] ; to clear circles
[Ctrl][G][Alt][Enter] ; to select all data-validation cells on the worksheet

The following routine will display how many cells on the worksheet have data-validation rules applied:

Code:

Sub showCount()
Set zSht = ActiveSheet
zCount = Cells.SpecialCells(xlCellTypeAllValidation).count
MsgBox zCount
End Sub

The following function returns the number of cells on the sheet that have ‘invalid’ data and displays the invalid cells with ‘red circles’ :

Code:

Function invalidCount()

Application.Volatile
Set zSht = ActiveSheet
zSht.ClearCircles
zShapeCountBefore = zSht.Shapes.count
zSht.CircleInvalid
zShapeCountAfter = zSht.Shapes.count

invalidCount = zShapeCountAfter - zShapeCountBefore

End Function

Add the code to a standard vba module.
To use the function, enter the formula =invalidCount() into any cell on a sheet that uses data validation. To test the function, copy-and-paste-values onto cells and overwrite to make the entries ‘invalid’.

zeddy

Reply | Quote

Hi Zeddy et al

This is my attempt to address the shortcoming that Zeddy identified whereby replacements were not context sensitive. This meant that the colour red would update in both the colour and status contexts. The latter being inappropriate.

The following line checks the validation formula for the appropriate table name in the validation source.

Code:

                [COLOR=#b22222]If InStr(ActiveCell.Validation.Formula1, sTblSrcName) Then[/COLOR]

The variable sTblSrcName is populated by the routine named getSrcTableName.

I am still having trouble implementing Cells.SpecialCells(xlCellTypeSameValidation). It does not seem to like the event trigger location.

Code:

Public sTblSrcName As String

Sub upDateValidation(sOldValue As String, sNewValue As String)
    Dim rng As Range
    Dim oCell As Range
    On Error GoTo ErrorTrap
    Application.ScreenUpdating = False
    getSrcTableName
    For i = 2 To Worksheets.Count
        Sheets(i).Activate
         '**Get all cells with validation set
        Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
'        Set rng = Cells.SpecialCells(xlCellTypeFormulas)
'        Set rng = Cells.SpecialCells(xlCellTypeSameValidation)

      On Error GoTo 0
            For Each oCell In rng
            oCell.Select
                oCell.Validation.IgnoreBlank = False
                'test tableName in validation formula
                [COLOR=#b22222]If InStr(ActiveCell.Validation.Formula1, sTblSrcName) Then[/COLOR]
                    If oCell.Value = sOldValue Then oCell.Value = sNewValue
                End If
            Next
    Next
    Set rng = Nothing
    MsgBox ("All Done")
    Exit Sub
ErrorTrap:
     MsgBox "There is an error " & Err.Description
     
End Sub

[COLOR=#b22222]Sub getSrcTableName()[/COLOR]
For i = 1 To ActiveSheet.ListObjects.Count
    If Not Intersect(ActiveCell, Range(ActiveSheet.ListObjects(i))) Is Nothing Then
        sTblSrcName = CStr(ActiveSheet.ListObjects(i).Name)
'        Debug.Print sTblSrcName
        'sTblSrcName = getSrcTableName
        Exit Sub
    End If
Next

End Sub

Thanks to Zeddy and Fred for their comments and assistance to date.

Geof

Reply | Quote

It shouldn’t be that hard to write. Is that formula entered literally into the DV source, or do you use a named range?

Reply | Quote

Hi Rory – thanks for the reply.

Yes is to a named range called “Projects” in a sheet called “lookup”

Regards

Reply | Quote

it would be MUCH easier to point the validation list at a named range then use VBA to define / redefine that named range.

Doing so once will then be picked up in all the validations that use it.

Reply | Quote

Verada,

I agree with Martin. Here is how I would set it up:

Create a named range:
42162-Datavalidation1

Select the cells you want then create the data validation:
42163-Datavalidation2

Any changes made to the validation list (add/delete/change) would reflect in all the applied cells

HTH,
Maud

Reply | Quote

Hi Maud

..perhaps you and Martin are missing the point that Verada raises?
It’s not about the best way to define a data validation list.
The question posed was about what happens to existing cells which have a currently valid selection from the data validation list (e.g. “Black”), when you subsequently make a change to that specific entry in the source list (e.g. changing the entry in the list from “Black” to “White”).

I find the best way is detect this is to use the ‘circle-invalid’ data option.
42165-z2

Geoff’s post#5 had the right approach, but he is probably unaware that in VBA, you can just use..
ActiveSheet.CircleInvalid

So, for cells that have an entry of ‘Black’ (as chosen from the ‘old’ data validation list), these will now be circled as ‘invalid’ i.e. no longer members of the current validation list (where ‘Black’ has been replaced by ‘White’)
42166-z1

I suspect that Verada wants all those ‘data-validation-Black’ entries to be automatically changed to ‘data-entry-White’ values, as per her example.
This could be done via vba as Rory suggests in post#2

zeddy

Reply | Quote

Hi folks
Thank you .. every day is a school day.

activesheet.circleInvalid is indeed new to me.

Geof

Reply | Quote

Hi Geof

..a school day for me too! Manawatu-Wanganui is my new favourite placename.

zeddy

Reply | Quote

Hi Zeddy,

I’m not sure what you meant by placing the cellpointer on a cell…

I put my cellpointer on a blank cell of Sheet2 and clicked your “Circle Invalid” button (after changing one of the colors in your Lookups sheet). I got the same circles I got as if I had followed your instructions.

3 other questions:
– what purpose do the named ranges “tempx” serve?
– on the Lookup sheet, what purpose does the formula in J3 serve?
– what does Sheet3 do, if anything?

Thks.

Fred

Reply | Quote

Hi Fred

You have forensic skills indeed.

For the [circle invalid] and [clear circles] buttons, it doesn’t matter where the cellpointer is.
But the routine that updates the validation cells requires that you start it with the cellpointer on a cell that uses the data validation that is being updated.
The routine Sub updateValidationCells() begins..
Selection.SpecialCells(xlCellTypeSameValidation)
..so the Selection part refers to the current cellpointer cell location. If it isn’t on a cell that uses the data validation, it won’t find any cells (i.e. that’s the purpose of the xlCellTypeSameValidation bit)
The named ranges serve no purpose – I was experimenting with the best method of selecting all the data validation cells – I just forgot to clear then out before posting the demo file.

Sheet3 was just another sheet with some sample data validation cells in slightly different locations. (Verada mentioned that multiple sheets were being used.) The idea was to show that you needed to run my update routine on each required sheet. A more general purpose routine could be used to do the replacement for all sheets in a workbook, but I left that for others to contribute.

– on the Lookup sheet, what purpose does the formula in J3 serve?

..in Verada’s post#1, there is a space between “5” and “0” in the formula:
“=OFFSET(Lookups!$C$2,0,0,COUNTA(Lookups!$C$2:$C$5 0),1)”
..I just copied it to any ol’ cell to fix the formula.

Good spot.

zeddy

Reply | Quote

Hi again

Dynamic Range Names & use of Offset()

Are there advantages to this function over the use of a range converted to a table?

A Data Validation rule could reference a table name instead

Something like this :- =INDIRECT(“Table2[Town]”)

To my aging eyes this is a lot easier to read.

Just wondering. Legacy issues?
Geof

Reply | Quote

zeddy,

Yes, I totally missed the point of what verada was asking. The question is not how changes affect the drop downs on choices to be made but rather on the now invalid choices that have already been made. Thanks for clarifying

Reply | Quote

Hi folks
The attached workbook is my attempt to automate the updating of validated cell contents in the event that you edit the validation source list.
I have used an Indirect() function against a list range converted to a table to maintain a dynamic range. I am trying this as an alternate method to using the Offset() function. The Indirect() formula in the validation is easier to read.
The scripts require that the worksheet named “Lists” remains at the first worksheet in the workbook.
There are two routines behind the worksheet named “Lists”.

[*]Worksheet_Change and
[*]Worksheet_selectionchange.

These routines capture the events and use the Intersect method to monitor changes. They capture values necessary as inputs to the upDateValidation routine in Module1.
These routines came from the microsoft support site.

Module1 contains two scripts

[*]upDateValidation() and
[*]isValidated()

IsValidated() tests whether cells are subject to data validation.

I have no idea if this is at all useful. It seems to work with a very small sample workbook. It has proved to be an interesting problem. On the face of it capturing selection changes and workbook changes could be annoying !!

Zeddy
I could not get the activesheet.circleInvalid to work for me !? Problem must be between the keyboard and the back of the chair.

Known Issues

[*]Deleting a row from the table data source.
[*]Deleting cell contents from table data source

Cheers
Geof

Reply | Quote

Me too

Reply | Quote

Hi Geof,

Looks good to me.

One question: why go to the “empty” sheet and why is it needed? I see your loop goes to Worksheets.Count and that you use the first sheet for the list, so you’re only examining Count-1 sheets for changes. Why not make the loop go 2 to Worksheets.Count-1 or every sheet of the sheet collection (I think this is a valid collection) whose name is not “Lists”?

Fred

Reply | Quote

Hi Fred
The empty sheet was only there to check for bugs if sheet was empty. It is not required at all. I forgot to delete it.

I cant get it to reliably mark invalid data with circles if I play around deleting items from the data table.

A blank cell updates in the validation list, and updates the validated cells to blank… most of the time.

Deleting a row from the table leaves the validated cells alone, the validation list updates for you to access with the drop list. But I cant mark the validated cell as invalid with .circleInvalid.
Which is why I haven’t used it.

As I said .. problem between the keyboard and back of my chair.
G

Cheers
Geof

Reply | Quote