Excel Automation Solution for useform

[Balajibenz]

Hi Team

Can you please help me with below requirement.

I do an exercise in my company where i fill manual warning forms for employees where i fill few details. I looking to automate this so that it makes our jobs easier.

In the manual sheet which I use to fill every employee has limit of 3 warnings. the details that needs to be filled for each employee is

1. Creator Name (Name of the person/Supervisor entering the details)
2. Warning Number
3. Week Number
4. Date
5. Pick Error
6.% Error
7. Remarks

I have below idea, please let me know if it can be materialized else kindly help me to on how to do it.

1. Have the names of employees in sheet1 of the workbook in Column A
2. If we can have a symbol as "+" in Column C against each name which can be clicked and we can have a user form to be filled with the above details. Once a user form against a name is saved then we can display number as 1 in column B against that name and if another user form is saved then number should be changed to 2.
3. These user forms should have option to save, cancel, delete and warning numbers should update accordingly.
4. We should be able able to access the userforms relating to each employee.

 

[RasGhul]

There is a built in userform function which could help you update your dataset, which doesn't require VBA.

SUPER EASY Excel Data Entry Form (NO VBA)

 

[Balajibenz]

There is a built in userform function which could help you update your dataset, which doesn't require VBA.

SUPER EASY Excel Data Entry Form (NO VBA)

Hi Mate,

Thank you, that helped. I have one more doubt, I have created userform but can you help me on how to make field in userform as dropdown with the options.

Let us say i have list in Column Z and I want that list to be populated in dropdown of particular field in the userform. If the list in Column Z changes dropdown should change accordingly.

 

[RasGhul]

Dynamic named ranges cant be applied to the no VBA solution, you will need to build a user form in the Developer tab.

If you need to create a dynamic named range

• Select an item in Column Z - then press CTRL-T to create a excel table with headers
• Highlight all rows not including the header - in the ribbon navigate to Formulas/Name Manager/New
• Create a name for your range then press ok. e.g. items
• In your user form properties in Row Source enter the name for your range e.g. items

If done correctly the Combobox or Listbox in your user form will list all rows from your named range, because its created as an excel table this will update dynamically when new rows are added.