Adding Data from an Excel Form

[NDMDRB]

Hello,



Please I need your help with my project.



I have made a system for my business and need to add some forms using different tools and entry methods



The form contains:

• Text Box
• Combo Box
• Option Buttons
• Check Box
• Commend Button

Once I open the form to add a new Item...

1- TextBox1 / "ItemNumber"​

• 
  [*=1]Make it, readonly box
  [*=1]Check the ItemsNumber {Sheet1, Column C} or / a dynamic named range "ItemNumber"
  [*=1]Get the last number and add (+1)
  [*=1]ex: the last ItemNumber is: DTR1234
  [*=1]Once open the form, show in the ItemNumber: DTR1235

2- ComboBox1 / "Category"​

• 
  [*=1]Get the values from the dynamic named range "Category"

3- OptionButtons / "Colors" / All are in one frame "Color"
​

• 
  [*=1]I have listed 10 colors as "Option Buttons"
  [*=1]I need the Commend Button to add the value that I've chosen
  [*=1]ex: If I chose "Red", Add "Red" to cell "C3"

4- CheckBox / "Occasion" / All are in one frame "Occasion
​

• 
  [*=1]I have listed 10 options as "Check Box"
  [*=1]I might choose many options for 1 Item
  [*=1]I need the Commend Button to add (let's say, number 1) to the cell opposite to the Item# and the chosen occasion
  [*=1]ex: If I tick "Wedding & Engagement" insert "1" to cell "D3" &"E3"

5- TextBox2 / "Supplier Name"​

• 
  [*=1]Make it, read only box
  [*=1]Once I add the Supplier ID to the {TextBox "SupplierID"} show the name of this Supplier ID automatically
  [*=1]Suppliers ID is in "Sheet3, Column B" / Dynamic named range "SupplierID"
  [*=1]Supplier Name is in "Sheet3, Cplumn C" / Dynamic named range "SupplierName"

6- CommendButton / "Add Item"​

• 
  [*=1]Some Text Boxes must not be empty, if these boxes are empty, popup note that this should not be empty , ex: Item Name
  [*=1]All the frames must have data, Color (one option), Occasion (at least one)...
  [*=1]Check the the Item Sheet (Sheet1) and add these data under the last available raw

​

I hope that I explained what I need well, but if you need any more details, please let me know

I really appreciate you help

​

[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Occasion[/TD]
[TD]Occasion[/TD]
[TD]Occasion[/TD]
[TD]Occasion[/TD]
[TD]Occasion[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item#[/TD]
[TD]Cat.[/TD]
[TD]Color[/TD]
[TD]Wed.[/TD]
[TD]Eng.[/TD]
[TD]Party[/TD]
[TD]Date[/TD]
[TD]Prom[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]DTR1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Leith Ross]

Hello NDMDRB,


If you have VBA code written for this project, it would help if you posted it. Not many people have the time to recreate what you have done and the results would be very prone to errors. If possible, a better option option would be to post your workbook to a file sharing site and post the link here at Mr Excel.

 

[NDMDRB]

Hello Ross,

Thank you for your reply,

As you said it's better to share my workbook to know what exactly I need, and since I don't have the full code, and don't need to share the part that I have as there might be a mistakes or not completed

Note: I don't need the whole code, just one sample for each condition will be enough (as what I mentioned on the main post)

Here is the link
https://drive.google.com/file/d/1vnD9GVvoRKfapnhzz5nfbSRKuQNZN4mS/view?usp=sharing


Many thanks in advanced

 

[Leith Ross]

Hello NDMDRB,

Thank you for making that available. It is a big help. I see you have the UserForm designed. Now you just need the code to make it work. Does this project a deadline you need to meet?

 

[NDMDRB]

Hello Ross,

And thanks again,

Actually this project is for my own business, and still have many things to do for it
This code will helps me a lot since there are many forms and will use the same code for them

But, no worries for time dear, your help is highly appreciated even at any time you finished it

 

[Leith Ross]

Hello NDMDRB,

I made some changes to the workbook. First, I added the column letter to the Tag property of each control (except the Labels). Since the row to filled is known all that is needed to transfer the data is the column on the worksheet. Dynamic ranges cannot be used with a UserForm. Dynamic ranges must be calculated by a formula. Since the UserForm is a modal dialog, the user is prevented from interacting with the application until the UserForm is dismissed. It is possible to display the UserForm as a non-modal dialog but this would allow the user to change the data at anytime.

To display the UserForm, you can now use the shortcut keys Ctlr+Shift+N. The only part of the code I have not finished is the validation. Let me know what must be filled in for the Add New Item button to transfer the data to the worksheet.

Here is the link to the updated workbook [FONT=&quot]https://www.mediafire.com/file/0xed7pth7mxpnqq/UserForm_To_Worksheet_ver_1.xlsm/file[/FONT]

 

[NDMDRB]

Hello Leith, and thank you so so much dear,

This is what exactly I'm looking for

I just need few more help please, I mentioned in Sheet4 all what I need to understand the code and add some msgs, can you please check it and update me

Many thanks in advance dear

Here is the new link
https://drive.google.com/file/d/14XqYraGOSCkehTaYDLbhUVq6Vmclf5MG/view?usp=sharing

 

[NDMDRB]

Sorry Dear,

Here is the correct link
https://drive.google.com/file/d/1DoOiNenUiw1PjPXmH6xysL5thAqFDw8H/view?usp=sharing