Listbox hide, unhide problem - MVP?

[didijaba]

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.

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

 

[parry]

Hi, if you use the debug option and step through your code you will see your code isn't hiding the columns because this line is always false...
If Rng.Cells(1, i) = ListBox2.Value Then

The reason why is because a number in a cell is stored as a double data type while a number stored in a listbox is text - i.e. 5 <> "5", but 5=5 and "5"="5".

So try...
If CStr(Rng.Cells(1, i).Value) = ListBox2.Value Then

Your also going to run into problems when all data is hidden so you may want to check at least 1 column is visible.

cheers,
Graham

 

[didijaba]

Hello and thanks for you effort. In cells there is just text, no numbers. I have tried placing code as you suggested but the result is like before. Thanks