Multiple Dropdown lists (sheets) default to Public Variable

Otisair

New Member
Joined
Jan 31, 2014
Messages
16
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Jan 31, 2014
Messages
16
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
To exclude the sheets try
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

On second thought this would be better
VBA Code:
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
 
Solution

Otisair

New Member
Joined
Jan 31, 2014
Messages
16
I truly look forward to having such efficient code. Mine would have been 40 additional lines. Thank You Sincerely
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,865
Members
416,347
Latest member
AT2021

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
Top