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 to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Norie

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,732
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

ADVERTISEMENT

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,732
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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,086
Messages
5,509,167
Members
408,711
Latest member
EMexcel

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top