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

  • Thread starter Thread starter Cimjet
  • Start date Start date
C

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
 
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?
 
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]...
 
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
Jim Cone said:
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
Click to expand...
 
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
Click to expand...
 
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
 
Back
Top