Excel 2010: dynamic update of drop down list based upon datasource validation worksheet changes
- by hornetbzz
I have one worksheet for setting up the data sources of multiple data validation lists.
in other words, I'm using this worksheet to provide drop down lists to multiple other worksheets.
I need to dynamically update all worksheets upon any of a single or several changes on the data source worksheet. I may understand this should come with event macro over the entire workbook.
My question is how to achieve this keeping the "OFFSET" formula across the whole workbook ?
Thx
To support my question, I put the piece of code that I'm trying to get it working :
Provided the following informations :
I'm using such a formula for a pseudo dynamic update of the drop down
lists, for example :
=OFFSET(MyDataSourceSheet!$O$2;0;0;COUNTA(MyDataSourceSheet!O:O)-1)
I looked into the pearson book event chapter but I'm too noob
for this.
I understand this macro and implemented it
successfully as a test with the drop down list on the same worksheet
as the data source. My point is that I don't know how to deploy this
over a complete workbook.
Macro related to the datasource worksheet :
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' Macro to update all worksheets with drop down list referenced upon
' this data source worksheet, base on ref names
Dim cell As Range
Dim isect As Range
Dim vOldValue As Variant, vNewValue As Variant
Dim dvLists(1 To 6) As String 'data validation area
Dim OneValidationListName As Variant
dvLists(1) = "mylist1"
dvLists(2) = "mylist2"
dvLists(3) = "mylist3"
dvLists(4) = "mylist4"
dvLists(5) = "mylist5"
dvLists(6) = "mylist6"
On Error GoTo errorHandler
For Each OneValidationListName In dvLists
'Set isect = Application.Intersect(Target, ThisWorkbook.Names("STEP").RefersToRange)
Set isect = Application.Intersect(Target, ThisWorkbook.Names(OneValidationListName).RefersToRange)
' If a change occured in the source data sheet
If Not isect Is Nothing Then
' Prevent infinite loops
Application.EnableEvents = False
' Get previous value of this cell
With Target
vNewValue = .Value
Application.Undo
vOldValue = .Value
.Value = vNewValue
End With
' LOCAL dropdown lists : For every cell with validation
For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
With cell
' If it has list validation AND the validation formula matches AND the value is the old value
If .Validation.Type = 3 And .Validation.Formula1 = "=" & OneValidationListName And .Value = vOldValue Then
' Debug
' MsgBox "Address: " & Target.Address
' Change the cell value
cell.Value = vNewValue
End If
End With
Next cell
' Call to other worksheets update macros
Call Sheets(5).UpdateDropDownList(vOldValue, vNewValue)
' GoTo NowGetOut
Application.EnableEvents = True
End If
Next OneValidationListName
NowGetOut:
Application.EnableEvents = True
Exit Sub
errorHandler:
MsgBox "Err " & Err.Number & " : " & Err.Description
Resume NowGetOut
End Sub
Macro UpdateDropDownList related to the destination worksheet :
Sub UpdateDropDownList(Optional vOldValue As Variant, Optional vNewValue As Variant)
' Debug
MsgBox "Received info for update : " & vNewValue
' For every cell with validation
For Each cell In Me.UsedRange.SpecialCells(xlCellTypeAllValidation)
With cell
' If it has list validation AND the validation formula matches AND the value is the old value
' If .Validation.Type = 3 And .Value = vOldValue Then
If .Validation.Type = 3 And .Value = vOldValue Then
' Change the cell value
cell.Value = vNewValue
End If
End With
Next cell
End Sub
