VBA Code to Hide/Unhide Separate Named Tables Based on Drop Down Menu

Joined
Dec 8, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
For this example we have 3 agents (Agent 1, Agent 2, Agent 3) listed drop-down menu. When their name is selected from the drop-down the monthly table (the green one) will auto populate their info. Below this there are 3 additional tables: One for each Agent showing their stats month by month.
I need a VBA code to HIDE those extra tables, and only show the month to month table for the Agent who is selected from the drop down. When drop-down is blank all tables can be hidden or all shown.

Also is there a way to code the Month drop-down to automatically fill the data into it's matching month column?

stat sheet.xlsm
ABCDEFGHIJKLMN
1Select Agent
2Agent_2
3Select Month
4
5DPSMonthTotal Points Possible
6Agent_20100
7Measured StatMonth End Average/TotalPoints EarnedQualifyingPoint RangeBonus*****
8Attendance216100-90$ 350.00Top
9Unavailable AVG19.00%2389.9-80$ 250.002nd
10QA AVG100.00%3079.9-70/No BonusStill passing
11Survey Avg4.64
12Hold/ACW AVG2.19%20
13Total Points93
14
15
16Bonus#N/A
17
18
19Agent 1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
20Attendance Score000000000000
21Attenadance Points000000000000
22Unavailable Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
23Unavailable Points000000000000
24QA Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
25QA Points000000000000
26Survey Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
27Survey Points000000000000
28Hold/ACW Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
29Hold/ACW Points000000000000
30EOM Total Points000000000000
31EOM Stat AVG0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
32EOM BONUS$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -
33
34Agent 2JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
35Attendance Score000000000000
36Attenadance Points000000000000
37Unavailable Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
38Unavailable Points000000000000
39QA Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
40QA Points000000000000
41Survey Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
42Survey Points000000000000
43Hold/ACW Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
44Hold/ACW Points000000000000
45EOM Total Points000000000000
46EOM Stat AVG0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
47EOM BONUS$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -
48
49Agent 3JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
50Attendance Score000000000000
51Attenadance Points000000000000
52Unavailable Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
53Unavailable Points000000000000
54QA Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
55QA Points000000000000
56Survey Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
57Survey Points000000000000
58Hold/ACW Avg0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
59Hold/ACW Points000000000000
60EOM Total Points000000000000
61EOM Stat AVG0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%0.00%
62EOM BONUS$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -$ -
DPS_MailOut
Cell Formulas
RangeFormula
B6B6=D2
C6C6=$D$4
D6D6=XLOOKUP(B6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$N:$N,0)
C8C8=XLOOKUP($B$6,Worksheet_C_Overall!$C$11:$C$13,Worksheet_C_Overall!$D$11:$D$13,0)
C9C9=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$F:$F,0)
C10C10=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$H:$H,0)
C11C11=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$J:$J,0)
C12C12=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$L:$L,0)
D8D8=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$E:$E,0)
D9D9=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$G:$G,0)
D10D10=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$I:$I,0)
D11D11=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$K:$K,0)
D12D12=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$M:$M,0)
D13D13=SUM($D$8:$D$12)
C16C16=VLOOKUP(,Table8[[Qualifying]:[Bonus]],4)
Cells with Data Validation
CellAllowCriteria
D2List=$W$7:$W$10
D4List=$X$7:$X$19
 

Attachments

  • Screenshot 2022-02-20 023409.png
    Screenshot 2022-02-20 023409.png
    68.6 KB · Views: 6

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
4,055
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
To hide show the tables do the following:
Right click on the tab of the sheet with the tables
Select: 'View Code...
This opens a special VBA module: the module of the sheet. Here things that happen on the sheet (events) can be captured and acted on, like a change made to a cell.

Paste the following code into the code window:
VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'This macro runs for any value changes the user makes to the sheet.
    'The changed cell or range is called Target and gets passed to the macro by Windows
    
    Dim rFind As Range
    Dim sAgent As String
    
    'Check to see if cell D2 has changed
    If Not Intersect(Target, Range("D2")) Is Nothing Then
        'D2 has changed, so act on the contents
        sAgent = Range("D2").Value
        
        If sAgent = "" Then 'D2 is blank
            'show all tables
            Range("A19:A" & Rows.Count).EntireRow.Hidden = False
        Else
            'Hide all tables
            Range("A19:A" & Rows.Count).EntireRow.Hidden = True
            'Find the table for the agent. The name in top left of table must be the same as in the drop down
            Set rFind = Range("B18:B10000").Find(sAgent)
            If Not rFind Is Nothing Then
                'found the table for the agent
                rFind.Resize(14).EntireRow.Hidden = False
            End If
        End If
    End If
End Sub
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
4,055
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Ah, i wasn't finished yet...
Now you can test it : go back to excel and make a change to any cell, nothing should happen. Then make a change to cell D2 with the dropdown. All tables should disappear bar one. If you make D2 blank all cells will be visible again.

Also is there a way to code the Month drop-down to automatically fill the data into it's matching month column?
Explain please.
what is the matching month column?
what data needs to be filled? where does it sit?
 

Forum statistics

Threads
1,181,680
Messages
5,931,375
Members
436,788
Latest member
er19

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