didijaba
Well-known Member
- Joined
- Nov 26, 2006
- Messages
- 511
I have two listbox on userform. On first I show columns that are not hidden, and on second that are hidden. It works ok when I open form, but it doesn't work when I click from last to first and it does't show all hidden on other listbox. Expert help pls.
Code:
Private Sub UserForm_Initialize()
ListBox2.Clear
ListBox3.Clear
''''''''''''
Dim last As Integer
With Sheets("KUMULATIVNA")
last = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set rng = Range(Cells(1, 1), Cells(1, last))
End With
'''''''''''''
With Me.ListBox2
.ColumnCount = 2
For i = 1 To last Step 1
If rng.Cells(1, i).Width <> 0 Then
.AddItem rng.Cells(1, i).Value
.List(.ListCount - 1, 1) = rng.Cells(1, i).Column
End If
Next i
End With
With Me.ListBox3
.ColumnCount = 2
For i = 1 To last Step 1
If rng.Cells(1, i).Width = 0 Then
.AddItem rng.Cells(1, i).Value
.List(.ListCount - 1, 1) = rng.Cells(1, i).column
End If
Next i
End With
End Sub
..............................................
Private Sub ListBox2_Click()
Dim last As Integer
With Sheets("KUMULATIVNA")
last = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
Set rng = Range(Cells(1, 1), Cells(1, last))
For i = 1 To last Step 1
If rng.Cells(1, i) = ListBox2.Value Then
Columns(i).Hidden = True
End If
Next i
''''''''''''''
ListBox2.Clear
ListBox3.Clear
''''''''''''
With Sheets("KUMULATIVNA")
last = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set rng = Range(Cells(1, 1), Cells(1, last))
End With
'''''''''''''
With Me.ListBox2
.ColumnCount = 2
For i = 1 To last Step 1
If rng.Cells(1, i).Width <> 0 Then
.AddItem rng.Cells(1, i).Value
.List(.ListCount - 1, 1) = rng.Cells(1, i).Column
End If
Next i
End With
With Me.ListBox3
.ColumnCount = 2
For i = 1 To last Step 1
If rng.Cells(1, i).Width = 0 Then
.AddItem rng.Cells(1, i).Value
.List(.ListCount - 1, 1) = rng.Cells(1, i).Column
End If
Next i
End With
End Sub