Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Columns based on a cell value | Excel Worksheet Functions | |||
Hide columns based on related cell | Excel Programming | |||
Hide/Show Rows based on Cell Value with Data Validation | Excel Programming | |||
Hide or Unhide certain columns based on a cell value | Excel Programming | |||
Macro to Hide/Show Columns based on control cell value | Excel Programming |