Moving through a listbox using spinbutton.

marlonsaveri

Board Regular
Joined
Jan 28, 2011
Messages
68
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Next post is the right one.:)
 
Last edited:
Upvote 0
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.
Code:
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
 
Upvote 0
Thanks, I did:

Code:
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.
 
Upvote 0
Thanks, your way is so easier.

But, textbox1 just get the focus every two times that I click the spinbutton, not everytime. Why?
Code:
Private Sub ListBox1_Change()
    SpinButton1.Value = ListBox1.ListIndex
    TextBox1.Text = TheValue(ListBox1.ListIndex, 2)
    TextBox1.SetFocus
End Sub

Code:
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
 
Upvote 0
Why are you setting focus on the textbox?
 
Upvote 0
'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.
Capturar.PNG


Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top