Need Help in Form data submission into excel sheet

[mebro]

Hello,

please help me with my requirement, i am trying to create work log entry by using the calendar, when i double click on the date it will show up a user input form which has comboboxs(getting data from other sheet "Data" in the same workbook). when i click on submit it should save the data in "Comments" sheet.

i am attaching my excel sheet, can someone take a look and suggest me the solution and the way i am approaching to this requirement.

thanks in advance.

[BadlySpelledBuoy]

Add this to the top of Module1:

Please Login or Register  to view this content.

This to your Calendar sheet module:

Please Login or Register  to view this content.

And this to your UserForm:

Please Login or Register  to view this content.

I've done that in the attached as well as made a few other minor changes, such as making your lists for the comboboxes into separate tables and referring to those in the userform initialize code which means no ugly blank spaces at the bottom of the combobox lists.
Also made your Close and Clear buttons work.

BSB

[mebro]

Hi BSB,

Awesome, thanks alot for your quick help.

[BadlySpelledBuoy]

No problem. Happy to help

BSB

[mebro]

Hi BSB,

Sorry to bother you again for the basic question, can you please explain how you tables in Details sheet.

thanks in advance

[BadlySpelledBuoy]

They are simply single column tables, created in the same way as you made the table on the Comments sheet (Select the range then "Insert / Table").

Then I've created 4 named ranges, one looking at each of the tables. That way you can add new values to, or delete values from, these tables and it will be automatically reflected in the combobox lists.

If I've missed the point of your question, let me know.

BSB

[mebro]

Hello BSB,

thanks for your response, not sure where i am doing wrong, i just created tables in new spreadsheet(Sheet1), i used that sheet in form initialisation, but i am getting error, i am sure something is wrong with table creation in sheet1. please check and correct me where i am doing wrong.

thanks in advance.

[BadlySpelledBuoy]

You're missing a step. You need to create the named ranges from those tables. To do that follow the below:
1) Select Sheet1 A1
2) Press Ctrl + F3
3) Click 'New'
4) Enter the name for the range in the 'Name' field
5) Click in the "Refers To" field
6) Select the cells in that table but NOT the header. The Refers To field will then show something like =TableName[ColumnHeader]
7) Click OK
8) Repeat the above for the remaining tables
9) Click close
10) Try your form again

BSB

[mebro]

thanks BSB, i got it now and its working. appreciate your help.

[BadlySpelledBuoy]

Happy to help

BSB

[mebro]

Hi BSB,

need one more help, could you please suggest me what is the best approach to update or delete the data(other than updating data directly in comments spread sheet) which i have submitted through userform.

thanks in advance

[BadlySpelledBuoy]

Do you mean you wish to recall a submitted record to the userform so you can update it or delete it?

If so then what would you use as a record identifier?

BSB

[mebro]

sorry for delay

yes, is it possible to retrieve the record based on user and the date?

thanks in advance