How to associate SpinButton to a listbox?

[marlonsaveri]

Hello,

I have a form with a listbox. It has some properties and, using a "textbox_change", a array and a button "finish", I can save values to that properties in a sheet.

To facilitate handling, without having to keep clicking all the time in the listbox, I created a SpinButton and it's working - I use spinbutton1_SpinUp and _SpinDown, ex:

Private Sub SpinButton1_SpinUp()
    If ListBox1.ListIndex + 1 < ListBox1.ListCount Then
        ListBox1.Selected(ListBox1.ListIndex + 1) = True
    End If
    TextBox1.SetFocus
End Sub

However, I wish every time we type "enter", it goes to the next item on listbox. I tried, but I don't know how to call "_SpinDown" inside "Textbox1_enter()".

There must be a better way to solve it.

 

[Greg Truby]

I don't get that behavior when I try what you describe. My guess is that either something in your textbox_change event handler or some other textbox event handler is altering the listindex. If you're sure that's not the case then have a look at the tab order of your controls and which are or are not tab stops. You may inadvertantly be "clicking" another control.

If it's still a mystery, then set a breakpoint at the beginning of the textbox1_change event handler and step through the code. Before you do that - SAVE YOUR WORKBOOK. I cannot count how many times I have crashed Excel when stepping through code related to userform control event handlers. Things can get very, very twitchy.

 

[Greg Truby]

Shoulda added, when you get to the END SUB statement of the event handler that you're stepping through, hit the F5 key, not the F8 key. Otherwise you'll end up stepping through EVERY event handler.

Also, when you do this, you can set a watch on USERFORM1.LISTBOX.LISTINDEX (edit the userform name as appropriate) and birddog the listindex to see what's going on.

 

[marlonsaveri]

Thanks, I've changed the TabOrder and discovered that listbox1_change was altering somethings. Now, when I type "Tab" and "left or right", I can move, I think it's almost enough.

Just a last problem:

It's working:

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     
End If     
TextBox1.SetFocus      
End Sub

and

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

But, TextBox1.SetFocus is not working. I don't know why, it works just each two times I click on spinbutton, not every time I click.

 

[Greg Truby]

Typically in a situation like that I set some debug.print statements in the event handlers - something like:

debug.print "textbox1_change", me.textbox1.text

or

debut.print "spinbutton1_change", me.spinbutton.value

and use that to try and trace the flow of the event handlers as I manipulate controls.