Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default SHow Hide COlumns based on cell value

Hello! I want to put validation on a cell call it cell C1 and I will require
the user to select a number between 1 and 6. Whenever the end user changes
the value in cell c1 I want the respective number of columns between column C
and H to be shown. Therefore, if the user puts a 1 in cell C1 I want column
"C" visible and columns "D"through "H" hidden. If the number is 2, I want
columns "C"&"D" visible and "E" through "H" hidden, and so on. Can anyone
help?
Thanks....
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,986
Default SHow Hide COlumns based on cell value

Put this in the worksheet code module of the sheet you want to hide the
columns. Right click the sheet tab and click on View Code in the drop down
menu. When the VB Editor opens, copy and paste the code below into the code
window.

Private Sub Worksheet_Change(ByVal Target As Range)
Columns("D:H").Hidden = True
If Target = Range("C1") Then
Select Case Target
Case Is = 1
Columns("C").Hidden = False
Case Is = 2
Columns("C:D").Hidden = False
Case Is = 3
Columns("C:E").Hidden = False
Case Is = 4
Columns("C:F").Hidden = False
Case Is = 5
Columns("C:G").Hidden = False
Case Is = 6
Columns("C:H").Hidden = False
End Select
End If
End Sub

Since you want to use C1 as the Target cell, then column C must remain
visible. If you want to use a cell in a column other than C:H then you could
change the line

Columns("D:H").Hidden = True

To:

Columns("C:H").Hidden = True

And all six columns would be hidden until a number is put into the Target
Range
which would also have to be changed in the code line If Target = Range ?






"Walt H" wrote:

Hello! I want to put validation on a cell call it cell C1 and I will require
the user to select a number between 1 and 6. Whenever the end user changes
the value in cell c1 I want the respective number of columns between column C
and H to be shown. Therefore, if the user puts a 1 in cell C1 I want column
"C" visible and columns "D"through "H" hidden. If the number is 2, I want
columns "C"&"D" visible and "E" through "H" hidden, and so on. Can anyone
help?
Thanks....

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default SHow Hide COlumns based on cell value

Thank you very much for your help. Take care.

"JLGWhiz" wrote:

Put this in the worksheet code module of the sheet you want to hide the
columns. Right click the sheet tab and click on View Code in the drop down
menu. When the VB Editor opens, copy and paste the code below into the code
window.

Private Sub Worksheet_Change(ByVal Target As Range)
Columns("D:H").Hidden = True
If Target = Range("C1") Then
Select Case Target
Case Is = 1
Columns("C").Hidden = False
Case Is = 2
Columns("C:D").Hidden = False
Case Is = 3
Columns("C:E").Hidden = False
Case Is = 4
Columns("C:F").Hidden = False
Case Is = 5
Columns("C:G").Hidden = False
Case Is = 6
Columns("C:H").Hidden = False
End Select
End If
End Sub

Since you want to use C1 as the Target cell, then column C must remain
visible. If you want to use a cell in a column other than C:H then you could
change the line

Columns("D:H").Hidden = True

To:

Columns("C:H").Hidden = True

And all six columns would be hidden until a number is put into the Target
Range
which would also have to be changed in the code line If Target = Range ?






"Walt H" wrote:

Hello! I want to put validation on a cell call it cell C1 and I will require
the user to select a number between 1 and 6. Whenever the end user changes
the value in cell c1 I want the respective number of columns between column C
and H to be shown. Therefore, if the user puts a 1 in cell C1 I want column
"C" visible and columns "D"through "H" hidden. If the number is 2, I want
columns "C"&"D" visible and "E" through "H" hidden, and so on. Can anyone
help?
Thanks....

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default SHow Hide COlumns based on cell value

Sorry for the bother, however, The columns revert to hidden if any other cell
in the worksheet happens to have a value entered into it subsequent to
changing the value in c1/b1. I see where you define the variable but this is
all beyond me so I am asking for a little more help whenever you have a
chance...Thanks
In other words, lets say the target is B1, so I enter "2" in b1 and the
first two columns in the case statement become unhidden. Then, suppose I type
the number "8" in cell b4, the macro then re-hides the two columns that were
shown. I am thinking this is happening because there was a change in the
worksheet where I pasted the code....Thanks....

"JLGWhiz" wrote:

Put this in the worksheet code module of the sheet you want to hide the
columns. Right click the sheet tab and click on View Code in the drop down
menu. When the VB Editor opens, copy and paste the code below into the code
window.

Private Sub Worksheet_Change(ByVal Target As Range)
Columns("D:H").Hidden = True
If Target = Range("C1") Then
Select Case Target
Case Is = 1
Columns("C").Hidden = False
Case Is = 2
Columns("C:D").Hidden = False
Case Is = 3
Columns("C:E").Hidden = False
Case Is = 4
Columns("C:F").Hidden = False
Case Is = 5
Columns("C:G").Hidden = False
Case Is = 6
Columns("C:H").Hidden = False
End Select
End If
End Sub

Since you want to use C1 as the Target cell, then column C must remain
visible. If you want to use a cell in a column other than C:H then you could
change the line

Columns("D:H").Hidden = True

To:

Columns("C:H").Hidden = True

And all six columns would be hidden until a number is put into the Target
Range
which would also have to be changed in the code line If Target = Range ?






"Walt H" wrote:

Hello! I want to put validation on a cell call it cell C1 and I will require
the user to select a number between 1 and 6. Whenever the end user changes
the value in cell c1 I want the respective number of columns between column C
and H to be shown. Therefore, if the user puts a 1 in cell C1 I want column
"C" visible and columns "D"through "H" hidden. If the number is 2, I want
columns "C"&"D" visible and "E" through "H" hidden, and so on. Can anyone
help?
Thanks....

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,986
Default SHow Hide COlumns based on cell value

The code can be written two ways. 1) Start with the columns hidden, then
unhide the ones you want to see, or 2) have all columns visible, then hide
the ones you don't want to see.

You have the first option, so any change other than entering 1-6 in C1 or B1
will hide the columns. But, I have modified the code so that if will not
hide any columns unless you make the change in cell B1. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B1") Then
Columns("D:H").Hidden = True
Select Case Target
Case Is = 1
Columns("C").Hidden = False
Case Is = 2
Columns("C:D").Hidden = False
Case Is = 3
Columns("C:E").Hidden = False
Case Is = 4
Columns("C:F").Hidden = False
Case Is = 5
Columns("C:G").Hidden = False
Case Is = 6
Columns("C:H").Hidden = False
End Select
End If
End Sub



"Walt H" wrote:

Sorry for the bother, however, The columns revert to hidden if any other cell
in the worksheet happens to have a value entered into it subsequent to
changing the value in c1/b1. I see where you define the variable but this is
all beyond me so I am asking for a little more help whenever you have a
chance...Thanks
In other words, lets say the target is B1, so I enter "2" in b1 and the
first two columns in the case statement become unhidden. Then, suppose I type
the number "8" in cell b4, the macro then re-hides the two columns that were
shown. I am thinking this is happening because there was a change in the
worksheet where I pasted the code....Thanks....

"JLGWhiz" wrote:

Put this in the worksheet code module of the sheet you want to hide the
columns. Right click the sheet tab and click on View Code in the drop down
menu. When the VB Editor opens, copy and paste the code below into the code
window.

Private Sub Worksheet_Change(ByVal Target As Range)
Columns("D:H").Hidden = True
If Target = Range("C1") Then
Select Case Target
Case Is = 1
Columns("C").Hidden = False
Case Is = 2
Columns("C:D").Hidden = False
Case Is = 3
Columns("C:E").Hidden = False
Case Is = 4
Columns("C:F").Hidden = False
Case Is = 5
Columns("C:G").Hidden = False
Case Is = 6
Columns("C:H").Hidden = False
End Select
End If
End Sub

Since you want to use C1 as the Target cell, then column C must remain
visible. If you want to use a cell in a column other than C:H then you could
change the line

Columns("D:H").Hidden = True

To:

Columns("C:H").Hidden = True

And all six columns would be hidden until a number is put into the Target
Range
which would also have to be changed in the code line If Target = Range ?






"Walt H" wrote:

Hello! I want to put validation on a cell call it cell C1 and I will require
the user to select a number between 1 and 6. Whenever the end user changes
the value in cell c1 I want the respective number of columns between column C
and H to be shown. Therefore, if the user puts a 1 in cell C1 I want column
"C" visible and columns "D"through "H" hidden. If the number is 2, I want
columns "C"&"D" visible and "E" through "H" hidden, and so on. Can anyone
help?
Thanks....

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide Columns based on a cell value Tami Excel Worksheet Functions 10 July 16th 09 06:31 PM
Hide columns based on related cell Lisa Excel Programming 4 May 30th 08 03:23 AM
Hide/Show Rows based on Cell Value with Data Validation Shelly Excel Programming 3 January 5th 08 12:01 AM
Hide or Unhide certain columns based on a cell value [email protected] Excel Programming 1 July 10th 06 10:17 AM
Macro to Hide/Show Columns based on control cell value Steve N Excel Programming 2 May 25th 04 06:51 PM


All times are GMT +1. The time now is 02:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"