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