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

DragonzBreath

New Member
Joined
Mar 2, 2019
Messages
11
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
 
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?
 
Last edited:
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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:

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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:

Code:
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
 
Upvote 0
Is there a way for this code to work while the sheets are protected?
Code:
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
 
Upvote 0
Code:
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
 
Upvote 0
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

 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top