Userform Search, Find and Edit Data

Hi to all, if I have this wrong then, I apologies as this is my first attempt at this. I have looked all around and cant find the result I am after.



I have the following:
- Many sheets of data which all have at least 30 column headers but they all have two columns headers the same “Number” and “Location”.
- Each sheet represents a different geographical area “location”.
- Each sheet has dates, text and number cells.
I am trying to do the following:
- Find a user Id.
- Put all the data from the row of the user id in to the form.
- Be able to edit the form and save it.
- Enter new data into the relevant sheet from a blank form if there is no user with the user id details.
I hope this makes sense, the code is bellow.

I must thank John55 for pointing me in the right direction for the code I have used so far.

Please Login or Register  to view this content.

Many thanks to all

Hi Chris270

If you'd like, attach a sanitized version of your data file. It'll be more productive for us (and you) if we can see what you have and what you'd like it to be. In the meantime, I'm attaching a file that demonstrates the ability to add or modify records in a database.

John, having issues with size of file. i hope to have this sorted soon

Hi Chris

Try zipping the file then attach.

Hi Chris...I'll take a look at it...no doubt will have questions.

Hi Chris
What version of Excel are you running this code with...Excel 2007 or later?
Your profile indicates Excel 2003.

What does this statement infer

I have 36 data sheets but the code that I am using stops and then it says it is too long

Do you have other code that's not included in the sample file? If so, I'd like to see it.

Another question...what's your "Main Menu" look like. For example, a project I developed for myself, the "Main Menu" looks like this Main Menu.jpg I'm not sure why your "Main Menu" is not included in the sample file but it could give me a clue to the overall approach you're taking.

For the moment, I'm stepping through your code so I can understand what it's doing...making some progress.

Hi Chris

Wow...the interface is substantially different from your originally posted file. Updating the record for changes is not a major issue (assuming you can define which fields you'll allow the user to change). I've worked a bit on your sample file to do just that. Now that the interface has changed, I'll need to revisit that.

Regarding adding NEW users...what information should be included...ALL fields on these sheets

Please Login or Register  to view this content.

or only basic specific fields...if so, which ones?

Regarding this

i use both 2003 and 2007

The reason I asked the question is because your code will error in Excel 2000. It contains sort routine syntax that exists in Excel 2007 but NOT in Excel 2000. I don't have Excel 2003 so can't test there. If you've tested in Excel 2003...so be it...any work I do will be in Excel 2007 or I'll need to rewrite the sort routines.

Let's look at "C Trade" worksheet. Your original file has Data Validation in Columns H through AC that allows only dates between 1/1/1980 and 1/1/3000. Your new file doesn't have Data Validation. Your original file seems to contain only dates in these fields...your new file has strings.

Can I take data from your original file and copy it to your new file for test data? Are the fields the same in both files? Can (will you) provide data that simulates your "Real Data"?

Hi Chris

The attached accommodates the following

I would like a UserForm that I could update from the UserForm to the relevant sheet. Must be multi sheet.

I NEED TO ADD A SAVE BUTTON

I ALSO NEED IT TO CLEAR ALL THE TEXT BOXES LESS WORK ID NUMBER BEFORE THE SEARCH IS STARTED

Add new users

The Add New Users routine needs further definition as I don't know what this means

this must add the user to the correct sheet depending on the “ location” details. Each new user needs to be added to the bottom of a minimum of 6 sheets depending on the qualification.

In the code for the "SAVE" button in UserForm 2 and UserForm 3, I've only coded for sh1. You can see what I did and write the code for the other sheets.

Let me know of issues.

Hi Chris
As I said yesterday

The Add New Users routine needs further definition as I don't know what this means
Quote:
this must add the user to the correct sheet depending on the “ location” details. Each new user needs to be added to the bottom of a minimum of 6 sheets depending on the qualification.

Pretend that I'm totally ignorant of what that means ('cause I am) and spell it out for me. If TextBox5 value is "A", what sheets are affected; if "B" what sheets are affected; if "C" what sheets are affected, etc.

I suspect a "Select Case" function is in order. I could study your workbook for an hour or more and MAYBE figure it out...but you KNOW...so enlighten me.

Hi Chris

You don't need "ReplaceC" code...go back to your original code and, if I understand your scheme, try adding this code to the original code

Please Login or Register  to view this content.

I'm still missing something...you say

Each new user needs to be added to the bottom of a minimum of 6 sheets depending on the qualification.

but I count only 5 sheets...what's the 6th or more sheets?

One further point...you can't MOVE an ID from one location to another with existing code. Do you need this capability?

Hi Chris

Alright...we're not on the same page.

What UserForm we talking about here

Please Login or Register  to view this content.

You're NOT being a PAIN. The PAIN is that I'm not seeing what you're seeing and I don't understand your process as you understand it.

If what we're doing is helping you through the process, I'd suggest you upload your current file (with current code) so I'm working with the same file and code you're working with. I've observed you've made significant changes to both the file and code during our conversation...which is fine...the issue is...unless you and I are dealing with the same "Stuff" it simply won't work.

I don't understand this statement

Sh 4/5 have been removed (to many characters)

WHAT ARE YOU TELLING ME...PLEASE EXPLAIN FURTHER. Sh 4/5 been removed from WHERE and why...from the code...from the workbook? Are you getting an error message? What's the message? What Line of Code? I don't ask questions to just ask questions...if you don't understand my questions please just say so...otherwise, please answer them best you can. If I don't understand your answer I'll let you know and rephrase the question. When I ask a question it's 'cause I don't understand something...unless I understand I can only guess or I can't help.

Sorry for the misunderstanding, I have not made myself clear!!!! Because I am sitting here working on this all night I thought I was explaining myself. But after going back I realise I am not (I don’t even understand myself ) so I will explain the issues I have not solved.

i have uploaded the file so far:
I have moved the code into 3 areas, userform 2 (individual search and edit), userform 3 (add a worker) and userform 4 (company qualification search)

Userform 2 (search button), Private Sub CommandButton1_Click() – error massage “procedure to long” this should search all the sheets (33 – 62) for the worker ID and automatically populate the userform

Userform 3 (save button), Private Sub CommandButton4_Click() - when adding worker it will only add the worker in all L sheets but no others (if i added "C or D or M or N" to the location in the user form then it will do nothing) but L works.

I shall leave it there for now and work on thosefor now and hopefully I will not confuse your good self or me. Again thanks so much, for putting up with me

Hi Chris

How many Employees we talking about? I'm thinking an Employee Master File. The Search Button would find the Employee Number in the Master File AND the Location Code. Using this approach you'd not have to search through 30 worksheets. Is my thinking correct?

In looking at your most recent file it appears that if an employee appears in one "C" sheet, the employee appears in all the "C" sheets...same appears to be true for all the other letters. Is this in fact the case? I'm looking for an approach to build the Master File with VBA.

Hi Chris...question is...how to build a worksheet that includes ALL employees. If I look at ALL Trade worksheets and extract the employees from those worksheets will I have a complete list of all employees?

Hi Chris

I believe the attached is getting closer. You've a duplicate employee number in the sample data...123789 PARRY M
123789 POPTTER J...I didn't do anything with it...I assumed it's simply an error in your sample data.

There's code included to create an Employee Master worksheet. It's picking up all employees in the various TRADE worksheets. In doing a SEARCH (from the Search Button) the code looks at this EmpMaster to find the Employee Number AND the Location. Doing so allows us to search ONLY the files for the designated location.

The EmpMaster will be UPDATED when a new employee is ADDED to the data file. The EmpMaster will be RECREATED when an employee is REVISED (just in case the location has been changed).

I've done nothing with UserForm1 and UserForm4...only Userform2 and UserForm3.

Let me know of issues.

Hi Chris

Give me the worker number you entered...I'll try to duplicate the error. May be a bit before I get back to you...have dinner guests.

Hi Chris

Never mind...I'm able to duplicate the issue. I'll post a fix.

Hi Chris

A rather simple fix...see attached.

John,
You are outstanding, thanks so much for your help. I am going through all at the moment and cant find any more code issues as yet.

The only couple of questions I do have;
-Why does the date change from dd.mm.yy to mm.dd.yy ?
- I need to add a delete record button, what code do I need? I have tried a few after moding the save data but it will not work.

Hi Chris

Regarding this

Why does the date change from dd.mm.yy to mm.dd.yy ?

I'd need to see when/where this is happening. Since you already know, please share that with me so I don't have to search for it (WHEN and WHERE please).

Now...I suspected you'd what delete capabilities. I'll look at that and get back to you. However...I'd like you to provide the CURRENT version of your FILE AND CODE, including the code you've written for the delete button. I want to make certain I'm working with the most current version of the data file and the code.

Hi Chris

I'm about to get dinner guests and will probably not look at this too much tonight. Settle your head down so it doesn't "explode". I believe...and I hope you believe...we've got it surrounded. There's going to be "things" to tweek...I believe they're going to be easily resolved at this point.

I'll look at this at the earliest opportunity

Using the last sent file from you: if i put a date in "D Trade" it will show me on the UserForm in the US format (mm.dd.yy) although it is entered in UK format (dd.mm.yy) on the sheet.

Hi Chris

In D Trade the date cells are formated like this D Trade.jpg

In C Trade the date cells are formated like this C Trade.jpg

Hi Chris

I think I understand this issue now

if i put a date in "D Trade" it will show me on the userform in the US format (mm.dd.yy)

In the CommandButton1_Click event code use something like this

Please Login or Register  to view this content.

That should resolve this issue.

Hi Chris
It works for me. I'd add an "Unload Me" at the end to close the UserForm but that would be my personal preference.

Hi Chris

The only code I've found that works with Conditional Formatting is found at this Link http://www.vbaexpress.com/kb/getarticle.php?kb_id=190 and it does work.

The function would be implemented like this

Please Login or Register  to view this content.

It's a LOT of coding as it would need to be applied to each field that contains Conditional Formatting. I don't know of any way to avoid that. Let me know what you think.

John,

I keep getting errors, i have tried many things but with no luck. could you have a look

Please Login or Register  to view this content.

NOTE: the end of the code has been removed due to having to many characters (but there has been no change (less format dd-mmm-yyyy) to the missing bit)

Error "Sub or Function not defined" and ConditionalColor was highlighted yellow

Hi Chris

What error message are you getting? Post a .jpg of the message. What line is highlighted?

Never mind...I missed the last line of your post.

Did you download the function from the link I provided you? If not, put this code in a standard module

Please Login or Register  to view this content.

I've got the code in a separate module but that's not necessary. Let me know how I can help.

Hi Chris
I'll look at it this afternoon.

Hi Chris

Well...I did look at it "this afternoon". Took me two days to "look at it". The link previously provided didn't provide consistent results so I went on a web search and found this link http://www.cpearson.com/excel/CFColors.htm. I've found Mr Pearson's code to be VERY reliable but I couldn't get it to work...that's what's taken so long...adapting his code to your situation.

To make his code work I had to change your Conditional Formatting for Rule 2 (I reversed the logic...look at the old and the new (in this attachment)...you'll see what I mean). I also had to modify his code significantly...look at the code in the link compared to the code in the attached...follow the logic.

I've tested this extensively and have corrected all the errors I've discovered...I'm certain you'll find more.

You'll need to check your formatting in the date cells. For example, in Sheet "J Inst", employee number 123 will fail because his/her dates are not formatted properly. However, on the same sheet, employee number 963852 succeeds because the date formatting is correct. So, when you run into errors like "Type Mismatch" in Module 3 first thing I'd check is the formatting of the date cells. Working with dates, to me, is VERY touchy.

A further piece of advice, if you can avoid it, DON"T ADD ANY FURTHER CONDITIONAL FORMATTING TO THIS WORKBOOK...in my experience (which is limited), it's very difficult for VBA to work with in terms of UserForms.

The code changes are all contained in Module 3 and UserForm2 Search Button, sht2 code.

Let me know of issues.

Edit...I'm color blind so...you're colors are probably not in the same ballpark as to what you desire.

Hi Chris

Been wondering about this project. Glad it's working for you. It's been an interesting project. If all is working as desired I'll ask you to mark your Thread as "Solved".

You may have missed the window of opportunity to do so and I can't do it for you (must be a Moderator or Administrator to do so).

You can try this:

To mark your thread solved do the following:
- Go to your first post on the thread
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solved
- Click Save

If this doesn't work please PM any Moderator and ask them to do so for you (include a link to your Thread).