Use list options to hide columns

[G_Chem]

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

[mangesh_yadav]

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

[G_Chem]

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

[mangesh_yadav]

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.Hidden = False

If ListBox1.Value = "test1" Then
Worksheets("Sheet2").Columns("B:B").EntireColumn.Hidden = True
ElseIf ListBox1.Value = "test2" Then
Worksheets("Sheet2").Columns("C:C").EntireColumn.Hidden = True
Else
Worksheets("Sheet2").Columns("D:D").EntireColumn.Hidden = True
End If

End Sub



Mangesh

[G_Chem]

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.Hidden = False

If ListBox1.Value = "test1" Then
Worksheets("Sheet2").Columns("B:B").EntireColumn.Hidden = True
ElseIf ListBox1.Value = "test2" Then
Worksheets("Sheet2").Columns("C:C").EntireColumn.Hidden = True
Else
Worksheets("Sheet2").Columns("D:D").EntireColumn.Hidden = 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 > Validation>Allow: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.Hidden = False

If ListBox1.Value = "Alpha" Then
Worksheets("Trial").Columns("K:K,X:AD").EntireColumn.Hidden = True
ElseIf ListBox1.Value = "Beta" Then
Worksheets("Trial").Columns("D:D,K:L").EntireColumn.Hidden = True
ElseIf ListBox1.Value = "Alpha Infinite Depth" Then
Worksheets("Trial").Columns("X:AD").EntireColumn.Hidden = True
ElseIf ListBox1.Value = "Beta Infinite Depth" Then
Worksheets("Trial").Columns("D:D,L:L").EntireColumn.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

[mangesh_yadav]

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.Hidden = False

If Target.Value = "Alpha" Then
Worksheets("Trial").Columns("K:K").EntireColumn.Hidden = True
Worksheets("Trial").Columns("X:AD").EntireColumn.Hidden = True
ElseIf Target.Value = "Beta" Then
Worksheets("Trial").Columns("D:D").EntireColumn.Hidden = True
Worksheets("Trial").Columns("K:L").EntireColumn.Hidden = True
ElseIf Target.Value = "Alpha Infinite Depth" Then
Worksheets("Trial").Columns("X:AD").EntireColumn.Hidden = True
ElseIf Target.Value = "Beta Infinite Depth" Then
Worksheets("Trial").Columns("D:D").EntireColumn.Hidden = True
Worksheets("Trial").Columns("L:L").EntireColumn.Hidden = 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