#1
|
|||
|
|||
UserForm Search, delete issues
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. Code:
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 |
#2
|
|||
|
|||
Hi,
This bit of code will clear the userform and leave the date inplace. Code:
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Is it possible to take an input from a UserForm in one document to a UserForm in a do | BoringDavid | Word VBA | 5 | 05-09-2014 09:08 AM |
Search for date and then apply mutliple search criteria in huge dataset | maxtymo | Excel | 2 | 12-01-2013 04:52 AM |
Looking for Windows Search app with ability to search by content | gopher_everett | Office | 1 | 02-28-2013 09:23 PM |
Search for text then delete line | konopca | Word VBA | 2 | 06-07-2012 02:44 AM |
Backspace & Delete key issues | marty69 | Word | 0 | 08-22-2010 08:06 AM |