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

 

[didijaba]

To explain more. Problem starts when I start clicking last item on list in listbox2 or listbox3. Then it becomes crazy

 

[didijaba]

So far solution would be to place "DO NOT CLICK!!!" in last column cell ). VBA solution are most welcome

 

[didijaba]

Any hepl, pls?

 

[parry]

Why are you clearing and rebuilding the listboxes every time someone clicks on ListBox2 - doesn't make sense.

Also, you may find you run into trouble if KUMULATIVNA isn't the activesheet with your rng variable.

Eg this...

With Sheets("KUMULATIVNA")
        last = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set rng = Range(Cells(1, 1), Cells(1, last))
End With

Should be...

With Sheets("KUMULATIVNA")
        last = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set rng = .Range(.Cells(1, 1), .Cells(1, last))
End With

 

[didijaba]

i'm clearing and rebuilding the listboxes every time someone clicks on ListBox2 or Listbox3 because I want lists to show true list of hidden (Listbox3) and unhidden (Listbox2) columns. If there is better way I'm all ears .
What I don't get is why my listboxes go crazy when I click last item on list.
I have changed code like you suggested.

Private Sub ListBox2_Click()
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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For i = 1 To last Step 1
If rng.Cells(1, i) = ListBox2.Value Then
Columns(i).Hidden = True
End If
Next i
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Call UserForm_Initialize

End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub ListBox3_Click()
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
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For i = 1 To last Step 1
If rng.Cells(1, i).Value = ListBox3.Value Then
Columns(i).Hidden = False
End If
Next i
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Call UserForm_Initialize

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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).Address
        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).Address
        End If
Next i
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

 

[snb_]

Start reducing the code

Private Sub ListBox2_Click()
   hide_column 2
End Sub
 
Private Sub ListBox3_Click()
   hide_column 3
End Sub
 
Private Sub(x)
   For each cl in Sheets("KUMULATIVNA").rows(1).specialcells(2)
     If cl.value = Me("ListBox" & x ).Value Then Columns(cl.column).Hidden = True
   Next 
   Me("Listbox" & x)=filter(worksheetfunction.transpose(worksheetfunction.transpose(Sheets("KUMULATIVNA").usedrange.rows(1))),Me("Listbox" & x).value,false)
End Sub

<!-- / message -->

 

[Norie]

didijaba

Why do you have this?

Call UserForm_Initialize

You probably shouldn't be using code to call any event(s), it doesn't really make sense.

An event is triggered automatically by something or other, in this example the userform being shown.

What are you actually trying to achieve? Why do you want/need to display what columns are hidden/visible?

PS It might be an idea to not specifically ask a MVP/expert to help out, it could actually put people off.

 

[parry]

To explain more. Problem starts when I start clicking last item on list in listbox2 or listbox3. Then it becomes crazy

It's unclear what you mean by crazy. The fact that your clearing the list items then rebuilding is going to lead to some screen flicker, where there is a slight delay before the item you selected is in focus.

You could turn off ScreenUpdating (i.e. Application.ScreenUpdating = False) at the start of the ListBox2_Click proc then turn them back on at the end - this will reduce the flicker effect.

Your code could be more efficient as snb has suggested, but besides that you are currently rebuilding the listbox regardless of whether the user has actually hidden anything or not after the form is opened. Unfortunately theres no worksheet event to monitor hiding/unhiding a column/row but you could use an array/range to indicate which rows/columns were hidden when the form was opened then interrogate this before rebuilding the list items to avoid needless resetting of the values.

Users hiding/unhiding rows isn't usually a thing users do repeatedly and often, but it appears to be a common action for your users.

 

[didijaba]

what I'm trying to do is to have two listbox on userform. One for displaying unhidden columns, and other for displaying hidden columns. When user click listbox1 he hides selected column , listbox1 removes that item from list and listbox2 adds that to its list. With crazy I mean that when I click last item on list, listbox doesn't add hidden column to listbox2, and starts acting randomly.