using 2003. Workbook has the following tabs:
MON, LTM, YTD, Actuals Comparison, Forecast Comparison, Budget
Comparison, Prior Year Comparison

I have created range names in each of the tabs - in MON, LTM, YTD they
are by month - Jan to Dec - with the range encompassing 18 columns for
each month.
in Actuals...Pror Year, they are by category of MON, LTM, YTD with the
range encompassing 13 columns for each category.

When the user selects a menu option, the action is to call ShowMon or
ShowComp - see code below;
Problem: ShowMon works great. ShowComp does not. Why?

Added info: I have a macro that creates the ranges. The ranges for
MON, LTM, YTD all show in the "Name Box" nex to the formula bar. The
ranges for Actuals...Prior Year, do not. Why? I have deleted all of
the ranges and created only the Actuals and still they do not show up
in the "Name Box" - however, they do show in the "Define Name" dialog
that is available thru Insert, Name, Define. When I create names for
MON, LTM, YTD tabs, they all show in the "Name Box" What gives?


Pulling out what little hair I have left,

meg99

Sub ShowMon()
Dim MyPik As String, MyTab As String, MyRange As String
'prevent screen flicker
Application.ScreenUpdating = False
'capture menu pik and tab name
'menu options are January ... December
MyPik = Application.CommandBars.ActionControl.Caption
'tab options are MON, LTM, YTD
MyTab = ActiveSheet.Name

'set initial data for range name
MyRange = "PLANT_5_"
'hide all columns
Columns("B:HV").Select
Selection.EntireColumn.Hidden = True

'cycle thru case statement and unhide selected range
Select Case MyTab < ""
Case MyTab = "MON"
MyRange = MyRange & MyPik & "_MONTH_END"
Range(MyRange).Columns.Hidden = False
Case MyTab = "LTM"
MyRange = MyRange & MyPik & "_LTM"
Range(MyRange).Columns.Hidden = False
Case MyTab = "YTD"
MyRange = MyRange & MyPik & "_YTD"
Range(MyRange).Columns.Hidden = False
End Select

'set screen to normal updating
Application.ScreenUpdating = True
End Sub
==============================
Sub ShowComp()
Dim MyPik As String, MyTab As String, MyRange As String
'prevent screen flicker
Application.ScreenUpdating = False
'capture menu pik
'menu options are MON, LTM, YTD
MyPik = Application.CommandBars.ActionControl.Caption
'capture tab name
'tab options are Actuals Comparison, Forecaste..., Budget..., Prior
Year...
MyTab = ActiveSheet.Name
'capture the basic name for the tab - to be used in case statement
below
'that is, eliminate "Comparison" from the Actuals, Forecast, Budget, &
Prior Year tabs
mytab2 = InStr(MyTab, "Comparison") - 2
'trim spaces and set to uppercase
MyTab = Trim(UCase(Mid(MyTab, 1, mytab2)))
'set initial data for range name
MyRange = "PLANT_5_"
'hide all colmns
Columns("B:AZ").Select
Selection.EntireColumn.Hidden = True
'cycle thru case statement and unhide selected range
Select Case MyPik < ""
Case MyPik = "MON"
MyPik = "MONTH_END"
MyRange = MyRange & MyPik & "_" & MyTab
Range(MyRange).Columns.Hidden = False
Case MyPik = "LTM"
MyRange = MyRange & MyPik & "_" & MyTab
Range(MyRange).Columns.Hidden = False
Case MyPik = "YTD"
MyRange = MyRange & MyPik & "_" & MyTab
Range(MyRange).Columns.Hidden = False
End Select

'restore normal screen updating
Application.ScreenUpdating = True
End Sub