row navigation with spin button

[Mileshigh]

ok, so its been a while since i have had to use excel and vba to build forms, most of the time I use visual basic.net these days but im trying to get this working.

I have an excel sheet that has a user form that a person can open and look at fields in a contained form much like they fill out on paper. I have the first cell working but need to figure out how to use a spin button to make the active cell go up or down so that the form will change. Here is my code as of right now.

Public Sub UserForm_Initialize()


Worksheets("sheet1").Range("A4").Select


 
lstSupplierName.Value = ActiveCell.Value
lstsuppliernumber.Value = ActiveCell.Offset(0, 1).Value
lstsampleDate.Value = ActiveCell.Offset(0, 3).Value
txtflakeIV.Value = ActiveCell.Offset(0, 4).Value
txthighP.Value = ActiveCell.Offset(0, 8).Value
txtruntime.Value = ActiveCell.Offset(0, 9).Value
txtcookL.Value = ActiveCell.Offset(0, 10).Value
txtcookA.Value = ActiveCell.Offset(0, 11).Value
txtcookB.Value = ActiveCell.Offset(0, 12).Value
txtcookWI.Value = ActiveCell.Offset(0, 13).Value
txtprebL.Value = ActiveCell.Offset(0, 14).Value
txtprebA.Value = ActiveCell.Offset(0, 15).Value
txtprebB.Value = ActiveCell.Offset(0, 16).Value
txtprebWI.Value = ActiveCell.Offset(0, 17).Value
txtposbL.Value = ActiveCell.Offset(0, 18).Value
txtposbA.Value = ActiveCell.Offset(0, 19).Value
txtposbB.Value = ActiveCell.Offset(0, 20).Value
txtposbWI.Value = ActiveCell.Offset(0, 21).Value
txtpre1.Value = ActiveCell.Offset(0, 22).Value
txtpre2.Value = ActiveCell.Offset(0, 23).Value
txtpre3.Value = ActiveCell.Offset(0, 24).Value
txtpre4.Value = ActiveCell.Offset(0, 25).Value
txtpre5.Value = ActiveCell.Offset(0, 26).Value
txtpre6.Value = ActiveCell.Offset(0, 27).Value
txtpre7.Value = ActiveCell.Offset(0, 28).Value
txtpre8.Value = ActiveCell.Offset(0, 29).Value
txtpos1.Value = ActiveCell.Offset(0, 31).Value
txtpos2.Value = ActiveCell.Offset(0, 32).Value
txtpos3.Value = ActiveCell.Offset(0, 33).Value
txtpos4.Value = ActiveCell.Offset(0, 34).Value
txtpos5.Value = ActiveCell.Offset(0, 35).Value
txtpos6.Value = ActiveCell.Offset(0, 36).Value
txtpos7.Value = ActiveCell.Offset(0, 37).Value
txtpos8.Value = ActiveCell.Offset(0, 38).Value
 
end sub

Thanks
Brett

 

[Mileshigh]

Ok so what if I just added a button for next and another button for previous? how could i have the active cell move up or down and refresh all my values in the user form?

 

[Noz2k]

How about something along the lines of

Dim lngLastRow As Long
    lngLastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
 
 
    1stSupplierName.List = Worksheets("Sheet1").Range("A4:A" & lngLastRow).Value

in your form initialise

and then in your 1stSupplierName_Change

If 1stSupplierName.List <> -1 Then
lstsuppliernumber.Value = Range("A" & 1stSupplierName.ListIndex + n).Offset(, 1)
lstsampleDate.Value = Range("A" & 1stSupplierName.ListIndex + 3).Offset(, 3)
etc

Think that would work if you made 1st SupplierName a combobox. Or you could just could into your Next button to add 1 to the ListIndex I think.