UserForm Search, delete issues

[johndough]

I work as an inmate job coordinator. I assign jobs to inmates. I am trying to move from the old fashion way of doing it which is one application per job. This takes a lot of time and filing space as we have 30+ jobs they can apply for, and 1500 inmates. I have redone our application to allow for 10 jobs on one app. What I am trying to accomplish with Excel is have “Database” be the Master File sort of speak. It will hold all information. I have created a sheet for each of the main jobs. I want it to work like this, when I enter data via UserForm I want it to populate “Database” and I want it to also populate on the sheet of the job as well so 2 different sheets each. Example would be,building porter it will populate on “Database”(which it does) and also on the next entry of the “BUILDING” worksheet. I have the basic functions working on my Userform some issues I need help with are:



1. The clear button also clears date. I don’t want the date to be erased I want it to always be the current date.

2. I want a search function like the Data Entry Form Excel has, the one that you can type data into any of the fields in the form to search for. I want it to populate the UserForm the same way as well. Also I want the search button to search the entire workbook

3. I have “Database” password protected, and it unprotects using my Userform. I want it to do this on all worksheets. Basically I want all sheets password protected except when using my UserForm.

4. Also I want to add a delete button that deletes the whole entry of a specific inmate. Like the Data Entry Form it populates the form and you hit clear and it erases it from the database and moves the cells up to leave no spaces.

5. I want to force CAPS on all cells on all sheets(might be easier to force on UserForm?)

Essentially I want the Excel Data Entry Form features but in my UserForm. If someone knows where to find the code for MS Excel Data Entry Form let me know.

I am limited to using Excel only(work Policy) and I cant install anything. I also have very limited internet privileges.

Look at the first 3 worksheets you will understand what I am looking for.
I know Excel offers most of these features thru macros and buttons on a ribbon, but I want it to be simple and on one form.

Password is wipp

I will donate via PayPal $10 for whoever can fix all problems.

 Private Sub adc_Change()
 End Sub
 Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Database")
 'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
 'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
  .Unprotect Password:="wipp"
  .Cells(iRow, 1).Value = inmate
  .Cells(iRow, 2).Value = adc
  .Cells(iRow, 3).Value = race
  .Cells(iRow, 4).Value = dorm
  .Cells(iRow, 5).Value = house
  .Cells(iRow, 6).Value = kitchen
  .Cells(iRow, 7).Value = job
  .Cells(iRow, 8).Value = preference
  .Cells(iRow, 9).Value = TextBox1
  .Protect Password:="wipp"
End With
 End Sub
 Private Sub CommandButton2_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
End If
Next ctl
End Sub
 
Private Sub Control1_Click()
 End Sub
  
 Private Sub CommandButton3_Click()
 Application.Dialogs(form).Show
End Sub
 Private Sub inmate_Change()
 End Sub
 Private Sub job_Change()
 End Sub
 Private Sub kitchen_Change()
 End Sub
 Private Sub Label1_Click()
 End Sub
 Private Sub Label3_Click()
 End Sub
 Private Sub Label13_Click()
 End Sub
 Private Sub Label14_Click()
 End Sub
 Private Sub Label2_Click()
 End Sub
 Private Sub Label21_Click()
 End Sub
 Private Sub Label7_Click()
 End Sub
 Private Sub Label8_Click()
 End Sub
 Private Sub TextBox7_Change()
 End Sub
 Private Sub race_Change()
 End Sub
 Private Sub TextBox1_Change()
 End Sub
 Private Sub userform_Initialize()
'initialise the form so the current date is showing Application.WorksheetFunction.Date(.Format, "dd/mmm/yyyy")
 
    TextBox1.Value = Format(Date, "dd-mmm-yy")
 
'add the rest our your userform initialisation values HERE........
End Sub
 Private Sub UserForm_Click()
 End Sub

[charlesdh]

Hi,

This bit of code will clear the userform and leave the date inplace.

Private Sub CommandButton2_Click()
With UserForm1
    .inmate.Text = ""
    .adc.Text = ""
    .race.Text = ""
    .dorm.Text = ""
    .house.Text = ""
    .kitchen.Text = ""
    .job.Text = ""
    .preference.Text = ""
End With
End Sub

Hope this helps in part of your request.

[charlesdh]

I see that you have the same question in this forum. But you have 2 different versions of the Userform. It makes it difficult to help you.
Perhaps you can tell use which forum question you would like to progress with and have the "Forum" monitor delete the other one.

the other question is at
https://www.msofficeforums.com/excel...textboxes.html

[charlesdh]

Hum,

This was asked and answered in a another forum.
It would be best if you do not cross post.
If you do let us know..
We may have been able to help others..

http://www.ozgrid.com/forum/showthread.php?t=188243