VBA to show or hide columns in one worksheet conditioned on value in other worksheet

[punsterr]

I'll preface this by saying I'm a relative newbie to VBA, so bear with me. I have three worksheets in a workbook, "Contents," "I-1" and "Prov Rec". I have written one macro in Module 1 to automatically open to the worksheet "Contents" regardless of which sheet was open when the user last saved the file.

One cell in worksheet I-1 has a three-choice drop-down box. Whenever the drop-down selection is changed on the I-1 worksheet, I want the macro to either hide or unhide certain columns on the Prov Rec worksheet.

Here's what I've done so far. I've borrowed from various sources I've come across, but it's not coming together quite right. Any suggestions as to what I'm missing and whether it can be written more efficiently?

All of these Subs are written in Module 2 one below the next. Let me know if that's an issue as well.

Thanks in advance!
-------

Sub Run_On_Open()

' Run the macro CheckforChange any time an entry is made in I-1 cell B17
ThisWorkbook.Worksheets("Contents").OnEntry = "CheckForChange"

End Sub


Sub CheckForChange()
Application.ScreenUpdating = False
Dim WorkbookTypeField As String
' A change in worksheet I-1 cell B17 will trigger the SetWorkbookType macro.
ThisWorkbook.Worksheets ("I-1")
WorkbookTypeField = Range("B17")

If Not Application.Intersect(ActiveCell, WorkbookTypeField) _
Is Nothing Then SetWorkbookType
Application.ScreenUpdating = True

End Sub

Sub SetWorkbookType()
Application.ScreenUpdating = False
Dim WorkbookTypeField As String
' Checking value in worksheet I-1 cell B17
ThisWorkbook.Worksheets ("I-1")
WorkbookTypeField = Range("B17")

Select Case WorkbookTypeField
' If the value in worksheet I-1 cell B17 is Book-to-Tax...
Case "Book-to-Tax"
' Hide columns C-E on Prov Rec worksheet
Sheets("Prov Rec").Select
Columns("C:E").Select
Selection.EntireColumn.Hidden = True
' If the value in worksheet I-1 cell B17 is Provision-to-Return...
Case "Provision-to-Return"
' Show columns C-E on Prov Rec worksheet
Sheets("Prov Rec").Select
Columns("C:E").Select
Selection.EntireColumn.Hidden = False
' If the value in worksheet I-1 cell B17 is Extension-to-Return...
Case "Extension-to-Return"
Sheets("Prov Rec").Select
Columns("C:E").Select
Selection.EntireColumn.Hidden = False

End Select

Application.ScreenUpdating = True

End Sub

[Bob Phillips]

I think this is just what you need

Private Sub Workbook_Open()
Worksheets("Contents").Activate
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

and

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B17"

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address(False, False) = WS_RANGE Then
Select Case .Value
' If value in worksheet I-1 cell B17 is Book-to-Tax...
Case "Book-to-Tax"
' Hide columns C-E on Prov Rec worksheet
Sheets("Prov Rec").Columns("C:E").Hidden = True
' If the value in worksheet I-1 cell B17 is
' Provision-to-Return...
Case "Provision-to-Return", "Extension-to-Return"
Sheets("Prov Rec").Columns("C:E").Hidden = False
End Select
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet (I-1) code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)


"punsterr" <[email protected]> wrote in
message news:[email protected]...
>
> I'll preface this by saying I'm a relative newbie to VBA, so bear with
> me. I have three worksheets in a workbook, "Contents," "I-1" and "Prov
> Rec". I have written one macro in Module 1 to automatically open to the
> worksheet "Contents" regardless of which sheet was open when the user
> last saved the file.
>
> One cell in worksheet I-1 has a three-choice drop-down box. Whenever
> the drop-down selection is changed on the I-1 worksheet, I want the
> macro to either hide or unhide certain columns on the Prov Rec
> worksheet.
>
> Here's what I've done so far. I've borrowed from various sources I've
> come across, but it's not coming together quite right. Any suggestions
> as to what I'm missing and whether it can be written more efficiently?
>
> All of these Subs are written in Module 2 one below the next. Let me
> know if that's an issue as well.
>
> Thanks in advance!
> -------
>
> Sub Run_On_Open()
>
> ' Run the macro CheckforChange any time an entry is made in I-1 cell
> B17
> ThisWorkbook.Worksheets("Contents").OnEntry = "CheckForChange"
>
> End Sub
>
>
> Sub CheckForChange()
> Application.ScreenUpdating = False
> Dim WorkbookTypeField As String
> ' A change in worksheet I-1 cell B17 will trigger the
> SetWorkbookType macro.
> ThisWorkbook.Worksheets ("I-1")
> WorkbookTypeField = Range("B17")
>
> If Not Application.Intersect(ActiveCell, WorkbookTypeField) _
> Is Nothing Then SetWorkbookType
> Application.ScreenUpdating = True
>
> End Sub
>
> Sub SetWorkbookType()
> Application.ScreenUpdating = False
> Dim WorkbookTypeField As String
> ' Checking value in worksheet I-1 cell B17
> ThisWorkbook.Worksheets ("I-1")
> WorkbookTypeField = Range("B17")
>
> Select Case WorkbookTypeField
> ' If the value in worksheet I-1 cell B17 is Book-to-Tax...
> Case "Book-to-Tax"
> ' Hide columns C-E on Prov Rec worksheet
> Sheets("Prov Rec").Select
> Columns("C:E").Select
> Selection.EntireColumn.Hidden = True
> ' If the value in worksheet I-1 cell B17 is
> Provision-to-Return...
> Case "Provision-to-Return"
> ' Show columns C-E on Prov Rec worksheet
> Sheets("Prov Rec").Select
> Columns("C:E").Select
> Selection.EntireColumn.Hidden = False
> ' If the value in worksheet I-1 cell B17 is
> Extension-to-Return...
> Case "Extension-to-Return"
> Sheets("Prov Rec").Select
> Columns("C:E").Select
> Selection.EntireColumn.Hidden = False
>
> End Select
>
> Application.ScreenUpdating = True
>
> End Sub
>
>
> --
> punsterr
> ------------------------------------------------------------------------
> punsterr's Profile:
http://www.excelforum.com/member.php...o&userid=23961
> View this thread: http://www.excelforum.com/showthread...hreadid=396718
>