RE: Create a "cell button" to hide selected cells.

To avoid cluttering up the work*** with controls, I used a UserForm.

The user clicks a button on the work*** 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 work***
- Name: cmdSetWeeksToShow
- Caption: Set Weeks to Show

Here's the VBA code......

The work*** 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 Active***
'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" wrote:

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" wrote:

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


"Steve1964" wrote:

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

"Ron Coderre" wrote:

Thanks for the feedback, Shoolin.....I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP


"Shoolin Patel" wrote:

Thanks Ron! That's excatly what I was looking for.

Shoolin

"Ron Coderre" wrote:

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


"Shoolin Patel" wrote:

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 *** 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

"Ron Coderre" wrote:

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


"Shoolin Patel" wrote:

Hey Ron,

That's a neat thing to know, but it still didn't help me with my problem. I
guess I am to be blame for that, I did a very poor job of describing my
situation.

I'll take another stab at it......

So far, I have entered Jan, Feb, Mach.......prior year and year to date data
in columns.

Now, I want to create 12 buttons (one for each month) in column A. I want to
setup these buttons so that when I click on January button all other months'
columns are hidden, and I can only see January, prior year and year to date
columns.

I hope I did a better job of describing my situation. Thank for you help!!!

Shoolin

"Ron Coderre" wrote:

If your post can be interpreted literally...meaning that you want a button to
hide/unhide entire columns...
Try this:

Select columns D,E, and F

From the Excel main menu:
<data><group and outline><Group>

That will display a [-] button above the work***.
Clicking on that button hides the columns and changes the button to [+]
Click on the [+] button to redisplay the columns.

Post back with any questions.
Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Shoolin Patel" wrote:

I want to setup or enter a function in a cell that will allow me to use that
cell as a "button". I want to setup this cell so that when I click on it, it
will hide selected cells.

For example:

Clicking on cell "A1" allows me to hide columns D,E and F.

I'd appreciate any kind of help on this.
.

Quantcast