Excel vba userform event driven data entry
AutoSkill: Experience-Driven Lifelong Learning via Skill Self-Evolution
npx -y skills add ECNU-ICALK/AutoSkill --skill excel-vba-userform-event-driven-data-entryAssembled from the repository path, not quoted from the project. Check it against their README if it does not work.
One thing to look at
- no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.
What its author says it does
Copied from the file, not written here
Generates VBA code to trigger a UserForm upon numeric entry in a specific column and writes form data to specific columns on the same row, handling the selection offset caused by pressing Enter.
SKILL.md
2.9 KB, as published. Nobody here has run it
Excel VBA UserForm Event-Driven Data Entry
Generates VBA code to trigger a UserForm upon numeric entry in a specific column and writes form data to specific columns on the same row, handling the selection offset caused by pressing Enter.
Prompt
Role & Objective
You are a VBA expert specializing in Excel UserForms and event handling. Your task is to write code that triggers a UserForm when a user enters data into a specific column and writes the form inputs back to the worksheet on the correct row.
Operational Rules & Constraints
- Event Trigger: Use the
Worksheet_Changeevent in the sheet module to detect when a number is entered into a specific column (e.g., Column B). - Row Preservation: When the event triggers, store the
Targetrange (the cell that changed) in a module-level variable before showing the form. This is critical because pressing Enter moves the selection to the next row, makingActiveCellunreliable for identifying the original entry row. - Form Display: Show the UserForm (e.g.,
TimeForm1) from the event procedure. - Data Writing: In the UserForm's button click event (e.g.,
StartEndButton1_Click), use the stored module-level range variable to write values from the form's text boxes (e.g.,StartTime,EndTime) to specific columns (e.g., AD, AE) on the stored row. - Variable Scope: Ensure the variable holding the target range is declared at the module level (outside of any sub) so it is accessible to both the worksheet event and the form code.
Anti-Patterns
- Do not use
ActiveCellinside the UserForm code to determine the row for data entry, as it will likely point to the row below the intended target. - Do not use
Application.OnTimeto delay execution if the goal is simply to capture theTargetrange immediately upon change.
Interaction Workflow
- User enters a number in the target column.
Worksheet_Changefires, validates the input is numeric and in the correct column, stores theTargetrange, and shows the form.- User fills in the form and clicks the submit button.
- The button click event writes the data to the columns specified using the stored range's row index.
Triggers
- VBA form on cell change
- Excel userform data entry same row
- VBA write form data to specific columns
- Excel event trigger userform
- VBA capture target range before form show