Posted to microsoft.public.excel.programming
|
|
Spin button to scroll up&down a database with a userform
I would assume that "C" is Nothing and the code exits.
Cimjet"
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
"Jim Cone" wrote in message ...
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"
wrote in message
...
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
-------------------------------
Regards
Cimjet
|