Transfer the data from a userform to a workbook and retrieve data from workbook to userform

[sn152]

Good Evening All,
I have a workbook which has 4 userforms such as Activities, One On One, PKT, EWS. The Activities userform has few command buttons and for example when I click on "One On One" command button it will display One On One userform.
So what I need now is, when I enter the data in the "One on One" userform and click submit, it should transfer the data to another workbook (One On One Feedback Form) and save the workbook. Also there are 2 fields in the userform (Associate ID and Associate Name). When the user enters the associate ID and click tab, it should automatically pull the associate's name and TL's(Team Leader) name in the respective fields in the user form. There is an other button in the userform called "retrieve". When I enter the associate ID and click on retrieve it should pull the details pertaining to that ID from the excel workbook to the userform.
I have attached the userform workbook and the One On One Feedback form workbook also.
Kindly help me with this. Thanks in advance...

 

[Deepak]

Hi sn152,

Check with attached file as a sample for userform "WES" where it will pull data from sheet 1,2 & put details on sheet1.

Later you can export sheet1 to another workbook by a little command.

Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:="D:\abc.xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWindow.Close

Hope! this will help to do the same with other userform too.

Feel free to revert on the same if you face any further issue.

A lot's of thing also could be on the same like as

• ID = only numeric
• other text box value = only text
• Check duplicate entry [where its required]

 

[sn152]

Hi Deepak,
Thank you so much for working on this.
My Emp ID has got 6 digits. When I enter 6 digits it shows an error. Also the emp id, emp name and manager name is on the sheet3. And when I enter all the details and click on submit button, the data should be saved to a seperate workbook without opening that workbook. Not sure whether this can be done or not.
Kindly help me on this...
Thank you....

 

[Deepak]

Hi Deepak,
Thank you so much for working on this.
My Emp ID has got 6 digits. When I enter 6 digits it shows an error. Also the emp id, emp name and manager name is on the sheet3. And when I enter all the details and click on submit button, the data should be saved to a seperate workbook without opening that workbook. Not sure whether this can be done or not.
Kindly help me on this...
Thank you....

Change id As Integer to id As Long

You may store the data on a hidden sheet & later export as said earlier as this would more reliable than a separate workbook.
However if you still think so then it's also possible.

 

[sn152]

Hi Deepak,

I have changed the id as Long and it works now. Thank you so much. Now I have a doubt. If I am entering an emp id and filling all the fields in the form and then I click submit. So it will be transferred to another workbook. Now again I am entering the same ID and filling the fields in the form and then click on submit. So when I want to retrieve the data pertaining to that ID, i enter the ID and click on retrieve. Now it will pull only the first record of that ID even if there are multiple records. Is there a way where I can provide the ID and the "month" in the form and then click on retrieve, so that it will pull only that month's record. Kindly help me on this. And also on the data to be saved on a seperate workbook.

Thank you so much...

 

[Deepak]

I will loop & update tomorrow.

 

[Deepak]

@ sn152,

Pls check with attached file.

Meanwhile i still didn't get why u want separate workbook !!! [Pls explain]

 

[sn152]

Hi Deepak,

This is working fine. Why I wanted this to be transferred to a seperate workbook is because, this macro file will be used by people in different locations. I have a seperate workbook in which the data that they fill in the form should be transferred. So from different locations I can get the data in one single workbook.
Once again thank you so much for helping me

 

[Deepak]

Dear sn152,

I didn't want to put you/your team in a trouble as separate workbook idea is not good so far.
U can add a button on user form to export the file.
Check with attached.

 

[sn152]

Hi Deepak,

Thank you so much for helping me.
I request you to do another help. I have attached a workbook. It has the login userform. When i enter the username, password and role and click submit it takes me to next form.


1) Instead of having a login button on the sheet, can we have to userform to appear automatically when we open the workbook?


2) In the userforms "Early Warning System", when navigating from one field to another field, when we press tab button in the keyboard it should move to next field in the userform.


3) In the userforms "Early Warning System", when we click on the retrieve button without entering the employee id, it is showing a run time error. Can this be corrected?


4) In the userforms "Early Warning System", after entering the emp id and when we click on retrieve it pulls the data of that particular user. But the retrieved data is not getting cleared from the userform when I delete the employee id in the userform.


Request you to kindly help me on this.
Once again thank you so much..

 

[sn152]

Also I tried to add the list box in the EWS userform in this workbook. But it is showing an error.

 

[Deepak]

1. Loop @ http://chandoo.org/forum/threads/user-access.16146/#post-98301
2. For the same you have to arrange the textbox in a up heal order.
   
3. Chek upload file.
4. Hope! You Haven't checked with last attached file , however check with attached.

 

[Deepak]

Also I tried to add the list box in the EWS userform in this workbook. But it is showing an error.

What error!! Share the same.

 

[Lymm]

To have a userform auto open you can use something like this code. I think there are other ways of doing it also.

Option Explicit
Sub MYEXCELSHOW()
UserForm1.Show (vbModeless) 'optional or modal
End Sub
Sub auto_open()
Call MYEXCELSHOW
End Sub

 

[Deepak]

Dear sn152,

As you asked earlier "When we press tab button in the keyboard it should move to next field in the userform."

Change all field TabIndex in a sequence.

 

[sn152]

Good Morning Deepak


Thank you for helping me with this. I have only one more problem in this. When I enter and employee ID and click retrieve, it pulls the data. But when I edit the retrieved data and click on save it is adding as a new line item. Can we do something to fix this? Please help me with this.


And also after retrieving the data, if I press backspace in the emp id field, it deletes the data in the form. But when I click delete button it is not deleting the data in the other textboxes. Can we do something for this as well?


Kindly help me with this. Thank you so much...

 

[Deepak]

Hello sn152,

If no value will be selected from list box then new entry will be added else selected record will be replaced with new one.

Check with attached for both the issue.

 

[sn152]

Hi Deepak,

I retrieved a data and tried editing it. After editing when I clicked submit it shows an error. Pls see the screenshot. Pls help me with this.

Thank you...

 

[Deepak]

Add

dim r as range

 

[sn152]

Thank you so much Deepak. U r awesome.... Thanks again for helping me....