Moving through a listbox using spinbutton.

[marlonsaveri]

Hi, I wish one could told me how we move through a listbox using spinbutton, ie, when we click in the spin button, we go up or down the list box.

 

[Norie]

Next post is the right one.

 

[Norie]

Just use the value of the spinbutton to set the ListIndex of the listbox.

If you are using a userform with a spinbutton and a listbox you could try this.

Private Sub SpinButton1_Change()

    If SpinButton1.Value = 0 Then SpinButton1.Value = 10
    
    ListBox1.ListIndex = ListBox1.ListCount - SpinButton1.Value
    
    
    Label1.Caption = SpinButton1.Value

End Sub
 
Private Sub UserForm_Initialize()
 
    ListBox1.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    
    SpinButton1.Max = ListBox1.ListCount
    SpinButton1.Min = 0
    
    SpinButton1.Value = 10
    
End Sub

 

[marlonsaveri]

Thanks, I did:

Private Sub SpinButton1_SpinDown()
    If ListBox1.ListIndex > 0 Then
        ListBox1.Selected(ListBox1.ListIndex - 1) = True
    End If
End Sub
 
Private Sub SpinButton1_SpinUp()
    If ListBox1.ListIndex + 1 < ListBox1.ListCount Then
        ListBox1.Selected(ListBox1.ListIndex + 1) = True
    End If
End Sub

I'll learn your code to change from 0 to listbox1.listcount.

 

[marlonsaveri]

I wanna also know, what could I do to:

when I'm in a textbox and make "enter" on the keyboard, how to SpinUp?

 

[marlonsaveri]

Thanks, your way is so easier.

But, textbox1 just get the focus every two times that I click the spinbutton, not everytime. Why?

Private Sub ListBox1_Change()
    SpinButton1.Value = ListBox1.ListIndex
    TextBox1.Text = TheValue(ListBox1.ListIndex, 2)
    TextBox1.SetFocus
End Sub

Private Sub SpinButton1_Change()
    
    If SpinButton1.Value > ListBox1.ListCount Then
        SpinButton1.Value = ListBox1.ListCount
    End If
    
    If ListBox1.ListCount > SpinButton1.Value And SpinButton1.Value >= 0 And SpinButton1.Value <= ListBox1.ListCount Then
        ListBox1.ListIndex = SpinButton1.Value
        Label1.Caption = SpinButton1.Value
    End If
    
End Sub

 

[Norie]

Why are you setting focus on the textbox?

 

[marlonsaveri]

'cause I want "select" the textbox to write things about next property. I created a sample but I don't know how to attach here.

Dim MyMtx() As String

Private Sub SpinButton1_Change()
 On Error GoTo trataerro1
    If SpinButton1.Value > ListBox1.ListCount Then
        SpinButton1.Value = ListBox1.ListCount
    End If
    
    If ListBox1.ListCount > SpinButton1.Value And SpinButton1.Value >= 0 And SpinButton1.Value <= ListBox1.ListCount Then
        ListBox1.ListIndex = SpinButton1.Value
    End If
    TextBox1.SetFocus
trataerro1:
End Sub

Private Sub UserForm_Initialize()
On Error GoTo trataerro2
   
    Dim j As Integer
    Dim i As Integer
    

'   Title of the form
    Me.Caption = ActiveSheet.Name

'   Listbox
    final = Sheets("SampleName").Range("E" & Rows.Count).End(xlUp).Row + Sheets("SampleName").Range("N" & Rows.Count).End(xlUp).Row - 2
    ReDim MyMtx(final, 2) As String
    
    For i = 0 To Sheets("SampleName").Range("E" & Rows.Count).End(xlUp).Row - 2
        MyMtx(i, 1) = Sheets("SampleName").Range("E2").Offset(i, 0)
    Next i
    
    j = 0
    For i = Sheets("SampleName").Range("E" & Rows.Count).End(xlUp).Row - 1 To final - 1
        MyMtx(i, 1) = Sheets("SampleName").Range("N2").Offset(j, 0)
        j = j + 1
    Next i
    
    For i = 0 To final - 1
        ListBox1.AddItem MyMtx(i, 1)
    Next i
        ListBox1.Selected(0) = True
        
trataerro2:
End Sub

Private Sub CommandButton1_Click()
    On Error GoTo trataerro3

    Dim valor As String
    Dim busca As Range
    Dim SearchedWord As String
    Dim planilha As Worksheet



    'Save values on the sheet
    Set planilha = ActiveWorkbook.ActiveSheet
    planilha.Range("A1").FormulaR1C1 = BoxCity.Text
    'etc
    
    For i = 0 To ListBox1.ListCount     'for each prop...
    
        SearchedWord = MyMtx(i, 1)  'Find the prop on the sheet
        Set busca = ActiveWorkbook.ActiveSheet.Cells.Find(what:=SearchedWord, after:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

        If Not busca Is Nothing Then        'If something is found...

                valor = Replace(MyMtx(i, 2), ".", ",") 'We correct commas and dots...


            If Not valor = "" Then 'and paste on the sheet.
                Cells(busca.Row, busca.Column + 3).FormulaR1C1 = valor
            End If

        End If
    Next i
    Unload AddProp
trataerro3:
End Sub

Private Sub ListBox1_Change()
On Error GoTo trataerro4
    'Change the frame
    Frame1.Caption = ListBox1.Text
    'Correct spinbutton (it happens when we click on the listbox)
        SpinButton1.Value = ListBox1.ListIndex
    'Save the value on the array...
    TextBox1.Text = MyMtx(ListBox1.ListIndex, 2)
    'Get focus
    TextBox1.SetFocus
trataerro4:
End Sub

Private Sub TextBox1_Change()
On Error Resume Next
    MyMtx(ListBox1.ListIndex, 2) = TextBox1.Text
End Sub