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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,654
Office Version
365
Platform
Windows
Next post is the right one.:)
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,654
Office Version
365
Platform
Windows
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
 

marlonsaveri

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

marlonsaveri

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

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,654
Office Version
365
Platform
Windows
Why are you setting focus on the textbox?
 

marlonsaveri

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


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:

Watch MrExcel Video

Forum statistics

Threads
1,102,353
Messages
5,486,367
Members
407,542
Latest member
Tyronaught

This Week's Hot Topics

Top