Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down bar to hide column according to the content of a cell
Hi all,
I need help. How can i write a macro such that when the user select (e.g. 2 ) from the dropdown for, it hides column G:Z when the user select (e.g. 5) from the dropdown form, it hides Column J:Z Thank you so much. Cheers WL |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down bar to hide column according to the content of a cell
WL
This macro should do what you want. I wrote the macro so that you could add conditions and ranges to hide for those conditions. Also, as written, this macro will hide the designated columns in the sheet that holds the drop-down and the drop-down is in cell A2. Note that this macro is a sheet macro and must be placed in the sheet module of that sheet that holds the drop-down. To access that module, right-click on the sheet tab, select View Code, and paste this macro into that module. "X" out of the module to return to the worksheet. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim RngToHide As Range If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A2")) Is Nothing Then Select Case Target.Value Case 2: Set RngToHide = Columns("G:Z") Case 5: Set RngToHide = Columns("J:Z") Case Else: Set RngToHide = Range("A1") End Select If RngToHide.Address(0, 0) = "A1" Then Exit Sub Cells.EntireColumn.Hidden = False RngToHide.EntireColumn.Hidden = True End If End Sub wrote in message ups.com... Hi all, I need help. How can i write a macro such that when the user select (e.g. 2 ) from the dropdown for, it hides column G:Z when the user select (e.g. 5) from the dropdown form, it hides Column J:Z Thank you so much. Cheers WL |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down bar to hide column according to the content of a cell
I'm sorry to bother you again..
If the columns to be hidden in another sheet, how shd i go about it?? E.g. The Drop Down is in Sheet "ABC", the columns to be hidden is in Sheet "Final" Otto Moehrbach wrote: WL This macro should do what you want. I wrote the macro so that you could add conditions and ranges to hide for those conditions. Also, as written, this macro will hide the designated columns in the sheet that holds the drop-down and the drop-down is in cell A2. Note that this macro is a sheet macro and must be placed in the sheet module of that sheet that holds the drop-down. To access that module, right-click on the sheet tab, select View Code, and paste this macro into that module. "X" out of the module to return to the worksheet. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim RngToHide As Range If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A2")) Is Nothing Then Select Case Target.Value Case 2: Set RngToHide = Columns("G:Z") Case 5: Set RngToHide = Columns("J:Z") Case Else: Set RngToHide = Range("A1") End Select If RngToHide.Address(0, 0) = "A1" Then Exit Sub Cells.EntireColumn.Hidden = False RngToHide.EntireColumn.Hidden = True End If End Sub wrote in message ups.com... Hi all, I need help. How can i write a macro such that when the user select (e.g. 2 ) from the dropdown for, it hides column G:Z when the user select (e.g. 5) from the dropdown form, it hides Column J:Z Thank you so much. Cheers WL |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop down bar to hide column according to the content of a cell
Daphie
Use this macro. Note that the sheet name ABC (the sheet that has the drop-down cell) does not appear in the macro. You can call it anything you want. But the sheet name "Final" does appear in the macro, so if you want to change that sheet name, you need to change the "Final" in the macro as well. Note the leading periods in the 2 lines between "With Sheets("Final")" and "End With". Make sure they are there. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim RngToHide As Range If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A2")) Is Nothing Then Select Case Target.Value Case 2: Set RngToHide = Columns("G:Z") Case 5: Set RngToHide = Columns("J:Z") Case Else: Set RngToHide = Range("A1") End Select If RngToHide.Address(0, 0) = "A1" Then Exit Sub With Sheets("Final") .Cells.EntireColumn.Hidden = False .Range(RngToHide.Address).EntireColumn.Hidden = True End With End If End Sub "Daphie" wrote in message oups.com... I'm sorry to bother you again.. If the columns to be hidden in another sheet, how shd i go about it?? E.g. The Drop Down is in Sheet "ABC", the columns to be hidden is in Sheet "Final" Otto Moehrbach wrote: WL This macro should do what you want. I wrote the macro so that you could add conditions and ranges to hide for those conditions. Also, as written, this macro will hide the designated columns in the sheet that holds the drop-down and the drop-down is in cell A2. Note that this macro is a sheet macro and must be placed in the sheet module of that sheet that holds the drop-down. To access that module, right-click on the sheet tab, select View Code, and paste this macro into that module. "X" out of the module to return to the worksheet. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim RngToHide As Range If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("A2")) Is Nothing Then Select Case Target.Value Case 2: Set RngToHide = Columns("G:Z") Case 5: Set RngToHide = Columns("J:Z") Case Else: Set RngToHide = Range("A1") End Select If RngToHide.Address(0, 0) = "A1" Then Exit Sub Cells.EntireColumn.Hidden = False RngToHide.EntireColumn.Hidden = True End If End Sub wrote in message ups.com... Hi all, I need help. How can i write a macro such that when the user select (e.g. 2 ) from the dropdown for, it hides column G:Z when the user select (e.g. 5) from the dropdown form, it hides Column J:Z Thank you so much. Cheers WL |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide column or row based on a cell in the column or row? | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
How do I reference every "n" cell in a column in Excel? | Excel Worksheet Functions |