add a Find/Update button to my userform1 that will allow me to find a report # in my worksheets, repopulate the userform.

[MacM109r]

I created a userform1 for data entry into my worksheet. I would like to add code to add a Find button that would find a report number in my workssheet and repopulate the userform1 pulling in the entire row. This would allow me to edit the record. i would then like to update to row with the new data. Below is the code, Any help would be appreciated.

Private Sub SubmitButton1_Click()

Dim i As Integer
i = 1
While ThisWorkbook.Worksheets("Disciplinary Report Log").Range("A" & i).Value <> ""
i = i + 1
Wend
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("A" & i).Value = ReportNumber.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("B" & i).Value = InmateName.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("C" & i).Value = InmateNumber.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("D" & i).Value = Offense.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("E" & i).Value = ClassofOffense.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("F" & i).Value = DateofOffense.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("G" & i).Value = DateofFinalImposition.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("H" & i).Value = InmatePlea.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("I" & i).Value = Investigator.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("J" & i).Value = Coordinator.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("K" & i).Value = HearingOfficer.Value
ThisWorkbook.Worksheets("Disciplinary Report Log").Range("L" & i).Value = Sanction.Value

datavalidation

End Sub

Sub UserForm_Initialize()


Offense.List = Array("ALTERATION OF A SPECIMEN", "ARSON", "ASSAULT", "ASSAULT ON DOC EMPLOYEE", "BARTERING", "BRIBERY", "CAUSING A DISBURENCE", "CONTRABAND CLASS A", _
"CONTABAND, CLASS B", "CREATING A DISTURBANCE", "DESTRUCTION OF PROPERTY", "DISOBEYING A DIRECT ORDER", "ESCAPE", "ESCAPE FROM PCS SUPERVISION", "FALSELY REPORTING AND INCIDENT", _
"FELONIOUS MISCONDUCT", "FIGHTING", "GAMBILING", "GIVING FALSE INFORMATION", "FLAGRANT DISOBEDIENCE", "HOSTAGE HOLDING", "HOSTAGE HOLDONG OF DOC EMPLOYEE", "IMPEDING ORDER", _
"INSULTING LANGUAGE", "INTERFERING WITH SAFETY AND SECURITY", "INTOXICATION", "LINGERING", "MISDEMEANOR MISCONDUCT", "OUT OF PLACE", "POSSESION OF SEXUALLY EXPLICIT MATERIAL", "PUBLIC INDECENCY", _
"REFUSAL OR REMOVAL OF AN INSTITUTIONAL PROGRAM OR POLICY", "REFUSAL TO GIVE A SPECIMEN", "REFUSING HOUSING", "RIOT", "SECRETING IDENTITY", _
"SECURITY RISK GROUP AFFILITATION", "SECURITY TAMPERING", "SELF MUTILATION", "SEXUAL MISCONDUCT", "THEFT, CLASS A", "THEFT, CLASS B", "THREATS", "VIOLATION OF PROGRAM PROVISIONS")

ClassofOffense.List = Array("A", "B", "C")
InmatePlea.List = Array("GUILTY", "NOT GUILTY")


End Sub

Private Sub datavalidation()
' If ReportNumber.Value = "" Then
' MsgBox "Sorry, Report Number can not be blank."
'Exit Sub
'End If

If InmateName.Value = "" Then
MsgBox "Sorry, Inmate Name cannot be blank,"
Exit Sub
End If

If InmateNumber.Value = "" Then
MsgBox "Sorry, Inmate Number cannot be blank,"
Exit Sub
End If

If Offense = "" Then
MsgBox "Please select Offense,"
Exit Sub
End If

If ClassofOffense.Value = "" Then
MsgBox "Sorry, Class of Offense cannot be blank,"
Exit Sub
End If

If DateofOffense.Value = "" Then
MsgBox "Sorry, Dateof Offense cannot be blank,"
Exit Sub
End If

'If DateofFinalImposition.Value = "" Then
' MsgBox "Sorry, Date of Final Imposition cannot be blank,"
'Exit Sub
'End If

'If InmatePlea.Value = "" Then
' MsgBox "Sorry, Inmate Plea cannot be blank,"
'Exit Sub
'End If

If Investigator = "" Then
MsgBox "Sorry, Investigator cannot be blank,"
Exit Sub
End If

'If Coordinator = "" Then
' MsgBox "Sorry, Coordinator cannot be blank,"
'Exit Sub
'End If

'If HearingOfficer = "" Then
' MsgBox "Sorry, Hearing Officer cannot be blank,"
'Exit Sub
'End If

'If Sanction = "" Then
' MsgBox "Sorry, Sanction cannot be blank,"
'Exit Sub
'End If

Exit Sub
End Sub
Private Sub ExitButton_Click()

Unload Me

End Sub
Private Sub ClearButton_Click()


'ReportNumber.Value = ""
InmateName.Value = ""
InmateNumber.Value = ""
Offense.Value = ""
ClassofOffense.Value = ""
DateofOffense.Value = ""
DateofFinalImposition.Value = ""
InmatePlea.Value = ""
Investigator.Value = ""
Coordinator.Value = ""
HearingOfficer.Value = ""
Sanction.Value = ""


End Sub

 

[dmt32]

Hi
welcome to forum

Your request is possible but would be helpful if you can place a copy (with dummy data only) of your workbook on a file sharing site like dropbox & provide a link to it?
If can do this, I will see if can find some time to have a play to include your request.

Dave

 

[MacM109r]

 

[dmt32]

Hi,
To share a file with the forum, you need to place a copy of your workbook (with sensitive data removed) on a file sharing site like dropbox & provide a link to it.

Dave

 

[MacM109r]

Hi,
To share a file with the forum, you need to place a copy of your workbook (with sensitive data removed) on a file sharing site like dropbox & provide a link to it.

Dave

ok, that is non-sensitive date.

 

[dmt32]

ok, that is non-sensitive date.

What you have posted is an image - what I have been asking for you to do is to place a copy of your workbook on a file sharing site like dropbox . This will allow myself and others here to ensure any solution created will work as required & saves a considerable amount of time posting questions.

Dave

 

[MacM109r]

What you have posted is an image - what I have been asking for you to do is to place a copy of your workbook on a file sharing site like dropbox . This will allow myself and others here to ensure any solution created will work as required & saves a considerable amount of time posting questions.

Dave

I understand. I will use "Dropbox" and create a link. I was just stating that data in the image is non-sensitive data and that's what the file in the link will look like.

 

[dmt32]

I understand. I will use "Dropbox" and create a link.

Okay, thanks

Dave