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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Solution
I truly look forward to having such efficient code. Mine would have been 40 additional lines. Thank You Sincerely
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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