Data Validation List, Multiple Sheets. Update one, update them all

[DragonzBreath]

Hi Guys,

I'm writing a budget spreadsheet. I have the same data validation list on multiple sheets in a workbook. I would like to be able to update the DVL on any one of the worksheets and have it update/mirror on all the other worksheets. I've googled for this and get nothing close to what I'm after. Perhaps my search terms are garbage, I don't know.

Is anyone able to help with the VB code to facilitate this?

Also, I have a pivot table which has the average number of days in a given time period. i.e. a month is 30.42 days (365/12), a quarter is 91.25 days (365/4)
I utilise VLOOKUP to work out costs of bills, by working them back to a daily cost and then multiplying them by the number of days in a give period based on the DVL in the above question.
Is there a more simple way to do this? i.e. does excel have a built in function for working out the average number of days in a month/quarter that doesn't require referencing a pivot table?

Thank you for reading, and thank you in advance for any input

DB

 

[DragonzBreath]

Yeah I just tried it now... But it results in a runtime error 1004. No cells were found.

Debugging highlights " For Each wCell In ws.Cells.SpecialCells(xlCellTypeAllValidation)"

EDIT: Also there are many lists that reference this same Data Validation List, so it'll probably update all of them, no?

 

[DanteAmor]

The macro will update all the relative lists:

I'm referring to the drop down itself, I drop the list choose a time period and the spreadsheet updates all the values. That's what I want reflected/mirrored across all tabs sheets.

The error is caused because you have sheets without validation lists.

Then only on the sheets that have a validation list in cell C2?

Try this:

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address(False, False) <> "C2" Then Exit Sub
    
    Dim ws As Worksheet
    Dim myVal As String, wcuenta As String
    '
    If Not Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        myVal = Target.Value
        Application.EnableEvents = False
        For Each ws In Sheets
            wcuenta = 0
            On Error Resume Next
            wcuenta = ws.Range("C2").SpecialCells(xlCellTypeSameValidation).Count
            On Error GoTo 0
            If wcuenta > 0 Then
                ws.Range("C2").Value = myVal
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

 

[DragonzBreath]

Thanks DanteAmor.

I copied that code only to the sheets with the ilst on cell C2 and nothing happens when I change the drop down. Cell C2 on the other sheets doesn't update

 

[DragonzBreath]

The macro will update all the relative lists:



The error is caused because you have sheets without validation lists.

Then only on the sheets that have a validation list in cell C2?

Try this:

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address(False, False) <> "C2" Then Exit Sub
    
    Dim ws As Worksheet
    Dim myVal As String, wcuenta As String
    '
    If Not Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        myVal = Target.Value
        Application.EnableEvents = False
        For Each ws In Sheets
            wcuenta = 0
            On Error Resume Next
            wcuenta = ws.Range("C2").SpecialCells(xlCellTypeSameValidation).Count
            On Error GoTo 0
            If wcuenta > 0 Then
                ws.Range("C2").Value = myVal
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

Legend!!!! That worked PERFECTLY! Thank you SO much

 

[DragonzBreath]

Is there a way for this code to work while the sheets are protected?

 

[DanteAmor]

Is there a way for this code to work while the sheets are protected?

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address(False, False) <> "C2" Then Exit Sub
    
    Dim ws As Worksheet
    Dim myVal As String, wcuenta As String
    '
    If Not Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        myVal = Target.Value
        Application.EnableEvents = False
        For Each ws In Sheets
            wcuenta = 0
            On Error Resume Next
            wcuenta = ws.Range("C2").SpecialCells(xlCellTypeSameValidation).Count
            On Error GoTo 0
            If wcuenta > 0 Then
                ws.unprotect "abc"
                ws.Range("C2").Value = myVal
                ws.protect "abc"
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

Change "abc" for your password

 

[DragonzBreath]

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Address(False, False) <> "C2" Then Exit Sub
    
    Dim ws As Worksheet
    Dim myVal As String, wcuenta As String
    '
    If Not Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
        myVal = Target.Value
        Application.EnableEvents = False
        For Each ws In Sheets
            wcuenta = 0
            On Error Resume Next
            wcuenta = ws.Range("C2").SpecialCells(xlCellTypeSameValidation).Count
            On Error GoTo 0
            If wcuenta > 0 Then
                ws.unprotect "abc"
                ws.Range("C2").Value = myVal
                ws.protect "abc"
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

Change "abc" for your password

Thanks DanteAmor, this code didn't work however. I even tried protecting my sheet with the password abc to ensure I didn't screw anything up

 

[DragonzBreath]

The code works if I protect the entire workbook. This works for me. Thank you again DanteAmor!

 

[DanteAmor]

You're welcome!

 

[jadams121]

Good Afternoon,

I have a similar issue to dragonzbreath; unfortunately myknowledge of VBA is very limited. I am very versed in excel, so I have noissues there.
I would like to select my “yes/no” validation list on sheet1, cell A1 and then in turn it will update all of my tabs to “yes” from afilter list in cell A1 (in all 270 of my sheets). This is to consolidate eachreport page (each tab is using the filter sort in cell A1). I would like tofigure this out, because otherwise I have to select 270 tabs and select “yes”. As far as I can tell, there isn’t an easy wayto do this in excel, unless you use some coding. If you have a better or moreefficient way of achieving this, I’m all ears!

Please advise, if you require further information