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

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

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

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

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 :confused:

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

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