Populate multiple Labels and TextBox values in a Userform based on 2 unique identifiers.

This Userform is a timesheet, consisting of two comboboxes and 25 labels with textboxes next to them. The first combobox is the user’s name, the second is the Department name, and when the department name is selected the labels in the form will populate with the appropriate Types of Work for that Department. The user will select a date, enter hours in the textbox next to each label (type of work), click Submit and the data goes into a database (another worksheet), where a new row is added for each type of work (label) and time (textbox) for that user for that date.
I’ve attached the code for this UserForm below.

Now I want to be able to populate this same UserForm with data/values that were previously entered when I select a name and a date and click on “Find”. My challenge is that I am not looking for a unique row, but for many rows that have different types of work but the same name in column A. You can see my poor attempt at trying to do this in the cmdFind sub in the code. It only finds the first label with associated data in the textbox and overwrites the first label in the form (which it shouldn’t, it has to show all labels, even if the textbox value is “”), and doesn’t find any other labels and textboxes with or without values.

Does anyone have a suggestion how I can fix this? My suspect I can probably achieve this by reversing the cmdOK3_Click() sub, but I have no idea how to do this.

Please Login or Register  to view this content.

Hi johnw993,

My suspect I can probably achieve this by reversing the cmdOK3_Click() sub, but I have no idea how to do this.

You're so very very close.

Each time you find a new match you need code like (untested):

Please Login or Register  to view this content.

If you need additional help, please upload a sample file. It is difficult to give optimal answers while taking code out of context, even though your code is well written and easy to read.

Lewis

Hi Lewis,
Thank You! Thank you!!
By replacing part of the code the with your suggestion, it works exactly as I was hoping for! Perfect Lewis!

As always, the devil is in the details, and this didn't work at first, until I found that all the date fields in the database had to be formatted in the exact same format, and Bingo!

I have attached the 'cleaned-up' version of the UserForm code.
I had to add a "Clear Form" button, which only clears the TextBoxes, because they didn't clear when I changed only the date (for the same name and department) to Find data for a different day.

I will be uploading a sample file of the Workbook as soon as I've cleared all the customer data from this file (I'm using real client data for my tests) and replaced it with some 'bogus' sample data. This may take a day. I may have to place it in a public Dropbox or Google cloud drive, for it is 5MB.

In the meantime I have one other question:
Now that the Userform can look up existing data (thanks to you), I want to add an "Update" cmdbutton to the form, that, when clicked will over-write existing data in the database when a user makes a change to any of the textboxes that are showing with the data we have found using the cmdFind sub.

Again, my suspicion is that I am looking the answer in the face, and that by copying the code from cmdOK3 sub into the cmdUpdate sub (not written yet), and modify it with a Find and Replace, or if Change() function, it should do the job?

ps: Don't be confused by the code I've added to the bottom of this code. It lets a user see how many hours they have entered while they are using the form.

Again, my sincerest appreciation for the help you've provided.

Please Login or Register  to view this content.

Your welcome. I'm glad I was able to help.

I may have to place it in a public Dropbox or Google cloud drive, for it is 5MB.

If you zip the file, ExcelForum may allow a larger file. It's a lot easier to work on a sample file, but I imagine a lot of the 5MB is probably redundant, and can be deleted from the sample file, because small files are a lot easier to work with.

Now that the Userform can look up existing data (thanks to you), I want to add an "Update" cmdbutton to the form, that, when clicked will over-write existing data in the database when a user makes a change to any of the textboxes that are showing with the data we have found using the cmdFind sub.

Again, my suspicion is that I am looking the answer in the face, and that by copying the code from cmdOK3 sub into the cmdUpdate sub (not written yet), and modify it with a Find and Replace, or if Change() function, it should do the job?

This one is best done with a little trickery.

In order to do this, you need to know the row number in the SpreadSheet that you will overwrite. You already know the row number when you put the data in the UserForm. All you need is a place to put it. There is a little known attribute of each UserForm control named the 'Tag' attribute. It is there for the programmer to use for any reason the programmer wants. It is a good spot to store the row number in the SpreadSheet. For example to store the value in the Label Tag Attribute:

Please Login or Register  to view this content.

Now when it's time to update, you can extract the row number. You also probably don't want to update a row where the value didn't change. First you need to save the current value in the spreadsheet. This can be done by using the Tag attribute for the TextBox (also included in the code above). cmdOK3_Click() code will have to be rewritten for this, and the variant array will be discarded. I'll show you how to do this in my next post.

Lewis

The following code will help you get started in updating the spreadsheet. I tested the code the best I could without the benefit of a UserForm.

Please Login or Register  to view this content.

Lewis

Thanks Lewis, for all the time you're devoting to this!
Before I test the code you just sent me:
I've noticed something strange in the cmdFind sub.
When I use the Find function in the UserForm, it indeed pulls up the data that was previously entered (that works), but it changes the sequence of the labels in the UserForm.
Starting from the top (Label 15), it replaces any labels with the labels that have a value in the adjacent Textbox (with the correct value however), thereby creating not only possible duplicate labels, but preventing the user from entering a value in a textbox they may have inadvertently left blank, because that label has 'disappeared'.
How can I add a statement in cmdFind sub that will include all labels even if textbox value is (""). Which means that in the database there wouldn't even be a record.

John

Hi John,

We're at the point now, where you're going to have to upload a sample workbook that demonstrates the problem.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Lewis

Lewis,

See attached a sample copy of the Workbook. I've left all sheets unprotected and un-formatted.

To see the entire workbook, click on the “Administration” button, and use “Admin” as the user name and the letter “a” (small caps) as the password, both without the quotation marks. Then click on the “Show All Sheets” button.
You can use the same if you're prompted for one (you shouldn't).

We’ve been only discussing UserForm1 in this thread, but as you’ll see, the same issues I’m trying to solve also apply to UserForm2, 4 and 7. And to some degree to UserForm 5 and 8.
To test the UserForms, there is some sample data for a "Cathy Smith” in the Accounting/Finance department.
Most of the Sheets in this sample workbook have comments in cell A1, which explain where the data is coming from or going to.

I am rebuilding this workbook from an earlier version I had created when I really didn't know what I was doing with VBA. So I’m still in clean-up mode and you’ll likely find/see some stuff that will raise your eyebrows. Also, I have not finished screen size-formatting for the Forms or Sheets, so it may look a little funky on your screen.

I appreciate you taking the time to take a look.
Thank you,

John

Lewis,

In case the Zip file didn't work, here is a link to my public dropbox with the same file to view/download.
https://dl.dropboxusercontent.com/u/...0405%20v1.xlsm

John

John,

The zip file worked fine. I hope have an update for you later today. If not, then Friday, because Wed and Thu are my busy days.

Lewis

Lewis,

Thank You!!! I can't believe how much work you've put into this! To say you 'went a little overboard' is an understatement. You've rebuild the entire UserForm1 code and, with the little testing I've done, it works exactly as I was hoping it would/could! Wonderful!
Once again I realize how little I actually know of VBA and am learning so much from you! Thank you!

I like that you've removed the Update function, and have the Submit (cmdOK3) function/sub do both tasks (add new or update&replace existing). Is there a reason you still left the Update button on the form with the msg: "Update CommandButton is NOT IMPLEMENTED."? I guess it is safe to assume I can remove the Update button and the message?

In reply to your comments:

Under Summary,
line item "i":
with the few tests I've done it does not save the data in the existing row, but removes that row and adds a new row at the bottom of the database. I don't think it matters, just a matter of placement because the correct(ed) data seems to be there.

Under Comments,
line item "a":
In order for the calendar function to work (even in 2003, I think), you have to have the mGlobals Module copied into your Modules, and the frmCalendar (and code) added to your Forms, plus the Option Explicit "Workbook_SheetSelectionChange" line of code added to the "ThisWorkbook" object. All of these should be in the sample workbook I'd sent you.
If you had these components installed and it was still not working, please let me know (I've gone through great lengths to find/build a calendar function that should work on all MS Excel versions, and does not rely on MS machine code like DTPicker did).

For now, I have lots to learn from what you've sent me (heretofore concepts that were foreign to me like Scripting Dictionary, Tag, Key, etc. etc.) and am going to use the code you've provided in my 'full version' workbook and test it with UserForm1, and then build UserForm2 with the same structure, and hopefully have learned/understand enough at that time to start rebuilding my other UserForms (which are a bit more complex).

Thanks Lewis! You've been a phenomenal help to me.
(Hope you don't mind if I get back in touch if/when I hit a brick wall? )

Best Regards,

John