PDA

View Full Version : Hide/ Unhide Column Through Listbox



vickydalmia
02-03-2006, 01:28 AM
Hello Board Room,

This is Vicky here....

I have a Excel Sheet which has a UserForm Containing Listbox and Two CommandButton for "OK" & "CANCEL". The Listbox Contain List of Headers which is defined in the columns of the worksheet.

For Example :
Column A ..............Column B.........Column C
...S.N. .............. Account Type.......Account Number

My Listbox has a properties are as follows :
1) ListBox1.MultiSelect = fmMultiSelectMulti
2) ListBox1.ListStyle = fmListStyleOption

Now what i want is, as I select "S.N." and "Account Type" from the Listbox and click ok, Column A and Column B Should Hide and Vice - Versa

The Code which i am using for fetching value from Worksheet to Listbox is as follows :

Private Sub UserForm_Initialize()

Dim Lrange As Range
Dim x As Variant

UserForm1.SpecialEffect = fmSpecialEffectEtched
ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.ListStyle = fmListStyleOption

Set Lrange = Sheet1.Range("A1:C1")
For Each x In Lrange
ListBox1.AddItem x.Value
Next x
End Sub

After Trying allot I have reached to fetch the selected value from the Listbox in the Msgbox but unable to hide the Column.

The code as follows :

Private Sub CommandButton2_Click()
Dim x As Integer
For x = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(x) = True Then
MsgBox ListBox1.List(x)
End If
Next x
Unload Me
End Sub

Kindly help me out to solve the project.

Thanking You

Vikash Dalmia
vicky.dalmia at gmail dot com (vicky.dalmia@gmail.com)
09331957669

matthewspatrick
02-03-2006, 07:09 AM
Vicky,

Welcome to VBAX. In the future, please try to use the VBA tags, as they make it much easier to read your code. Also, you should make a practice of indenting your code to further enhance readability.

Try something like this:


Private Sub CommandButton2_Click()

Dim x As Integer

With ThisWorkbook.Worksheets("The worksheet")
For x = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(x) = True Then
.Columns(x + 1).Visible = False
Else
.Columns(x + 1).Visible = True
End If
Next x
End With

Unload Me

End Sub



Patrick