VB use of field text to select & execute macro

dlbarrett

New Member
Joined
Dec 22, 2011
Messages
1
I need to select and unhide a sheet based on the the text that is input and stored in a spreadsheet. I have four options that will be stored in a sheet called MENU. The options are: Automotive, Industrial, Life_Sciences, Electronics.


Based on the field selection in the cell h9 I want to execute a macro to unhide the appropriate sheet.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
Use this and and replace to suit yours needs.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
 
    Application.ScreenUpdating = False
    Range("A1").Select
    Worksheets("PCAP 3.31").Visible = False
    Worksheets("PCAP 6.30").Visible = False
    Worksheets("PCAP 9.30").Visible = False
    Worksheets("PCAP 12.31").Visible = False
    Columns("F:L").Hidden = False
 
    Select Case Target.Value
        Case "First Quarter"
            Worksheets("PCAP 3.31").Visible = True
            Columns("F:K").Hidden = True
 
        Case "Second Quarter"
            Worksheets("PCAP 6.30").Visible = True
            Columns("H:K").Hidden = True
 
        Case "Third Quarter"
            Worksheets("PCAP 9.30").Visible = True
            Columns("J:K").Hidden = True
 
        Case "Fourth Quarter"
            Worksheets("PCAP 12.31").Visible = True
    End Select
 
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,579
Messages
5,523,695
Members
409,531
Latest member
wo1f

This Week's Hot Topics

Top