UserForms ListIndex weird issue

---- ANOTHER EDIT -----

I'll try to sum up the whole thing to you guys, than maybe someone can help me.

Although the

Userform.Listbox.ListIndex = 1

and the

Userform.Listbox.Selected(1) = True

works on this listbox, depending on which list item I'm trying to select It just won't work.

Whenever I try to use variables insted of the "1", it won't work, mostly the times or situations.

If I try to run any operation with the variable it just won't work (Like A UserForm.ListBox.ListCount - 1, wouldn't work)

Whenever I try to select the last item it won't work

-----------------------------------

Hi Guys..

I'm having this unusual trouble with the listindex property of a listbox on an userform.

The code below search for a range and refreshes the listbox with the range values.

Sub Form_Esquadrias_Atualiza(IndexCache As Integer)

    Dim Aux As Worksheet
    Dim Cont1 As Integer
    Dim ListaRange As Range
    Dim ActSheet As Worksheet
    Dim Lista As MSForms.ListBox
    
    Set Aux = Sheets("AUXILIAR")
    Set ActSheet = ActiveSheet

    Call Form_Calcular_Esquadrias

    Cont1 = 1
    Do While Aux.Cells(Cont1, 11).Value <> Empty
        Cont1 = Cont1 + 1
    Loop
    Cont1 = Cont1 - 1
    If Cont1 = 0 Then Form_Esquadrias.Esq_Lista.Clear
    
    If Cont1 > 0 Then
        Application.ScreenUpdating = False
        Aux.Visible = True
        Aux.Select
        Set ListaRange = Aux.Range(Cells(1, 11), Cells(Cont1, 15))
        Set Lista = Form_Esquadrias.Esq_Lista
        With Lista
            .List = ListaRange.Cells.Value
            .ColumnCount = 5
            .ColumnWidths = "28;40;40;40;30"
        End With
        ActSheet.Select
        Aux.Visible = False
        Application.ScreenUpdating = True
    End If
    
    Lista.ListIndex = IndexCache
    
End Sub

The sub routine that I'm fireing in the midle of the code above can be seen here:

Sub Form_Calcular_Esquadrias()

    Dim Aux As Worksheet
    Dim Lanca As Worksheet
    Dim Resumo As Worksheet
    Dim ChkReset As Integer
    Dim Cont1 As Integer
    Dim Ini1 As Integer
    Dim Fim1 As Integer

    Set Aux = Sheets("AUXILIAR")
    Set Lanca = Sheets("Lançamento")
    Set Resumo = Sheets("Resumo")
    
    Cont1 = 1
    Do While Lanca.Cells(Cont1, 2).Value <> "#2"
        If Lanca.Cells(Cont1, 2).Value = "#1" Then Ini1 = Cont1 + 2
        If Lanca.Cells(Cont1 + 1, 2).Value = "#2" Then Fim1 = Cont1
        Cont1 = Cont1 + 1
    Loop
    
    Cont1 = 1
    Do While Resumo.Cells(Cont1, 2).Value <> "#2"
        If Resumo.Cells(Cont1, 2).Value = "#1" Then Ini2 = Cont1 + 2
        If Resumo.Cells(Cont1 + 1, 2).Value = "#2" Then Fim2 = Cont1
        Cont1 = Cont1 + 1
    Loop
    
    Aux.Columns(15).ClearContents
    
    Cont3 = 1
    Do While Aux.Cells(Cont3, 11).Value <> Empty
        For Cont1 = Ini1 To Fim1
            Col = 13
            Do While Col <> 0
                If Aux.Cells(Cont3, 11).Value = Lanca.Cells(Cont1, Col) Then
                    QtdCache = 0
                    For Cont2 = Ini2 To Fim2
                        If Lanca.Cells(Cont1, 3).Value = Resumo.Cells(Cont2, 3).Value Then
                            QtdCache = Resumo.Cells(Cont2, 5).Value
                            Cont2 = Fim2 + 1
                        End If
                    Next Cont2
                    Aux.Cells(Cont3, 15) = Aux.Cells(Cont3, 15).Value + Lanca.Cells(Cont1, Col + 1).Value * QtdCache
                End If
                If Col = 17 Then Col = 0
                If Col = 13 Then Col = 17
            Loop
        Next Cont1
        Cont3 = Cont3 + 1
    Loop

End Sub

On my form, I got 2 command buttons which I use to change the position of an Item on the predeterminated range and, I fire the listbox refreshing code.

One of the buttons, sends the selected listindex up and the other one sends it down.

the "send up" code can be seen here:

Private Sub Sobe_Click()

    Dim Aux As Worksheet
    Dim Cache As String
    Dim LiCache As Integer
    
    Set Aux = Sheets("AUXILIAR")

    If Form_Esquadrias.Esq_Lista.ListIndex < 1 Then Exit Sub
    LiCache = Esq_Lista.ListIndex - 1
    For Col = 11 To 13
        Cache = Aux.Cells(Form_Esquadrias.Esq_Lista.ListIndex + 1, Col).Value
        Aux.Cells(Form_Esquadrias.Esq_Lista.ListIndex + 1, Col) = Aux.Cells(Form_Esquadrias.Esq_Lista.ListIndex, Col).Value
        Aux.Cells(Form_Esquadrias.Esq_Lista.ListIndex, Col) = Cache
    Next Col

    Call Form_Esquadrias_Atualiza(LiCache)
    
End Sub

The thing is, as you can see, the LiCache variable, which can be seen in the last line of the code above, is the ListIndex of the selected item after I changed its position up (ex.: ListIndex - 1), and, in the first code I wrapped, I try to keep the previously selected item still selected.

I have oftenly used this kind of control on my listboxes, but this time IT JUST DOESN'T WORK.

I have tested it so many times and it even stores the right value in the LiCache variable and also changes the listbox.listindex to the desired value, but it doesn't select the item.

I hope I made myself clear.

Thanks for your attention.

Att. Daniel

--------- EDIT -----------
The UserForm can be seen on the attached picture. The two buttons I was Talking about are the /\ and \/ last two.

Attach an example workbook, not a picture

Private Sub Sobe_Click()
  If Esq_Lista.ListIndex = -1 Then Exit Sub
    
  With Sheets("AUXILIAR")
    sn = .Cells(Esq_Lista.ListIndex + 1, 11).resize(,3)
    .Cells(Esq_Lista.ListIndex + 1, 11).resize(,3) = .Cells(Esq_Lista.ListIndex, 11).resize(,3).Value
    .Cells(Esq_Lista.ListIndex, 11).resize(,3) = sn
  End with

  Form_Esquadrias_Atualiza  Esq_Lista.ListIndex - 1
End Sub

Sub Form_Esquadrias_Atualiza(IndexCache As Integer)
  Form_Calcular_Esquadrias

  With Form_Esquadrias.Esq_Lista
    .list=Sheets("AUXILIAR").cells(1,11).currentregion.resize(,5).value
    .ColumnCount = 5
    .ColumnWidths = "28;40;40;40;30"
    .listindex=indexcache
  End With
End Sub

Originally Posted by royUK

Attach an example workbook, not a picture

I can't. It's a worksheet from the company I work for. I could create a similar one, which I did, but in the other worksheet the problem just didn't happened.

Originally Posted by snb

Private Sub Sobe_Click()
  If Esq_Lista.ListIndex = -1 Then Exit Sub
    
  With Sheets("AUXILIAR")
    sn = .Cells(Esq_Lista.ListIndex + 1, 11).resize(,3)
    .Cells(Esq_Lista.ListIndex + 1, 11).resize(,3) = .Cells(Esq_Lista.ListIndex, 11).resize(,3).Value
    .Cells(Esq_Lista.ListIndex, 11).resize(,3) = sn
  End with

  Form_Esquadrias_Atualiza  Esq_Lista.ListIndex - 1
End Sub

Sub Form_Esquadrias_Atualiza(IndexCache As Integer)
  Form_Calcular_Esquadrias

  With Form_Esquadrias.Esq_Lista
    .list=Sheets("AUXILIAR").cells(1,11).currentregion.resize(,5).value
    .ColumnCount = 5
    .ColumnWidths = "28;40;40;40;30"
    .listindex=indexcache
  End With
End Sub

I have tried this routines, but the problem persisted.



Anyway, I kinda solved it. It had something to do with the .ColumnWidth property.

I solved it by changing the ColumnWidth to 28 pt;40 pt;40 pt;40 pt;30 pt as the standard width. And removed that line from the routine. After that, It worked fine.

It's not the brightest solution, but at least it's a solution.

If anyone knows why this .ColumnWidth property remove the selection from the listbox and won't let me select listbox entries through the routine let me know.

Thanks for your help guys

---- EDIT ----
Btw, analizing snbs code, Is there anyway to select a determinated Region without selecting the Worksheet it lies in?

I ask that because I noticed that there are a few actions that make the whole routine slow, like, copying, inserting, formating and changing through worksheets as an example.

I always avoid using any of those actions because of that. Although, whenever you need to select a range, you must be in the right worksheet.