spin button help

[yves_sater]

i would like to do smthg like this:

Option Explicit

Private Sub ListBox1_Change()
Dim I As Integer
For I = 1 To 4
Me.Controls("TextBox" & I).Value = Range("A" & ListBox1.ListIndex + 2).Offset(, I)
Next I
End Sub

Private Sub SpinButton1_Change()
If ListBox1.ListIndex <> ListBox1.ListCount - 1 Then
ListBox1.ListIndex = SpinButton1.Value
Else
ListBox1.ListIndex = 0
SpinButton1.Value = 0
End If
End Sub
Private Sub UserForm_Initialize()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

ListBox1.RowSource = "A2:A" & LastRow

SpinButton1.Min = 0
SpinButton1.Max = LastRow

End Sub

by using only one spin button i can scroll down and up all vlaues of the textboxes and comboboxes that i have in my userform....can anyone help me plz

 

[bobsan42]

if the Next and previous programming is finished why don't you just put their code into the spin button:
first of course you must set the limits of the spin button:
(it should be ok in UserForm_Initialize() )
SpinButton1.Min = 6 (this i see from previous button)
SpinButton1.Max = LastRow (you will have to decide how much it is)
then:

Private Sub SpinButton1_AfterUpdate()
    ' Set row number:
    lCurrentRow = Me.SpinButton1.Value
    ' Show contents of row in the form:
    LoadRow
End Sub

 

[yves_sater]

how do i put the codes of the next and previous within one spin button

 

[yves_sater]

by the way iv done the way u just mentioned n nothin happened when pressing on the spin button

 

[yves_sater]

did u try it on my workbook n it worked?? just to c if it s smthg im doing wrong or it s from the codes

 

[bobsan42]

how do you show Userform1
and do the Next and Previous buttons work for you (because they don't work for me)?
oops.
they really do something but only after you select a record from the dropdown list.

Private Sub SpinButton1_AfterUpdate()
    ' Set row number:
    lCurrentRow = Me.SpinButton1.Value
    ' Show contents of row in the form:
    LoadRow
End Sub</pre>

but first you have to define MIN and MAX for the Spin button

i am also receiving an error message about a missing DLL which probably will limit the functionality of your code for me

 

[yves_sater]

to run the userform1 u click in the Purchase Order Database sheet the "Run Preview" on the top of the page....

the next and previous buttons are working good in my workbook ...wen u open the userform u enter a value click add n then click next or previous it s working properly..

so im not gettin where exactly is the problem..n please :$:$ im a beginner with vba language so if u may be more easy with the terms :$:$:$:

Again thank you very much

 

[yves_sater]

look im gonna send u by email a workbook that i got over the internet, a sample of the spin button doing wat i want it to do with my workbook...

so if u can manipulate and edit the codes from it and adjust them to my workbook it would be great coz i m not understand the language to be able to change it..

so may i send it to u??

 

[bobsan42]

yes i got it
it contains the code you posted
i get all this but since it's not a workbook i created it takes me some time to figure it out.
the second file is clean and small and all is clear.
i will try to help but maybe tomorrow (i doubt that i'll find time this evening) - time's running out as the day slips away
i just have to figure out all the connections and conditions to apply the changes.
sorry if this delays you.
gotta run now.
cheers.

 

[yves_sater]

thx for everything .... we ll continu tomorw

 

[bobsan42]

here we are:
remmove all the code from USERFORM1 and paste the code below in it's place:

Dim lCurrentRow As Long[COLOR=Red], RowsMax As Long, RowsMin As Long[/COLOR]
Dim dWidth As Double

Option Explicit

Private Sub cmdPrev_Click()
    ' Show previous only if not already in first row:
    If lCurrentRow > 6 Then
        ' Decrement row number:
        lCurrentRow = lCurrentRow - 1
        ' Show contents of row in the form:
        LoadRow
    End If
End Sub

Private Sub cmdNext_Click()
    ' Increment row number:
    lCurrentRow = lCurrentRow + 1
    ' Show contents of row in the form:
    LoadRow
End Sub

Private Sub cmdDelete_Click()
    Dim smessage As String
    smessage = "Are you sure you want to delete " + Textitem.Text + "?"
    If MsgBox(smessage, vbQuestion + vbYesNo, _
              "Confirm Delete") = vbYes Then
        ' Delete current row
        Rows(lCurrentRow).Delete
        ' Show contents of new current row in the form:
        LoadRow
    End If
End Sub

Private Sub cmdAdd_Click()
    ' Save form contents before changing rows:
    SaveRow
    ' Clear the form for user to add new name:
    LoadRow
    ' Set focus to Name textbox:
    Textitem.SetFocus
End Sub


[COLOR=Red]Private Sub SpinButton1_SpinDown()
lCurrentRow = lCurrentRow + 1
If lCurrentRow > RowsMax Then lCurrentRow = RowsMin
LoadRow
End Sub

Private Sub SpinButton1_SpinUp()
lCurrentRow = lCurrentRow - 1
If lCurrentRow < RowsMin Then lCurrentRow = RowsMax
LoadRow
End Sub[/COLOR]

Private Sub cmdClose_Click()
    Unload Me ' Close the form
End Sub

Private Sub LoadRow()
    POnumber.Text = Cells(lCurrentRow, 20).Value
    Textitem.Text = Cells(lCurrentRow, 2).Value
    Supplier.Text = Cells(lCurrentRow, 3).Value
    TextQty.Text = Cells(lCurrentRow, 4).Value
    TextPpU.Text = Cells(lCurrentRow, 5).Value
    Combcurruncy.Text = Cells(lCurrentRow, 6).Value
    CombTVA.Text = Cells(lCurrentRow, 9).Value
    TextDoO.Text = Cells(lCurrentRow, 10).Value
    TextDoD.Text = Cells(lCurrentRow, 11).Value
    CombDelivery.Text = Cells(lCurrentRow, 12).Value
    TextDesc1.Text = Cells(lCurrentRow, 13).Value
    CombPurchstype.Text = Cells(lCurrentRow, 14).Value
    TextDescr2.Text = Cells(lCurrentRow, 15).Value
    Comboorder.Text = Cells(lCurrentRow, 16).Value
    Combproject.Text = Cells(lCurrentRow, 17).Value
    TextFileRef.Text = Cells(lCurrentRow, 18).Value
    TextNotes.Text = Cells(lCurrentRow, 19).Value
    Pmtstatus.Text = Cells(lCurrentRow, 21).Value
    Adddelivery.Text = Cells(lCurrentRow, 22).Value
End Sub

Private Sub SaveRow()
    Cells(lCurrentRow, 20).Value = POnumber.Text
    Cells(lCurrentRow, 2).Value = Textitem.Text
    Cells(lCurrentRow, 3).Value = Supplier.Text
    Cells(lCurrentRow, 4).Value = TextQty.Text
    Cells(lCurrentRow, 5).Value = TextPpU.Text
    Cells(lCurrentRow, 6).Value = Combcurruncy.Text
    Cells(lCurrentRow, 9).Value = CombTVA.Text
    Cells(lCurrentRow, 10).Value = TextDoO.Text
    Cells(lCurrentRow, 11).Value = TextDoD.Text
    Cells(lCurrentRow, 12).Value = CombDelivery.Text
    Cells(lCurrentRow, 13).Value = TextDesc1.Text
    Cells(lCurrentRow, 14).Value = CombPurchstype.Text
    Cells(lCurrentRow, 15).Value = TextDescr2.Text
    Cells(lCurrentRow, 16).Value = Comboorder.Text
     Cells(lCurrentRow, 17).Value = Combproject.Text
    Cells(lCurrentRow, 18).Value = TextFileRef.Text
       Cells(lCurrentRow, 19).Value = TextNotes.Text
       Cells(lCurrentRow, 21).Value = Pmtstatus.Text
       Cells(lCurrentRow, 22).Value = Adddelivery.Text
End Sub

Private Sub UserForm1_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the button!"
  End If
End Sub

[COLOR=Red]Private Sub UserForm_Initialize()
    ' Read initial values from Row 1:
    RowsMax = Range("A" & Rows.Count).End(xlUp).Row
    RowsMin = 6
    lCurrentRow = RowsMin
    LoadRow
End Sub[/COLOR]

Private Sub UserForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the close button!"
  End If
End Sub

Private Sub ToggleButton1_Click()

    If ToggleButton1.Value = True Then
        Me.Height = Me.Height * 0.11
    Else
        Me.Height = dWidth
    End If

End Sub

Private Sub cmdClear_Click()
   Textitem.Value = Null
   Supplier.Value = Null
   TextQty.Value = Null
   TextPpU.Value = Null
   Combcurruncy.Value = Null
   CombTVA.Value = Null
   TextDoO.Value = Null
   TextDoD.Value = Null
   CombDelivery.Value = Null
   TextDesc1.Value = Null
   CombPurchstype.Value = Null
   TextDescr2.Value = Null
   Comboorder.Value = Null
   Combproject.Value = Null
   TextFileRef.Value = Null
   TextNotes.Value = Null
   Adddelivery.Value = Null
   Pmtstatus.Value = Null
End Sub

- tested
- working
- nothing else is broken just the spin works as you want it to

even though i tested it - let me know anyway

the bits i put are colored, some are remove (they are not colored)

cheers