Rearrange column headers using listbox

[gaudrco]

I'm looking for a way to move line items in a listbox to rearrange the order of my table headers. So maybe a click and drag approach where I click and hold one line item in my listbox and drag it to a different position in the listbox so that it changes the table header location. Or maybe another approach could be to use two buttons. One button moves the line item up one and the other moves the line item down one. So you could click a listbox line item and use the buttons to move it up or down, which would ultimately change the location of the column headed on the table.

Background:
I have two listbox that use the column headers of a table as the listbox line items. The two listboxes allow me to show and hide columns of my table. When a listbox item is double-clicked, the list item is moved to the other listbox. ListboxVisible shows the columns and ListboxHidden hides the column.
Here is the code that accomplishes that:

Private Sub ToggleVisible_Click()Columns(4).Resize(, 37).Hidden = Not Columns(4).Resize(, 37).Hidden
UpDate_List
End Sub
Private Sub ListboxHidden_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Application.ScreenUpdating = False
On Error GoTo M
Cancel = True
Dim c As Long
Dim i As Long
Cells(7, 4).Resize(, 37).Select
    Selection.Find(What:=ListboxHidden.Value, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        c = ActiveCell.Column
        Columns(c).Hidden = False
        
For i = 0 To ListboxHidden.ListCount - 1
    If ListboxHidden.Selected(i) Then
        ListboxHidden.RemoveItem (i)
    End If
Next i
ListboxHidden.ListIndex = -1
Call UpDate_List
Range("B1").Select
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "No value selected"
End Sub
Private Sub ListboxVisible_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Application.ScreenUpdating = False
On Error GoTo M
Cancel = True
Dim c As Long
Dim i As Long
Cells(7, 4).Resize(, 37).Select
    Selection.Find(What:=ListboxVisible.Value, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        c = ActiveCell.Column
        Columns(c).Hidden = True
        
For i = 0 To ListboxVisible.ListCount - 1
    If ListboxVisible.Selected(i) Then
        ListboxVisible.RemoveItem (i)
    End If
Next i
ListboxVisible.ListIndex = -1
Call UpDate_List
Range("B1").Select
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "No value selected"
End Sub


Private Sub UserForm_Initialize()
For i = 4 To 40
    If Columns(i).Hidden = True Then ListboxHidden.AddItem Cells(7, i).Value
    If Columns(i).Hidden = False Then ListboxVisible.AddItem Cells(7, i).Value
Next
ListboxHidden.ControlTipText = "Double-click on me to SHOW this column"
ListboxVisible.ControlTipText = "Double-click on me to HIDE this column"


End Sub
Sub UpDate_List()
ListboxHidden.Clear
ListboxVisible.Clear
For i = 4 To 40
    If Columns(i).Hidden = True Then ListboxHidden.AddItem Cells(7, i).Value
    If Columns(i).Hidden = False Then ListboxVisible.AddItem Cells(7, i).Value
Next
End Sub


Private Sub CloseUserForm2_Click()
UserForm2.Hide
End Sub
Private Sub CommandButton1_Click()
UserForm2.Hide
UserForm1.Show
End Sub

 

[Yongle]

I tested sub below and it works for me AND the spin button also works correctly

Private Sub UserForm_Activate()
  For i = 4 To 40
      If Columns(i).Hidden = True Then ListboxHidden.AddItem Cells(7, i).Value
      If Columns(i).Hidden = False Then ListboxVisible.AddItem Cells(7, i).Value
  Next
  ListboxHidden.ControlTipText = "Double-click on me to SHOW this column"
  ListboxVisible.ControlTipText = "Double-click on me to HIDE this column"
End Sub

So I can only guess that it is something else causing the problem in your code - and that is easily proven
What I suggest you do is to disable EVERYTHING except UserForm_Activate and SpinButton1_Change to allow the intitalizing to be tested in isolation

Disabling other subs is simple - place XX before EVERY other procedure name
eg
Private Sub ListboxHidden_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
becomes
Private Sub XXListboxHidden_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
etc

Then run the userform to see if the spin button works correctly (and I think it will)
- let me know whether the problem has disappeared or not

 

[gaudrco]

- let me know whether the problem has disappeared or not

I did exactly as you said and the problem is persisting. Nothing happens when I click the DOWN button and when I click the UP button, I get an error on this section:

Private Sub SpinButton1_Change()
    MoveItem -SpinButton1.Value
    SpinButton1.Value = 0
End Sub

Should I not disable Private Sub XXMoveItem(X As Long)?

 

[Yongle]

You are correct - I forgot about that

 

[gaudrco]

Okay I enabled that part but the same thing happens. The UP button works but the DOWN does not

 

[Yongle]

Weird that it works correctly for me but not for you
Which version of Excel are you running?

You could try simply selecting an item in UserForm_Activate
ListboxVisible.ListIndex = 0

Let me know if that works

I will investigate further tomorrow

 

[gaudrco]

I am using Excel 2016. That did not work for me but I'm not sure if I did it right. I mentioned this above but I will say it again for clarity:

I have 2 listboxs, and when you double click on the line items, the item moves to the other listbox. For ListboxVisible, I am trying to apply this code to reorder the items around. When I initially open the userform, only the UP button works. But if I double click one of the list items in ListboxVisible, then both the UP and DOWN button work fine.

 

[gaudrco]

Have a look at the explanation of how to perform 2-way lookup using Index & Match here

I am also having a tough time getting this to work.

My current formula is this:
=IFERROR(INDEX('Competitor Comparison Data'!H:H,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS($A$8:$A8)-1),"No Match")

I'm trying to have Row 7 on the current sheet match Row 5 on Sheet ("Competitor Comparison Data"), then take the values of that column in Sheet ("Competitor Comparison Data"). That way, when I rearrange the columns, the headers change and then the MATCH formula adjusts the data to display the data for that corresponding column header.

 

[Yongle]

Suggest you post that as a new thread with title "Index & Match question"
- explain layout of the sheet etc
- do not refer to this thread's userform issue
- simply explain what you require to be returned from Index & Match
- it's a separate puzzle for someone to solve

 

[Yongle]

I have now been able to replicate your problem - but still trying to resolve - will update thread tomorrow

 

[Yongle]

SpinButton1_Change was the cause of the problem - using SpinDown and SpinUp fixes the issue and simplifies the code

Private Sub SpinButton1_SpinDown()
    MoveItem 1
End Sub

Private Sub SpinButton1_SpinUp()
    MoveItem -1
End Sub

Private Sub UserForm_Activate()
    Dim i As Long
    For i = 4 To 40
        If Columns(i).Hidden = True Then ListboxHidden.AddItem Cells(7, i).Value
        If Columns(i).Hidden = False Then ListboxVisible.AddItem Cells(7, i).Value
    Next
    ListboxHidden.ControlTipText = "Double-click on me to SHOW this column"
    ListboxVisible.ControlTipText = "Double-click on me to HIDE this column"
End Sub

Sub UpDate_List()
    ListboxHidden.Clear
    ListboxVisible.Clear
    For i = 4 To 40
        If Columns(i).Hidden = True Then ListboxHidden.AddItem Cells(7, i).Value
        If Columns(i).Hidden = False Then ListboxVisible.AddItem Cells(7, i).Value
    Next
End Sub

Private Sub MoveItem(X As Long)
    Dim itms As String, i As Long, lbVal As String, newPos As Long
'move the item
    With Me.ListboxVisible
        If .ListIndex > -1 Then
            newPos = .ListIndex + X
            If newPos < 0 Or newPos = .ListCount Then Exit Sub
            lbVal = .Value
            itms = .List(newPos)
            .List(newPos) = .List(.ListIndex)
            .List(.ListIndex) = itms
        End If
're-select original item
        For i = 0 To .ListCount - 1
            If .List(i) = lbVal Then .Selected(i) = True
        Next i
     End With
End Sub