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.

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 worksheet.
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.

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 worksheet.
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.

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 procedu
Sub cboPickMth_Change()

End Sub

Copy the body of this code and paste it into the empty procedu
'--------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 worksheet.
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.

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

"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 procedu
Sub cboPickMth_Change()

End Sub

Copy the body of this code and paste it into the empty procedu
'--------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 worksheet.
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.

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

"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 procedu
Sub cboPickMth_Change()

End Sub

Copy the body of this code and paste it into the empty procedu
'--------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 worksheet.
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.