help with 'advance to next record' code for button on userform

K

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
476
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have 2 userforms in my workbook. One is for entering a record, and the other is for editing/viewing a previously entered record.
I would like to add an additional commandbutton to the editing userform that will copy the contents of the form and then instead of unloading and exiting the form, it will then populate the form with the next record in the row directly beneath the row that was just updated... I have included a couple screen-shots to help make sense of what I am requesting as well as my existing code. Thank you

Column A is the column that contains the string that is searched for when the specific number is entered on the editing userform (editing form is in the next pic):

izrvjq.jpg


Here is the editing form with showing that ID# "19-493" is being entered into the txtIncidentID1 text field...

4ggop4.jpg


Upon entering the ID# "19-493" and exiting that field, the form is then populated with the contents from row 508 on the worksheet (which is the row that ID# 19-493 resides.)

2hrnzat.jpg


CODE SECTION:

here is my code for when when the 'editing' userform is opened: (txtIncidentID1 is the focus)

Code: 
[COLOR=#008000]' ********************************************************************************[/COLOR]
Private Sub UserForm_Initialize()
Worksheets("Seatex Incident Log").Activate
Me.cboPriority1.RowSource = "Priority_Lookup1"

Dim cLoc As Range
Dim cCus As Range
Dim cEnd As Range
Dim cIssu As Range
Dim cBeh As Range
Dim cIss As Range
Dim cIss2 As Range

Worksheets("Seatex Incident Log").Activate

Set ws = Worksheets("LookupLists")
Set ws1 = Worksheets("Seatex Incident Log")

With Me.cboLocation1                                    [COLOR=#008000]' Uses the Named Range "Locations" for the ComboBox[/COLOR]
    .AddItem "-"
    For Each cLoc In ws.Range("Locations")
        .AddItem cLoc.Value
    Next cLoc
End With
With Me.cboCustomer1                                   [COLOR=#008000] ' Uses the Named Range "Customers" for the ComboBox: "Customer"[/COLOR]
    .AddItem "-"
    For Each cCus In ws.Range("Customers")
        .AddItem cCus.Value
    Next cCus
End With
With Me.cboEndcustomer1                                   [COLOR=#008000] ' Uses the Named Range "Endcustomers" for the ComboBox: "cboEndustomer1"[/COLOR]
    .AddItem "-"
    For Each cEnd In ws.Range("Endcustomers")
        .AddItem cEnd.Value
    Next cEnd
End With
With Me.cboIssuedBy1                                [COLOR=#008000] ' Uses the Named Range "Names" for the ComboBox: "Issued by"[/COLOR]
    .AddItem "-"
    For Each cIssu In ws.Range("Names")
        .AddItem cIssu.Value
    Next cIssu
End With
With Me.cboOnBehalfOf1                                 [COLOR=#008000]' Uses the Named Range "Names" for the ComboBox: "On Behalf Of"[/COLOR]
    .AddItem "-"
    For Each cBeh In ws.Range("Names")
        .AddItem cBeh.Value
    Next cBeh
End With
With Me.cboIssuedTo1                                    [COLOR=#008000]' Uses the Named Range "Departments" for the ComboBox: "Issued To"[/COLOR]
    .AddItem "-"
    For Each cIss In ws.Range("Departments")
        .AddItem cIss.Value
    Next cIss
End With
With Me.cboIssuedTo21                                    [COLOR=#008000]' Uses the Named Range "Departments" for the ComboBox: "Issued To"[/COLOR]
    .AddItem "-"
    For Each cIss2 In ws.Range("Departments")
        .AddItem cIss2.Value
    Next cIss2
End With

Me.txtIncidentID1.SetFocus

End Sub
[COLOR=#008000]' ********************************************************************************[/COLOR]

This is the code for populating the form with the row contents from what matches with what is entered into the txtIncidentID1 field:

Code: 
Private Sub txtIncidentID1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
[COLOR=#008000]' Lookup Incident ID Number[/COLOR]

Dim cK1 As String
Dim cK2 As String

If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.txtIncidentID1.Value) = 0 Then

MsgBox "Incident ID not found."
Me.txtIncidentID1.Value = ""

Cancel = True

Exit Sub
End If

With Me
On Error Resume Next
.txtDateBox1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 2, 0)
.cboLocation1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 6, 0)
.cboPriority1 = "   " & Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 4, 0)
.txtCAPA1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 19, 0)
.cboCustomer1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 7, 0)
.cboEndcustomer1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 8, 0)
.txtProblem1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 9, 0)
.txtAction1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 10, 0)
.cboIssuedBy1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 11, 0)
.cboOnBehalfOf1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 12, 0)
.cboIssuedTo1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 13, 0)
.cboIssuedTo21 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 14, 0)
.txtNotes1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 20, 0)
.txtNotes1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 20, 0)
[COLOR=#008000]' ********************************************************************************[/COLOR]
cK1 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 15, 0)
If cK1 = "YES" Then .chkYes1 = True Else
If cK1 = "no" Then .chkNo1 = True Else
cK2 = Application.WorksheetFunction.VLookup((Me.txtIncidentID1), Sheet1.Range("DynamicRange"), 19, 0)
If cK2 = "No CAR" Then .chkCAPA1 = True Else
[COLOR=#008000]' ********************************************************************************[/COLOR]
Me.txtDateBox1 = Format(txtDateBox1.Text, "mm/dd/yyyy")
Me.txtProblem1.Locked = False
Me.txtProblem1.ScrollBars = fmScrollBarsVertical
Me.txtDateBox1.SetFocus
End With

End Sub

And finally this is the code for adding (copying over) the contents of the fields that currently populate the editing form back into the row that represents what is entered into txtIncidentID1 when the "cmdAdd1" button is selected:
Code: 
Private Sub cmdAdd1_Click()

Dim i As Long
Dim lCol As Long
Dim rCol As Long
        rCol = ActiveSheet.UsedRange.Rows.Count
        lCol = ActiveSheet.UsedRange.Rows.Count

Dim DynRng As Range, Inc_ID As String, R As Long
Set DynRng = Worksheets("Seatex Incident Log").Range("DynamicRange")
Inc_ID = txtIncidentID1
R = Application.WorksheetFunction.Match(Inc_ID, DynRng.Resize(, 1), 0)

DynRng.Cells(R, 2).Value = Me.txtDateBox1.Value
DynRng.Cells(R, 4).Value = Me.cboPriority1.Value
DynRng.Cells(R, 19).Value = Me.chkCAPA1.Value
DynRng.Cells(R, 6).Value = Me.cboLocation1.Value
DynRng.Cells(R, 7).Value = Me.cboCustomer1.Value
DynRng.Cells(R, 8).Value = Me.cboEndcustomer1.Value
DynRng.Cells(R, 9).Value = Me.txtProblem1.Value
DynRng.Cells(R, 10).Value = Me.txtAction1.Value
DynRng.Cells(R, 11).Value = Me.cboIssuedBy1.Value
DynRng.Cells(R, 12).Value = Me.cboOnBehalfOf1.Value
DynRng.Cells(R, 13).Value = Me.cboIssuedTo1.Value
DynRng.Cells(R, 14).Value = Me.cboIssuedTo21.Value
DynRng.Cells(R, 20).Value = Me.txtNotes1.Value
DynRng.Cells(R, 18).Value = Me.txtDateClosed1.Value

DynRng.Cells(R, 27).Value = Now

If chkCAPA1.Value = False Then
DynRng.Cells(R, 19).Value = Me.txtCAPA1.Value
Else
DynRng.Cells(R, 19).Value = "No CAR"
End If

If chkYes1.Value = True Then
DynRng.Cells(R, 15).Value = "Yes"
Else
DynRng.Cells(R, 15).Value = "No"
End If

Unload Me
Worksheets("Seatex Incident Log").Activate

End Sub

so, what I need is code for a new command button that does the same thing as the last code I posted here (cmdAdd1 button) but instead of closing the form, it then populates txtIncidentID1 with the contents of what is in column A in the next row that is directly below the row that was just updated... (so in this case, the next row would be 509 so it would automatically populate the txtIncidentID1 field with "19-494"... (and then the user would hit 'enter' and it would populate the form with the contents of row 509... Make sense...? so how can this be done? Thank you!

PS- row #18 is where the first row where the first ID# begins (and then goes down to the last row which has anything entered.. .which is currently 518, but is growing all the time.)
 
Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hi,
With big projects it is always helpful if you can place copy of your workbook with sample data in a dropbox & provide link to it as this will allow contributors to test ideas.

I have scanned your code & quickly come up with following but it is untested & will most likely need you to amend it to ensure it is returning correct data.


  • Make a back-up of your workbook
  • Add two command buttons to your form & name them
    • cmbNext
    • cmbPrevious
  • delete existing Sub txtIncidentID1_Exit code


Add following code to your userforms code page

Code: 
 Private Sub txtIncidentID1_Exit(ByVal Cancel As MSForms.ReturnBoolean)    
    Dim FoundCell As Range
    Dim Search As String
    
' Lookup Incident ID Number
    Search = Me.txtIncidentID1.Value
    
    Set FoundCell = Sheet1.Range("A:A").Find(Search, lookat:=xlWhole, LookIn:=xlValues)
    
    If Not FoundCell Is Nothing Then
        GetRecord FoundCell.Row
    Else
        MsgBox Search & Chr(10) & "Incident ID not found.", 48, "Not Found"
        Me.txtIncidentID1.Value = ""
        
        Cancel = True
    End If
    
End Sub




Sub GetRecord(ByVal RecordRow As Long)
    Dim sh As Worksheet
    Set sh = Sheet1
    With Me
        .txtIncidentID1.Value = sh.Cells(RecordRow, 1).Text
        .txtDateBox1 = sh.Cells(RecordRow, 2).Text
        .cboPriority1 = "   " & sh.Cells(RecordRow, 4)
        .cboLocation1 = sh.Cells(RecordRow, 6)
        .cboCustomer1 = sh.Cells(RecordRow, 7)
        .cboEndcustomer1 = sh.Cells(RecordRow, 8)
        .txtProblem1 = sh.Cells(RecordRow, 9)
        .txtAction1 = sh.Cells(RecordRow, 10)
        .cboIssuedBy1 = sh.Cells(RecordRow, 11)
        .cboOnBehalfOf1 = sh.Cells(RecordRow, 12)
        .cboIssuedTo1 = sh.Cells(RecordRow, 13)
        .cboIssuedTo21 = sh.Cells(RecordRow, 14)
        .txtCAPA1 = sh.Cells(RecordRow, 19)
        .txtNotes1 = sh.Cells(RecordRow, 20)
        
' ********************************************************************************
        .chkYes1 = CBool(UCase(sh.Cells(RecordRow, 15)) = "YES")
        .chkNo1 = CBool(UCase(sh.Cells(RecordRow, 15)) = "NO")
        .chkCAPA1 = CBool(sh.Cells(RecordRow, 19) = "No CAR")
' ********************************************************************************
        
        .txtProblem1.Locked = False
        .txtProblem1.ScrollBars = fmScrollBarsVertical
        .txtDateBox1.SetFocus
        
        With .cmdNext
            .Enabled = CBool(Len(sh.Cells(RecordRow + 1, 1).Value) > 0)
            .Tag = RecordRow
        End With
        .cmdPrevious.Enabled = CBool(RecordRow > 18)
    End With
    
End Sub


Private Sub cmdNext_Click()
    GetRecord Val(cmdNext.Tag + 1)
End Sub


Private Sub cmdPrevious_Click()
    GetRecord Val(cmdNext.Tag - 1)
End Sub

Hopefully, this will do what you want

Dave
 
Last edited:
Upvote 0
dmt32 said:
Hi,
With big projects it is always helpful if you can place copy of your workbook with sample data in a dropbox & provide link to it as this will allow contributors to test ideas.

I have scanned your code & quickly come up with following but it is untested & will most likely need you to amend it to ensure it is returning correct data.


  • Make a back-up of your workbook
  • Add two command buttons to your form & name them
    • cmbNext
    • cmbPrevious
  • delete existing Sub txtIncidentID1_Exit code


Add following code to your userforms code page

Code: 
 Private Sub txtIncidentID1_Exit(ByVal Cancel As MSForms.ReturnBoolean)    
    Dim FoundCell As Range
    Dim Search As String
    
' Lookup Incident ID Number
    Search = Me.txtIncidentID1.Value
    
    Set FoundCell = Sheet1.Range("A:A").Find(Search, lookat:=xlWhole, LookIn:=xlValues)
    
    If Not FoundCell Is Nothing Then
        GetRecord FoundCell.Row
    Else
        MsgBox Search & Chr(10) & "Incident ID not found.", 48, "Not Found"
        Me.txtIncidentID1.Value = ""
        
        Cancel = True
    End If
    
End Sub




Sub GetRecord(ByVal RecordRow As Long)
    Dim sh As Worksheet
    Set sh = Sheet1
    With Me
        .txtIncidentID1.Value = sh.Cells(RecordRow, 1).Text
        .txtDateBox1 = sh.Cells(RecordRow, 2).Text
        .cboPriority1 = "   " & sh.Cells(RecordRow, 4)
        .cboLocation1 = sh.Cells(RecordRow, 6)
        .cboCustomer1 = sh.Cells(RecordRow, 7)
        .cboEndcustomer1 = sh.Cells(RecordRow, 8)
        .txtProblem1 = sh.Cells(RecordRow, 9)
        .txtAction1 = sh.Cells(RecordRow, 10)
        .cboIssuedBy1 = sh.Cells(RecordRow, 11)
        .cboOnBehalfOf1 = sh.Cells(RecordRow, 12)
        .cboIssuedTo1 = sh.Cells(RecordRow, 13)
        .cboIssuedTo21 = sh.Cells(RecordRow, 14)
        .txtCAPA1 = sh.Cells(RecordRow, 19)
        .txtNotes1 = sh.Cells(RecordRow, 20)
        
' ********************************************************************************
        .chkYes1 = CBool(UCase(sh.Cells(RecordRow, 15)) = "YES")
        .chkNo1 = CBool(UCase(sh.Cells(RecordRow, 15)) = "NO")
        .chkCAPA1 = CBool(sh.Cells(RecordRow, 19) = "No CAR")
' ********************************************************************************
        
        .txtProblem1.Locked = False
        .txtProblem1.ScrollBars = fmScrollBarsVertical
        .txtDateBox1.SetFocus
        
        With .cmdNext
            .Enabled = CBool(Len(sh.Cells(RecordRow + 1, 1).Value) > 0)
            .Tag = RecordRow
        End With
        .cmdPrevious.Enabled = CBool(RecordRow > 18)
    End With
    
End Sub


Private Sub cmdNext_Click()
    GetRecord Val(cmdNext.Tag + 1)
End Sub


Private Sub cmdPrevious_Click()
    GetRecord Val(cmdNext.Tag - 1)
End Sub

Hopefully, this will do what you want

Dave
Click to expand...


Great information, thank you Dave.

And the code worked great. No changes necessary at all.... right out of the box & worked on the fisrt try!
 
Upvote 0
kbishop94 said:
Great information, thank you Dave.

And the code worked great. No changes necessary at all.... right out of the box & worked on the fisrt try!
Click to expand...

Hi,
Glad suggestion helped - I surprise myself sometimes

If all working ok then may be able to modify the cmdAdd1_Click code to use the updated solution

Code: 
Private Sub cmdAdd1_Click()
    Dim sh As Worksheet
    Dim RecordRow As Long
    
    Set sh = Worksheets("Seatex Incident Log")
    
    RecordRow = Val(Me.cmdNext.Tag)
    
    With sh
        .Cells(RecordRow, 2).Value = Me.txtDateBox1.Value
        .Cells(RecordRow, 4).Value = Me.cboPriority1.Value
        .Cells(RecordRow, 6).Value = Me.cboLocation1.Value
        .Cells(RecordRow, 7).Value = Me.cboCustomer1.Value
        .Cells(RecordRow, 8).Value = Me.cboEndcustomer1.Value
        .Cells(RecordRow, 9).Value = Me.txtProblem1.Value
        .Cells(RecordRow, 10).Value = Me.txtAction1.Value
        .Cells(RecordRow, 11).Value = Me.cboIssuedBy1.Value
        .Cells(RecordRow, 12).Value = Me.cboOnBehalfOf1.Value
        .Cells(RecordRow, 13).Value = Me.cboIssuedTo1.Value
        .Cells(RecordRow, 14).Value = Me.cboIssuedTo21.Value
        .Cells(RecordRow, 15).Value = IIf(chkYes1.Value, "Yes", "No")
        .Cells(RecordRow, 18).Value = Me.txtDateClosed1.Value
        .Cells(RecordRow, 19).Value = IIf(chkCAPA1.Value, "No CAR", Me.txtCAPA1.Value)
        .Cells(RecordRow, 20).Value = Me.txtNotes1.Value
        .Cells(RecordRow, 27).Value = Now
    End With
    
End Sub

add following as the LAST line in txtIncidentID1_Exit code

Code: 
Me.cmdAdd1.Enabled = Not Cancel

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
trying to get my userform to advance through visible records and not all records
Replies
3
Views
1K
Akuini
Akuini
K
  • Question Question
need help with my 'get record' sub when the target cell is a hyperlink...
Replies
0
Views
393
kbishop94
K
K
  • Question Question
what to have userform do when 'error' happens...
Replies
3
Views
592
Fluff
Fluff
K
  • Question Question
using Resize to define range for updating a COLUMN from userform
2
Replies
12
Views
895
Fluff
Fluff
K
  • Question Question
stuck trying to get userform to update record....
Replies
4
Views
599
Yongle
Yongle

Forum statistics

Threads
1,223,888
Messages
6,175,213
Members
452,618
Latest member
Tam84

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top