Append data in worksheets

Hi to all,

I am hoping that you can give me a sample code to append data on Excel Worksheets, I have attached my project as a sample, as u can see the code I have here is only to view the Datas on Multiple worksheets but no code for appending the entries.

Please Login or Register  to view this content.

Can you guys help me to complete this project?

Thanks a lot guys.

Patience! It's the weekend and there tend to be more people online during the week.

hi,

Your code (seems to?) shows that you understand how to pull data from the spreadsheet... to put data back into the spreadsheet is effectively the opposite, for example

Please Login or Register  to view this content.

hth
Rob

Hi again,

Yap you are right, the code works.

I added the code as shown below:

Please Login or Register  to view this content.

But once I click one of the item in the listbox, I get this error:

Run-time error '6':

Overflow

I attached the amended file just in case you want to see the exact changes I made.

Thank you very much again.

Edit: The error occurs once I finished entering the datas then click Change Button. After that Select again for another data from listbox then boom.

hi,

Which line (if any) highlights in yellow when the error occurs?

I haven't been able to duplicate your error in Excel 2007 but then I haven't tried very hard either!

I suggest that:
- In each Sub where you use a "set xyz = ..." statement to define an object/range then you also state "set xyz = Nothing" at the end of the Sub (unless it is a global variable - I don't think you have any of these though?).

- The code is currently set up to search for the Employee number ("sFind = Me.ListBox1.Text") & if none have been selected it will populate the first row of the spreadsheet (because it finds ""). Can you figure out a way to overcome this?

hth
Rob

hi Bong,

I'm assuming that you are happy with the Tel ph number issue for the moment, is that okay?
(perhaps we can look at it again later - I still haven't been able to duplicate the issue)
Does it make any difference if you change the code from:

Please Login or Register  to view this content.

Here's some code which should hopefully overcome the Search issue:

Please Login or Register  to view this content.

If you agree with the logic I've used in the comments of the above code, you'll need to make similar changes to the other sections of your code which use the same principles. Ideally, where sections of code are duplicated you'd move them into a separate macro & call them from the first macro with parameters if necessary. We may be able to do this for you once you have your code working.
If you don't agree with my logic in the above code & can't fix it yourself - let us know...

hth
Rob

Hi Rob,

Seems the suggested code works fine. The changes are as follows:

Please Login or Register  to view this content.

As you can see I have added a "Format" for Date Entries.

Thanks you very much for your help.

PS: I cannot say at the moment that this project is final, can be able to tell you if everything goes well by tomorrow.

hi Bong,

Another vbe function that may help when dealing with dates is "Cdate".

Ummm...
I think (without checking) that the way you have used the If statement means that the "lrow" defined within the Else clause, will not be used after the End If (b/c you then move onto the next sheet) therefore it may as well be deleted. Is this right?
From memory, I think that this also means that you can't add new records to the file using the Change button. How will you add new records/employees to the file?

Another tip for code tidyness (speed?) is to add another With statement eg:

Please Login or Register  to view this content.

hth
Rob

Hi Rob,

Thanks again for the Tip.

Regarding Date, Is this code OK?

Please Login or Register  to view this content.

And with regards of adding a record, I am thinking to add additional button to insert for a new Serial Number and Employee Number, but I am still trying to figure out how is this possible, may you can give me a hint.

Thanks and regards,

Bong

Edit: ----------------------------------------------------

I noticed a problem with the below code:

Please Login or Register  to view this content.

Hope you can figure out what just happened.

Again best regards,

Bong

hi Bong,

Try searching for tips on the Dates section (if you're not reassured by your testing) - I'll look tomorrow.

Good catch!
sORRY, I changed the rng to use "cells(8,2)" to stop the search finding blank rows at the top of the page but overlooked this impact. To ensure the correct figures are displayed when using "cells(8,2)", you need to change...

Please Login or Register  to view this content.

This will then mean that the cells which are referenced within the "With Rng" statement are correctly picked up.

goodluck
Rob

Hi Rob,

My apology for taking so long. I was concentrating on the form to add new entry and this is what I have done.

Please Login or Register  to view this content.

The above code works fine, but I think it a messy setup coz I was just playing around to find out how things can be done. Hope you can show me how to make it tidy.

You may have noticed the brown colored text, as we have discussed before, the " +7 " thing does not help to show the proper display of the data.

I hope I am not being retarded.

Thanks again, hope to hear from you soon.

Best Regards,


bong

Edit: BTW my main key here is Employee Number so I have to make a new UserForm to call in so that I can add new record/data.

hi Bong,

It's good to hear you are learning through playing around & trying things out

I probably won't be on the computer for a few days so hopefully someone else can help you...
To make it easier for us, can you please upload the latest version of your file?

Rob

Hi Rob,

Yes of course I attached here my latest project.

Hope to hear from you sooner.

Best Regards,

Bong

Hi Rob,

I have been searching for Search/Find code to incorporate in my file, and I found this:

Please Login or Register  to view this content.

How can I use this for my file?

Please help.

Thanks and best regard,

Bong

EDIT: Is there a code to redirect the find result to ListBox as a selected value?

hi Bong,

I've had a go at tidying up your code - see what you think of the attached file...
I've included some commented Assumptions within the code.

I'm not sure exactly what you mean in your last post, can you please have another go at explaining your question?

hth
Rob

Hi Rob,

My findings with the new file are as follows:

1. During Editing Mode, If a user mistakenly select a textbox (for any Date), could not switch to any other textbox unless the value required is entered.

Please Login or Register  to view this content.

2. Once the user is done with editing and fires up the Update, it calls up the "UformUpdateErrorMsg" for all the sheets. (With my little knowledge I could not figure out how).

Edit: uppsssss, I got it: sFind = Me.LBoxOfEmpNums.Text <-- should be sFind = Me.LBoxOfEmpNums.Value (your testing me right?)

3. The On Error Resume next <--- I did not bother to check out this, it was on the original code.

4. Adding new employee number - You are correct, so that’s fine.

5. I am trying to include the search/find function, say for example that the datas are huge and the need to search is required. Below is what I am trying to do. (again it’s not done correctly / not working at all).

Please Login or Register  to view this content.

Note: I have to show you the code in the old format.

The search/find is added in the main form at the top right side with “Find button”, Label and TextBox for entering what to find.

Say user entered “passport number” and press find, the code should look for the entered value then if found the corresponding Employee Number in the listbox should be selected automatically and of course displays the information on the useform.

I know you are tired, coz the changes you made are massively huge, and could not find the way how to thank you enough.


Thanks and best regards,

Bong

EDIT2: Testing the new code I found another one (which I think another test for me):

Please Login or Register  to view this content.

Where it should

Please Login or Register  to view this content.

Hi Bong,
I wasn't testing you - I was just dopy!

1. I think your approach will work but to minimise code duplication I've moved the check into the function changing it from "IsDateEntryValid" to "IsDateEntryEmptyOrValid".
2. Nope, I wasn't testing you - but I'm pleased you found it
You may know all this but in case you don't...
To help find what is happening when the code runs, you can type "stop" on a line by itself at the start of a function or sub. When the code stops on this line & highlights the line in the VBE you can press [ctrl + L] in the VBE to show the "call stack" & by clicking on the items you can go back to the location in the previous sub/function. Once you are looking at the preceding code you may be able to identify what has caused the error by holding the mouse over the variables (or creating a "Watch expression"). To subsequently continue the code, you can press [F8] to proceed one line at a time from the highlighted "stop", or [F5] to let the code run automatically.
3. I've removed the "on error resume next" & replaced it with a logic check.
4.
5. I've had a go at recreating a search function & including it in the code of the attached v3 file. Also, I have attached another file which contains a Search function that I've recently made for a work mate which may give you some further ideas.

Edit2. Ooopps! Good spotting
btw, I'm currently reading "Excel Programming with Visual Basics for Applications in 21 Days" by Matthew Harris & although it is "old" (written for Excel '95 users!)* it has some good concepts (well, I think they are ). One of these is the deliberate assignation of Null to indicate invalid data which can then be tested using "IsNull(...)" (p354-355). So I thought I'd give it a go in your code
* So old in fact, I was able to get it for free from a secondhand bookshop (www.skoob.com)

hth
Rob

Hi Rob,

Thanks again.

About the testing thing, I was enjoying it and I thought you are testing me.

To help find what is happening when the code runs ......

I honestly don't know that and hey thanks for the tip.

Thanks again for the search code, I will study it how this codes works.

I am excited to see this project goes.

cya later.

Thanks and Best Regards,

Bong

EDIT: WOW man, the search was great, and it even made the listbox automatically select the right row, this is what I was breaking my head with hummer just to figure out how. You are great man

Thanks a lot.

hi Bong

Thanks for the feedback - I'm pleased I could help

I haven't tested the code completely but I think we must be close to marking this thread as solved...
If you are happy with the solution, can you please mark the thread as solved & feel free to add to my rep (thanks for the previous rep )?
I think any small changes you need to make now can be the subject of a new thread (with a link to this thread for background info).

re "edit: Search selecting item in list"
Yes, it just comes from knowing how to word your search questions when Googling & the try & try again approach!
I found it using something like "select highlight listbox item in vba excel sub" as my search terms.

Goodluck
Rob

Dear Rob,

Yes, after doing some testing, I can tell that thread is done.

Thanks you very much indeed, much appreciated.

BTW I added another point to you.

Best Regards,

Bong