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

life_in_picture_format · Feb 20, 2022

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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Select Agent

2

Agent_2

3

Select Month

4

5

DPS

Month

Total Points Possible

6

Agent_2

0

100

7

Measured Stat

Month End Average/Total

Points Earned

Qualifying

Point

Range

Bonus

**

***

8

Attendance

2

16

100

-

90

$ 350.00

Top

9

Unavailable AVG

19.00%

23

89.9

-

80

$ 250.00

2nd

10

QA AVG

100.00%

30

79.9

-

70

/

No Bonus

Still passing

11

Survey Avg

4.6

4

12

Hold/ACW AVG

2.19%

20

13

Total Points

93

14

15

16

Bonus

#N/A

17

18

19

Agent 1

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

20

Attendance Score

0

21

Attenadance Points

0

22

Unavailable Avg

0.00%

23

Unavailable Points

0

24

QA Avg

0.00%

25

QA Points

0

26

Survey Avg

0.00%

27

Survey Points

0

28

Hold/ACW Avg

0.00%

29

Hold/ACW Points

0

30

EOM Total Points

0

31

EOM Stat AVG

0.00%

32

EOM BONUS

$ -

33

34

Agent 2

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

35

Attendance Score

0

36

Attenadance Points

0

37

Unavailable Avg

0.00%

38

Unavailable Points

0

39

QA Avg

0.00%

40

QA Points

0

41

Survey Avg

0.00%

42

Survey Points

0

43

Hold/ACW Avg

0.00%

44

Hold/ACW Points

0

45

EOM Total Points

0

46

EOM Stat AVG

0.00%

47

EOM BONUS

$ -

48

49

Agent 3

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

50

Attendance Score

0

51

Attenadance Points

0

52

Unavailable Avg

0.00%

53

Unavailable Points

0

54

QA Avg

0.00%

55

QA Points

0

56

Survey Avg

0.00%

57

Survey Points

0

58

Hold/ACW Avg

0.00%

59

Hold/ACW Points

0

60

EOM Total Points

0

61

EOM Stat AVG

0.00%

62

EOM BONUS

$ -

DPS_MailOut

Cell Formulas
Range	Formula
B6	B6	=D2
C6	C6	=$D$4
D6	D6	=XLOOKUP(B6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$N:$N,0)
C8	C8	=XLOOKUP($B$6,Worksheet_C_Overall!$C$11:$C$13,Worksheet_C_Overall!$D$11:$D$13,0)
C9	C9	=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$F:$F,0)
C10	C10	=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$H:$H,0)
C11	C11	=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$J:$J,0)
C12	C12	=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$L:$L,0)
D8	D8	=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$E:$E,0)
D9	D9	=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$G:$G,0)
D10	D10	=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$I:$I,0)
D11	D11	=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$K:$K,0)
D12	D12	=XLOOKUP($B$6,Worksheet_C_Overall!$C:$C,Worksheet_C_Overall!$M:$M,0)
D13	D13	=SUM($D$8:$D$12)
C16	C16	=VLOOKUP(,Table8[[Qualifying]:[Bonus]],4)

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=$W$7:$W$10
D4	List	=$X$7:$X$19

sijpie · Feb 23, 2022

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 · Feb 23, 2022

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?

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

life_in_picture_format

New Member

Attachments

Excel Facts

sijpie

Well-known Member

sijpie

Well-known Member

Similar threads

Forum statistics

Share this page

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

life_in_picture_format

New Member

Attachments

Excel Facts

sijpie

Well-known Member

sijpie

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock