Spin button to scroll up&down a database with a userform

[Cimjet]

Hi Everyone
The code below is not working.
What I'm trying to do is use a spin Button on a Userform to scroll up&down the
list. The main problem is having the data show up in the textbox on the form as
I scroll.
Can anyone help me with this.
Any advise will be appreciated.
-------------------------------------
Private Sub SpinButton1_Change()
Dim rSearch As Range
Application.ScreenUpdating = False
Range("A2:A250").Cells(SpinButton1.Max - SpinButton1.Value + 1, 1).Select
ActiveSheet.Select
strFind = Me.TextBox1.Value

With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then
c.Select

With Me 'load entry to form
.TextBox2.Value = c.Offset(0, 1).Value
.TextBox3.Value = c.Offset(0, 2).Value
.TextBox4.Value = c.Offset(0, 3).Value
.TextBox5.Value = c.Offset(0, 4).Value
.TextBox6.Value = c.Offset(0, 5).Value
.TextBox7.Value = c.Offset(0, 6).Value
.TextBox8.Value = c.Offset(0, 7).Value
.TextBox9.Value = c.Offset(0, 8).Value
.TextBox10.Value = c.Offset(0, 9).Value

End With
End If
Application.ScreenUpdating = True
End Sub

 

[AB]

You haven't specified what exactly isn't working but there are some
peculiarities in your code.
I didn't really read all your code through but where in the code do
you set the rSearch range?
You dimmension (Dim) it at the beginning but where do you set it?

 

[Jim Cone]

This may "work" better (not tested)...
'---
Private Sub SpinButton1_Change()
Dim C As Range
Dim rSearch As Range
Dim strFind As String

Application.ScreenUpdating = False
Set rSearch = Range("A2:A250").Cells(Me.SpinButton1.Max - Me.SpinButton1.Value + 1, 1)
strFind = Me.TextBox1.Value
On Error Resume Next
Set C = rSearch.Find(strFind, LookIn:=xlValues)
On Error GoTo 0

If Not C Is Nothing Then
With Me 'load entry to form
.TextBox2.Value = C.Offset(0, 1).Value
.TextBox3.Value = C.Offset(0, 2).Value
.TextBox4.Value = C.Offset(0, 3).Value
.TextBox5.Value = C.Offset(0, 4).Value
.TextBox6.Value = C.Offset(0, 5).Value
.TextBox7.Value = C.Offset(0, 6).Value
.TextBox8.Value = C.Offset(0, 7).Value
.TextBox9.Value = C.Offset(0, 8).Value
.TextBox10.Value = C.Offset(0, 9).Value
End With
End If
Application.ScreenUpdating = True
End Sub
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)




"Cimjet" <[email protected]>
wrote in message
news:[email protected]...

 

[Cimjet]

Hi Jim
Thanks for your reply.
I tried it but nothing is showing up on the Userform.
No error, just doing nothing, my textbox stays empty.
Regards
Cimjet

This may "work" better (not tested)...
'---
Private Sub SpinButton1_Change()
Dim C As Range
Dim rSearch As Range
Dim strFind As String

Application.ScreenUpdating = False
Set rSearch = Range("A2:A250").Cells(Me.SpinButton1.Max - Me.SpinButton1.Value
+ 1, 1)
strFind = Me.TextBox1.Value
On Error Resume Next
Set C = rSearch.Find(strFind, LookIn:=xlValues)
On Error GoTo 0

If Not C Is Nothing Then
With Me 'load entry to form
.TextBox2.Value = C.Offset(0, 1).Value
.TextBox3.Value = C.Offset(0, 2).Value
.TextBox4.Value = C.Offset(0, 3).Value
.TextBox5.Value = C.Offset(0, 4).Value
.TextBox6.Value = C.Offset(0, 5).Value
.TextBox7.Value = C.Offset(0, 6).Value
.TextBox8.Value = C.Offset(0, 7).Value
.TextBox9.Value = C.Offset(0, 8).Value
.TextBox10.Value = C.Offset(0, 9).Value
End With
End If
Application.ScreenUpdating = True
End Sub
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)




"Cimjet" <[email protected]>
wrote in message

 

[Jim Cone]

I would assume that "C" is Nothing and the code exits.


Cimjet" <[email protected]>
wrote in message

Hi Jim
Thanks for your reply.
I tried it but nothing is showing up on the Userform.
No error, just doing nothing, my textbox stays empty.
Regards
Cimjet

 

[Cimjet]

Hi Jim
I tried different things but without success.
I've got this macro that brings me up to the Start (First line) but the
Spinbutton does nothing.
Anymore ideas, I could try.
Private Sub cmnbFirst_Click()
Dim FirstCl As Range

'first data Entry
Set FirstCl = Range("a1").End(xlDown).Offset(1, 0) 'allow for rows being
added deleted above header row
With Me
.cmbAmend.Enabled = False
.cmbDelete.Enabled = False
.cmbAdd.Enabled = True
.TextBox1.Value = FirstCl.Value
.TextBox2.Value = FirstCl.Offset(0, 1).Value
.TextBox3.Value = FirstCl.Offset(0, 2).Value
.TextBox4.Value = FirstCl.Offset(0, 3).Value
.TextBox5.Value = FirstCl.Offset(0, 4).Value
.TextBox6.Value = FirstCl.Offset(0, 5).Value
.TextBox7.Value = FirstCl.Offset(0, 6).Value
.TextBox8.Value = FirstCl.Offset(0, 7).Value
.TextBox9.Value = FirstCl.Offset(0, 8).Value
.TextBox10.Value = FirstCl.Offset(0, 9).Value
End With
End Sub
Regards
Cimjet

 

[Jim Cone]

You should put a "stop" in your code and then step thru it using the F8 key.
That will allow you to see the values in your objects and variables and determine what is going on.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Bingo Card Variety - in the free folder)





"Cimjet" <[email protected]>
wrote in message

 

[Cimjet]

Thanks Jim
I'll do that and post back.
Cimjet

You should put a "stop" in your code and then step thru it using the F8 key.
That will allow you to see the values in your objects and variables and
determine what is going on.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Bingo Card Variety - in the free folder)





"Cimjet" <[email protected]>
wrote in message