To avoid cluttering up the worksheet with controls, I used a UserForm.
The user clicks a button on the worksheet to display a UserForm
That form has 2 SpinnerButtons (for startweek and endweek), a button to only
show those columns, and a Cancel button.
Note: I put an Excel file with this solution (it contains macros, of course)
at this free file sharing website:
http://www.savefile.com/files/919359
1) Create a UserForm1 in the VBE with these features
In the upper left
• SpinnerButton
- Name: spinStartWk
• Label (next to spinStartWk to display its value)
- Name: lblStartWk
In the upper right
• SpinnerButton
- Name: spinEndWk
• Label (next to spinEndWk to display its value)
- Name: lblEndWk
Below the SpinnerButtons and Labels
• CommandButton
- Name: cmdDisplayColumns
Below cmdDisplayColumns
• CommandButton
- Name: cmdCancel
That's the minimum userform structure.
2) Put a command button in the upper left of the worksheet
- Name: cmdSetWeeksToShow
- Caption: Set Weeks to Show
Here's the VBA code......
The worksheet module code for the button:
Option Explicit
Private Sub cmdSetWeeksToShow_Click()
UserForm1.Show
End Sub
The UserForm1 code:
Option Explicit
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdDisplayColumns_Click()
Const iFirstCol As Integer = 3 'Adjust this value to your situation
Const iLastCol As Integer = 54 'Adjust this value to your situation
Dim iCtr As Integer
Application.ScreenUpdating = False
With ActiveSheet
'Hide all week columns
.Range( _
Cells(ColumnIndex:=iFirstCol), _
Cells(ColumnIndex:=iLastCol)) _
.EntireColumn.Hidden = True
'Show only the selected range of columns
.Range( _
Cells(ColumnIndex:=spinStartWk + iFirstCol - 1), _
Cells(ColumnIndex:=spinEndWk + iFirstCol - 1)) _
.EntireColumn.Hidden = False
End With
Application.ScreenUpdating = True
Unload Me
End Sub
Private Sub spinStartWk_Change()
With spinStartWk
lblStartWk.Caption = .Value
If .Value > spinEndWk.Value Then
spinEndWk.Value = .Value
End If
End With
End Sub
Private Sub spinEndWk_Change()
With spinEndWk
lblEndWk.Caption = .Value
If .Value < spinStartWk.Value Then
spinStartWk.Value = .Value
End If
End With
End Sub
Is that something you can work with?
***********
Regards,
Ron
XL2003, WinXP
Steve1964 said:
Thanks for the Reply Ron,
Option "2" is the one. We report on a weekly basis so there are 52 columns
of data.
I'd like to display, say, week 5 to week 8 when we are focusing on January
and perhaps week 25 to 29 when we are in June/July. I won't need to select a
non-contiguous range of columns. My "from" and "to" range will always be an
unbroken block.
Hope this is clear
Regards
Steve
Ron Coderre said:
Need a few more details....
Which of these describes what you want to do:
1)Unhide from the 1st period through the one you choose (hide the rest)
2)Unhide from a chosen start period through a chosen end period (hide the
rest)
3)Unhide various selected periods (hide the rest).
4)...something else?
***********
Regards,
Ron
XL2003, WinXP
:
Hello Ron,
Thanks for your advice on this macro. It's almost exactly what I'm after.
What I'd like to do, however, is to select a range of columns to display so I
can see the trend. Not just one period. Would I need two combo boxes, say, a
"From" and a "To"?
Any help would be great.
Thanks
Steve
:
Thanks for the feedback, Shoolin.....I'm glad that worked for you.
***********
Regards,
Ron
XL2002, WinXP
:
Thanks Ron! That's excatly what I was looking for.
Shoolin
:
OK...I understand better now
Replace the VBA code with this:
Sub cboPickMth_Change()
Dim intPickMonth As Integer
intPickMonth = Range("MthChoice").Value
On Error GoTo errTrap
Application.ScreenUpdating = False
With Range("MonthCols")
.EntireColumn.Hidden = True
.Offset(ColumnOffset:=intPickMonth - 1) _
.Resize(ColumnSize:=1) _
.EntireColumn _
.Hidden = False
End With
errTrap:
Application.ScreenUpdating = False
End Sub
Does that help?
***********
Regards,
Ron
XL2002, WinXP
:
Hey Ron,
Your last post helped a lot. The Macro that you gave almost did the trick.
There is only one change that I need to make. Currently, when I click on
March, or for that matter any month after January, the Macro shows all the
month prior to the month select and hides all the months that come after the
month selected. What I need, is for the Marco to hide all months before and
after the month selected.
Here is how I want the sheet to look if I selected March:
Column D Column N Column M
March Prior Year Year-to-Date
All other months (Jan, Feb and April to Dec) are hidden.
Thanks a lot for your help and time. I really appreciate it.
Shoolin
:
See if this example does what you want:
On Sheet1
In cells A1:A12 enter this list: Jan, Feb....Dec
Name that range: LU_MthList
Select B1 (still on Sheet1)
Name that range: MthChoice
Switch to Sheet2
In cells B1:M1 enter this list: Jan, Feb....Dec
Select entire columns B through M
Name that range: MonthCols
Using the FORMS toolbar
Create a ComboBox on Cell A1
While it's selected...
Type this in the NameBox (next to the formula bar): cboPickMth
Press [Enter]
(That just gives the ComboBox a name that makes sense)
Right-click on the cboPickMth ComboBox
Select: Format Control
Input Range: LU_MthList
Cell Link: MthChoice
Click the [OK] button
Right-click on the cboPickMth ComboBox (again)
Select: Assign Macro
Select: New
The VB editor should open and display the below empty procedure:
Sub cboPickMth_Change()
End Sub
Copy the body of this code and paste it into the empty procedure:
'--------start of code--------
Sub cboPickMth_Change()
Dim intPickMonth As Integer
intPickMonth = Range("MthChoice").Value
On Error GoTo errTrap
Application.ScreenUpdating = False
Range("MonthCols").EntireColumn.Hidden = False
If intPickMonth < 12 Then
Range("MonthCols") _
.Offset(ColumnOffset:=intPickMonth) _
.Resize(ColumnSize:=12 - intPickMonth) _
.EntireColumn _
.Hidden = True
End If
errTrap:
Application.ScreenUpdating = True
End Sub
'--------end of code--------
That's all....When you change the month in the ComboBox the appropriate
columns should show/hide.
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
