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):
Here is the editing form with showing that ID# "19-493" is being entered into the txtIncidentID1 text field...
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.)
CODE SECTION:
here is my code for when when the 'editing' userform is opened: (txtIncidentID1 is the focus)
This is the code for populating the form with the row contents from what matches with what is entered into the txtIncidentID1 field:
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:
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.)
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):
Here is the editing form with showing that ID# "19-493" is being entered into the txtIncidentID1 text field...
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.)
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: