Multiple Dropdown lists (sheets) default to Public Variable

[Otisair]

Hello, I have a workbook with 5 sheets. Each sheet (in Cell P1) has the same data validation dropdown list (Source=compname).
compname is a named range that includes (Alpha, Bravo, Charlie, Delta, Echo) I have also set a Public variable compset as String.

My intention is to get the Public variable reset anytime a choice is made from either of Sheets 1 - 5, so that all sheets drop down lists will automatically become selected, whatever the new compset value equals.

I know I can make cell P1 in sheets 2 - 5 = sheets1!$P$5, however, the user will not necessarily start their tasks on sheet 1. In fact, depending on which task they are working on, they may start on sheet 3 or sheet 2 and sheet 5, so I'm hoping to figure out how they can make one choice on whatever sheet they are working on, and the same company name will automatically be selected on the other 4 sheets without them having to manually select it. So, whenever there is a change to any of the sheets dropdown lists, that new selection becomes the Public Variables value. Hopefully I'm being clear.

I've been trying a variety of Worksheet Changes & Cell Changes, but it's not working out. Can someone assist please.

Thanks Muchly

 

[Fluff]

How about

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Dim Ws As Worksheet
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "P1" Then
      For Each Ws In Worksheets
         Application.EnableEvents = False
         Ws.Range("P1").Value = Target.Value
         Application.EnableEvents = True
      Next Ws
   End If
End Sub

This needs to go in the ThisWorkbook module

 

[Otisair]

Outstanding! Your suggestion works perfectly! I was looking in the wrong direction thinking I had to use a Global/Public Variable. Thank You So Much.

As a follow up ( and something I didn't think about including in my initial request), I have two data sheets that I'd like to exclude from having cell P1 changed, as they contain data sets that I'd like to keep static. I'm looking at various ways to (if Ws = "Filters2021", Or if Ws = "Data2021" Goto End) to exclude those two pages (Filters2021 and Data2021), that I'll figure out. You solved my request 100% and have helped me so much.

Thank You Kindly

 

[Fluff]

To exclude the sheets try

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Dim Ws As Worksheet
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "P1" Then
      For Each Ws In Worksheets
         Select Case Ws.name
            Case "Filters2021", "Data2021"
            Case Else
               Application.EnableEvents = False
               Ws.Range("P1").Value = Target.Value
               Application.EnableEvents = True
         End Select
      Next Ws
   End If
End Sub

 

[Fluff]

On second thought this would be better

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Dim Ws As Worksheet
   If Target.CountLarge > 1 Then Exit Sub
   If Sh.name = "Filters2021" Or Sh.name = "Data2021" Then Exit Sub
   If Target.Address(0, 0) = "P1" Then
      For Each Ws In Worksheets
         Select Case Ws.name
            Case "Filters2021", "Data2021"
            Case Else
               Application.EnableEvents = False
               Ws.Range("P1").Value = Target.Value
               Application.EnableEvents = True
         End Select
      Next Ws
   End If
End Sub

 

[Otisair]

I truly look forward to having such efficient code. Mine would have been 40 additional lines. Thank You Sincerely

 

[Fluff]

You're welcome & thanks for the feedback.