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

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" wrote in
message ...

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