User Form to enter mileage

Been a while since I have gotten to write some code, so I am a bit rusty. I have created a user form for the user to enter their mileage to go into an expense sheet. I have data validation in "B10:B25" along with VLookup in "C10:C25". My challenge is that I want the user form to pop up for the user to enter their mileage ONLY IF Mileage is celected in Column B, and then I want their input to be entered into the same row into the cell in column D. Note, there could be multiple "Mileage" entries in column B. I would prefer not to have a "Close Form" button, but would like the data to be inserted AND the form close upon the click of the "Enter" button on the user form.

Any help would be appreciated

Pleased ignore the rest of the sheet, as it is a complete WIP as I play with how we want to have it set up.

Attached is an example using the Change event on the worksheet. You can remove the Close button and associated code. I would have just used an InputBox for this instead of creating a new UserForm but I made changes to accommodate your design.

This is part of your original code. It does not find the first empty cell in a row, it finds the first empty cell in the entire sheet. This will always be A1 on your sheet. Not sure what you are even trying to do there, it doesn't seem necessary.

Please Login or Register  to view this content.

I also added validation to ensure that what the user enters is a number.

The strategy is for the userform to capture the mileage and make it available in a public variable. After the form is done the calling procedure retrieves the value from the public variable and writes it to the sheet.