Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Use list options to hide columns
Excel 97 Hi, I was wondering if the following is possible in excel: <big deep breath I have 4 separate worksheets for data entry and results calculation for radiation detection, each is a separate type of test. These worksheets/tests share a lot of common Fields for data input and calculations, what I want to do is combine the worksheets and show/hide only the columns relevant to each test. In column A, I have added a drop down list of 4 different types of test. Depending on the type of test I select; call them A, B, C & D Can I use a List to control which columns are shown? I'm not looking for you to do this for me just a helpful nudge in the right direction, providing it's feasible in the first place! Thanks for your time. G_Chem -- G_Chem ------------------------------------------------------------------------ G_Chem's Profile: http://www.excelforum.com/member.php...o&userid=24366 View this thread: http://www.excelforum.com/showthread...hreadid=379611 |
#2
|
|||
|
|||
You could use a combobox from the Control Toolbox menu (View Toolbars - check the Control Toolbox). Then add the following change event Private Sub ComboBox1_Change() Worksheets("Sheet2").Column("2:4").Hidden = False If ComboBox1.Value = "Test1" Then Worksheets("Sheet2").Column("2:2").Hidden = True ElseIf ComboBox1.Value = "Test2" Then Worksheets("Sheet2").Column("3:3").Hidden = True Else Worksheets("Sheet2").Column("4:4").Hidden = True End If End Sub Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=379611 |
#3
|
|||
|
|||
mangesh_yadav Wrote: You could use a combobox from the Control Toolbox menu (View Toolbars - check the Control Toolbox). Then add the following change event Private Sub ComboBox1_Change() Worksheets("Sheet2").Column("2:4").Hidden = False If ComboBox1.Value = "Test1" Then Worksheets("Sheet2").Column("2:2").Hidden = True ElseIf ComboBox1.Value = "Test2" Then Worksheets("Sheet2").Column("3:3").Hidden = True Else Worksheets("Sheet2").Column("4:4").Hidden = True End If End Sub Mangesh Thanks for the reply Mangesh, I have put this on the back burner for now as there are some arguments I have to sort out first. I am using a list box to control a few IF statement conditions and I'm not quite ready to make the jump to a combo box :) Thanks again! G_Chem -- G_Chem ------------------------------------------------------------------------ G_Chem's Profile: http://www.excelforum.com/member.php...o&userid=24366 View this thread: http://www.excelforum.com/showthread...hreadid=379611 |
#4
|
|||
|
|||
Since you had mentioned a drop down list, I used the combo-box, but if you want to use the list box instead, heres the code: Private Sub ListBox1_Click() Worksheets("Sheet2").Columns("B:D").EntireColumn.H idden = False If ListBox1.Value = "test1" Then Worksheets("Sheet2").Columns("B:B").EntireColumn.H idden = True ElseIf ListBox1.Value = "test2" Then Worksheets("Sheet2").Columns("C:C").EntireColumn.H idden = True Else Worksheets("Sheet2").Columns("D:D").EntireColumn.H idden = True End If End Sub Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=379611 |
#5
|
|||
|
|||
mangesh_yadav Wrote: Since you had mentioned a drop down list, I used the combo-box, but if you want to use the list box instead, heres the code: Private Sub ListBox1_Click() Worksheets("Sheet2").Columns("B:D").EntireColumn.H idden = False If ListBox1.Value = "test1" Then Worksheets("Sheet2").Columns("B:B").EntireColumn.H idden = True ElseIf ListBox1.Value = "test2" Then Worksheets("Sheet2").Columns("C:C").EntireColumn.H idden = True Else Worksheets("Sheet2").Columns("D:D").EntireColumn.H idden = True End If End Sub Mangesh Mangesh you've been a great help but I'm a bit stuck at the moment, below is my code: this is returning a variable undefined message for ListBox1. I seem to be having a problem getting VBA to recognise my listbox. The ListBox was created in excel using Data ValidationAllow:List, then my 4 test types values were selected from A34:A37, repectively, to display the drop down list in A2. The worksheet is called Trial and the test types are Alpha (I want this to hide columns K and X to AD) Beta (I want this to hide columns D, K and L) Alpha Infinite Depth (I want this to hide columns X to AD) Beta Infinite Depth (I want this to hide columns D and L) ======================================== Sub ListBox1_Click() Worksheets("Trial").Columns("A:AD").EntireColumn.H idden = False If ListBox1.Value = "Alpha" Then Worksheets("Trial").Columns("K:K,X:AD").EntireColu mn.Hidden = True ElseIf ListBox1.Value = "Beta" Then Worksheets("Trial").Columns("D:D,K:L").EntireColum n.Hidden = True ElseIf ListBox1.Value = "Alpha Infinite Depth" Then Worksheets("Trial").Columns("X:AD").EntireColumn.H idden = True ElseIf ListBox1.Value = "Beta Infinite Depth" Then Worksheets("Trial").Columns("D:D,L:L").EntireColum n.Hidden = True End If End Sub ========================================= I'm really unsure as to how to get VBA to associate the listbox in my spreadsheet with the commands in the macro TIA G_Chem -- G_Chem ------------------------------------------------------------------------ G_Chem's Profile: http://www.excelforum.com/member.php...o&userid=24366 View this thread: http://www.excelforum.com/showthread...hreadid=379611 |
#6
|
|||
|
|||
Hi, I thought you were using a listbox from the Control Toolbox menu. Anyway, since you are using the Data Validation, you need to use the following code. Assuming that the data validation is for the cell A1, use Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Worksheets("Trial").Columns("A:AD").EntireColumn.H idden = False If Target.Value = "Alpha" Then Worksheets("Trial").Columns("K:K").EntireColumn.Hi dden = True Worksheets("Trial").Columns("X:AD").EntireColumn.H idden = True ElseIf Target.Value = "Beta" Then Worksheets("Trial").Columns("D:D").EntireColumn.Hi dden = True Worksheets("Trial").Columns("K:L").EntireColumn.Hi dden = True ElseIf Target.Value = "Alpha Infinite Depth" Then Worksheets("Trial").Columns("X:AD").EntireColumn.H idden = True ElseIf Target.Value = "Beta Infinite Depth" Then Worksheets("Trial").Columns("D:D").EntireColumn.Hi dden = True Worksheets("Trial").Columns("L:L").EntireColumn.Hi dden = True End If End If End Sub This code should go in the module of the sheet where you will select the value alpha or beta.... Also change $A$1 from the code above to suit your needs. Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=379611 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Hide Columns | Excel Discussion (Misc queries) | |||
I used the "hide" feature on 3 columns - now I cannot get them b. | New Users to Excel | |||
How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions | |||
Hide Cell Content if no data in previous columns | Excel Discussion (Misc queries) | |||
drop down list multiple columns | Excel Discussion (Misc queries) |