Solved

Next and Previous buttons

Posted on 2016-11-04
9
64 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41873728
How about a sample sheet for testing?
0
 

Author Comment

by:Sam Coombes
ID: 41873773
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
ID: 41873830
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 41873833
When you load the form the current row would be displayed
0
 

Author Comment

by:Sam Coombes
ID: 41873872
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
ID: 41873874
I did not work on any thing else. What is the add button supposed to do?
0
 

Author Closing Comment

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

Author Comment

by:Sam Coombes
ID: 41874338
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
ID: 41874598
You need to explain what you want it to do.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

691 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