Solved

Next and Previous buttons

Posted on 2016-11-04
9
29 Views
Last Modified: 2016-11-04
Hi , How do I get this code for previous row entry to work and display in the text boxes.  This code is not working and obviously the reverse of this will be the next button. But until this works I cant get the NEXT button to work.  

Private Sub Cmdbutton_PREVIOUS_Click()

   Dim ws As Worksheet
Set ws = Worksheets("Blueteq")

Dim newRow As Long
newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1

CurrentRow = CurrentRow - 1
If CurrentRow > 1 Then

 
 TextBox_Initials = Cells(CurrentRow, 1)
 TextBox_HNUMBER = Cells(CurrentRow, 2)
DTPicker_DOB = Cells(CurrentRow, 3)
TextBox_NHSNUMBER = Cells(CurrentRow, 4)
TextBox_GPNUMBER = Cells(CurrentRow, 5)
ComboBox_DRUG = Cells(CurrentRow, 6)
ComboBox_INDICATION = Cells(CurrentRow, 7)
DTPicker_STARTDATE = Cells(CurrentRow, 8)
TextBox_CONSULTANT = Cells(CurrentRow, 9)
Me.DTPicker_DATEADDED = Cells(CurrentRow, 10)
 ComboBox_Blueteqdrug = Cells(CurrentRow, 11)
 ComboBox_FORMLIST = Cells(CurrentRow, 12)
TextBox_BLUETEQID = Cells(CurrentRow, 15)
 DTPicker_APPROVALDATE = Cells(CurrentRow, 16)
TextBox_DOCTOR = Cells(CurrentRow, 17)
ComboBox_STATUS = Cells(CurrentRow, 18)
TextBox_EMAIL = Cells(CurrentRow, 19)
TextBox_Commisioners = Cells(CurrentRow, 20)

End If
If CurrentRow = 1 Then


CurrentRow = CurrentRow + 1
 End If
End Sub
0
Comment
Question by:Sam Coombes
  • 5
  • 4
9 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
How about a sample sheet for testing?
0
 

Author Comment

by:Sam Coombes
Comment Utility
Hi , Here is the spreadsheet

Thank You so much
Blueteqv4-email-v1.xlsm
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
Replace the above code with
Private Sub Cmdbutton_PREVIOUS_Click()

Dim ws As Worksheet
Set ws = Worksheets("Blueteq")

Dim newRow As Long
'newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
currentrow = ActiveCell.Row - 1
If currentrow < 7 Then
    currentrow = 7
End If
ws.Cells(currentrow, 1).EntireRow.Select
loadrowtoform (currentrow)
End Sub

Sub loadrowtoform(currentrow)
    TextBox_Initials = Cells(currentrow, 1)
    TextBox_HNUMBER = Cells(currentrow, 2)
    DTPicker_DOB = Cells(currentrow, 3)
    TextBox_NHSNUMBER = Cells(currentrow, 4)
    TextBox_GPNUMBER = Cells(currentrow, 5)
    ComboBox_DRUG = Cells(currentrow, 6)
    ComboBox_INDICATION = Cells(currentrow, 7)
    DTPicker_STARTDATE = Cells(currentrow, 8)
    TextBox_CONSULTANT = Cells(currentrow, 9)
    Me.DTPicker_DATEADDED = Cells(currentrow, 10)
    ComboBox_Blueteqdrug = Cells(currentrow, 11)
    ComboBox_FORMLIST = Cells(currentrow, 12)
    TextBox_BLUETEQID = Cells(currentrow, 15)
    DTPicker_APPROVALDATE = Cells(currentrow, 16)
    TextBox_DOCTOR = Cells(currentrow, 17)
    ComboBox_STATUS = Cells(currentrow, 18)
    TextBox_EMAIL = Cells(currentrow, 19)
    TextBox_Commisioners = Cells(currentrow, 20)

End Sub

Private Sub UserForm_Activate()
currentrow = ActiveCell.Row
If currentrow < 7 Then
    currentrow = 7
End If
Cells(currentrow, 1).EntireRow.Select
loadrowtoform (currentrow)

End Sub

Open in new window

0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
Comment Utility
When you load the form the current row would be displayed
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Sam Coombes
Comment Utility
That's works amazing but why doesn't  the add button work after you press the previous button
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
I did not work on any thing else. What is the add button supposed to do?
0
 

Author Closing Comment

by:Sam Coombes
Comment Utility
Thats fantastic works a treat, sorry I was just a little confused earlier but all fixed now
0
 

Author Comment

by:Sam Coombes
Comment Utility
Hi , Once again thank you so much but now do you have any idea how to get an upate/edit button to work
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
You need to explain what you want it to do.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now