Next / Previous Button Code

[ipbr21054]

Hi,
I have a database & userform im making but now stuck again.
On my userform i can click a button and the first record is imported from the database into the form.
I would now like to browse my other files hence record 3,4,5 etc with the use of Next / Previous buttons.
Can you advise a code for the Next / Previous files.

Thanks

 

[ipbr21054]

Ok,
Ive made an edit to cover the correct range.
A6-O6
Row count 15

I am happy with this so can you please make a few additions/edits if possible to the supplied code.

1,The customers details will always start from row A6,but i am able to click the previous button so the textbox can also show 5,4,3,2,1 etc.Can we make it 6 and not go below this.
2,When the data for the customer is shown in the listbox is it possible to also show the header from my database.
3,Can we style the text shown in the text box so i can alter font size & colour.
4,Some of the info in the text box is cut off,i mean if i should seeing say 123456789 i only see 1234567,am i able to resize the columns width.

Thanks very much for the help.

 

[My Aswer Is This]

You have asked several questions here:
To change Font type and size:
Click on the textbox or listbox or command button and right click choose properties and you will see Font in the listing.
Click on the little icon ... and then you can choose font type and size
To change color we have to use code in the inialize script. You will see I have done that I set it to red RGB 255,0,0
for the listbox.
You can change it to what you want if you need more help on that I can explain more later

To be able to see the header row we have to add another Listbox like the previous one you can just right click and drag the first one and it will make you a copy. Place it above the previous one Then in the script you will see I set the rowsource to "A1:O1" or what ever you want.

To keep the Previous button from going back further then row 6 you will see I added some code to the script that says:

If TextBox1.Value = 6 Then Exit sub that should solve that problem

The problem with column width you will see in the code I entered column with as 100 if you need it larger you can do so. It sets all the columns to the same width I see no way to modify each column.
I also change the rowsource to show all rows if you want. This way if you want you can scroll down the rows with the scroll bars on the far right of the Listbox.

Private Sub CommandButton2_Click()
'Next Button code
Dim one As Long
Dim Start As Long
one = TextBox1.Value + 1
ListBox1.RowSource = "A" & one & ":N" & one
TextBox1.Value = one
End Sub

Private Sub CommandButton3_Click()
'Previous Button Code
Dim one As Long
one = TextBox1.Value - 1
If TextBox1.Value = 6 Then Exit Sub
ListBox1.RowSource = "A" & one & ":N" & one
TextBox1.Value = one
End Sub

Private Sub UserForm_Initialize()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ListBox1.ColumnCount = 14
ListBox1.RowSource = "A6:O" & Lastrow ' With this change you will be able to see all the rows by using the scroll bar on listbox if you want
ListBox2.RowSource = "A1:O1"    'New list box you need to display Row headers
ListBox1.ForeColor = RGB(255, 0, 0) 'Text color for Listbox1
ListBox1.ColumnWidths = 100   'Listbox1 Column width change if needed
TextBox1.Value = "6"
End Sub

 

[dmt32]

Hi,
just seen this post & whilst may be on way to solution, just wondered have you tried using the built-in DataForm to view / edit your database sheet?

If still want a custom form to both navigate the database & edit record from your forms textboxes post back & will let you have code to do this.

Dave.

 

[ipbr21054]

Hi,

Yes please let me take a look.
Thanks

 

[dmt32]

Hi,

Yes please let me take a look.
Thanks

Hi,
Just so I understand your Form can you confirm that these are the textbox names & columns they get data from:-

 txtCustomer.Text = Range("A6").Value
txtRegistrationNumber.Text = Range("B6").Value
txtBlankUsed.Text = Range("C6").Value
txtVehicle.Text = Range("D6").Value
txtButtons.Text = Range("E6").Value
txtKeySupplied.Text = Range("F6").Value
txtTransponderChip.Text = Range("G6").Value
txtJobAction.Text = Range("H6").Value
txtProgrammerCloner.Text = Range("I6").Value
txtKeyCode.Text = Range("J6").Value
txtBiting.Text = Range("K6").Value
txtChassisNumber.Text = Range("L6").Value
txtJobDate.Text = Range("M6").Value
txtVehicleYear.Text = Range("N6").Value
txtPaid.Text = Range("O6").Value

Also, your records start in Row 6 (Header Row 5)?

& please provide the name of your Database worksheet

Dave

 

[ipbr21054]

Hi,
That code is correct.
First record starts at Row 6
Header Row A4
Name of worksheet is Database.

 

[dmt32]

Hi,
Firstly, make a BACKUP of your workbook.

Starting with your UserForm:

You will need THREE buttons named as follows:

· NextRecord
· PrevRecord
· UpdateRecord

Apply Captions to each button as appropriate.

Place ALL following code in your UserForms Code Page ensuring that you first delete any existing code with that has the same name.

Dim ws As Worksheet
Dim r As Long
Const StartRow As Long = 6


Private Sub NextRecord_Click()
    Navigate Direction:=xlNext
End Sub


Private Sub PrevRecord_Click()
    Navigate Direction:=xlPrevious
End Sub


Private Sub UpdateRecord_Click()
    Dim i As Integer
    For i = 1 To UBound(ControlNames)
        ws.Cells(r, i).Value = Me.Controls(ControlNames(i)).Text
    Next i
    
    MsgBox "Record Updated", 48, "Record Updated"
End Sub


Private Sub UserForm_Initialize()
    Set ws = ThisWorkbook.Worksheets("Database")
    'start at first record
    Navigate Direction:=xlFirst
End Sub




Sub Navigate(ByVal Direction As XlSearchDirection)
    Dim i As Integer
    Dim LastRow As Long
    
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    'set value of r
    r = IIf(Direction = xlPrevious, r - 1, r + xlNext)
    
    'ensure value of r stays within data range
    If r < StartRow Then r = StartRow
    If r > LastRow Then r = LastRow
    
    'get record
    For i = 1 To UBound(ControlNames)
        Me.Controls(ControlNames(i)).Text = ws.Cells(r, i).Text
    Next i
    
    'set enabled status of next previous buttons
    Me.NextRecord.Enabled = r < LastRow
    Me.PrevRecord.Enabled = r > StartRow


End Sub

Place following code In a STANDARD Module:

 Option Base 1
Function ControlNames() As Variant
ControlNames = Array("txtCustomer", "txtRegistrationNumber", "txtBlankUsed", "txtVehicle", _
                    "txtButtons", "txtKeySupplied", "txtTransponderChip", "txtJobAction", _
                    "txtProgrammerCloner", "txtKeyCode", "txtBiting", "txtChassisNumber", _
                    "txtJobDate", "txtVehicleYear", "txtPaid")
End Function

Note Option Base 1 statement – This MUST sit at the TOP of the Module – do not move or delete it.


Hopefully, when you open the form the first record will be displayed with the PrevRecord Button disabled.
Press NextRecord button should take you to the next Record ( and enable PrevRecord button) – doing this until you reach the end of the database where button will be disabled.

At any time you amend a record in the form – pressing UpdateRecord button should write the record back to the database & msgbox display to confirm.

Hope Helpful

Dave.

 

[ipbr21054]

WoW
This is perfect & just what i need.

Can you advise me some code please.

The form works great BUT i want to add a button called NEW CUSTOMER so when pressed it will clear all the text boxes so i can then enter a new customer & there details.
I would then need to press a button to do the following,
Insert new row at A6
Save these new customers details in to new A6 row just created.
Each time a new customer is added to my database it is put in Row A6 thus moving everybody else down 1 Row.

Thanks for a great piece of kit.

 

[dmt32]

WoW
This is perfect & just what i need.

Can you advise me some code please.

The form works great BUT i want to add a button called NEW CUSTOMER so when pressed it will clear all the text boxes so i can then enter a new customer & there details.
I would then need to press a button to do the following,
Insert new row at A6
Save these new customers details in to new A6 row just created.
Each time a new customer is added to my database it is put in Row A6 thus moving everybody else down 1 Row.

Thanks for a great piece of kit.

Hi,
glad solution worked ok for you & no problem with additional requirement.
I am about to head off out but will post back later with additional code.

Dave

 

[ipbr21054]

Ok
Thanks very much.