PDA

View Full Version : Creating a form on an excel worksheet and posting it to a data sheet in the same wkbk



mcrackin
06-20-2013, 08:22 PM
Hi everyone.

Really new here, I've only done a few macro's on a few projects before.

I'm trying to figure out how to make a form in excel where a bunch of data is input by a user until they click a submit button and all that data is posted into another sheet in the same workbook. They should be able to keep doing this to fill up records in the data sheet.

I thought this would be simple and there would be tutorials everywhere, but I can't find a single tutorial that will guide me through this after scouring google.

Any help would be appreciated. Any links to a good tutorial would be great.

Thanks

SamT
06-21-2013, 08:37 AM
Sounds like a good use for a UserForm. "MyUserForm.Show" it from either the Workbook Open Event sub or place a Command button on the sheet that "Shows" it.

In Excel, Open VBA, (Press Alt+F11), make sure the Project Explorer Pane is shown, (Press Ctrl+R,) Right Click on the " VBAProject(Your WorkBook Name)" and select Insert >> UserForm.

Use TexBoxes for Data Entry points and Labels to Identify them.
Press F4 to view the Properties of the Controls. Set the Labels "Caption"s to the Data Field name you want. Name the TextBoxes with Data Field Descriptive Names.

Add CommandButtons such as, (Caption and Name Pro[erties,) "Clear Fields," Submit," and "Done."

DoubleClicking a control on the Form will automatically insert the start of the Control's "On Click" procedure into the Form's Code Page. I would not use any Control Procedures except the Command Buttons'.

If you use very similar names for the Data Sheet's Column Labels, the Columns Defined Names, and The TextBox Names, the Coding for the Form is very simple.
For the First Name Data Field:
Column Label: First Name
Column Defined Name: First_Name
TextBox Control Name: tbxFirstNameThe Submit Button's code might look like:
With Sheets("DataSht")
nr = LastRow + 1 'There are lots of LastRow Examples in VBAExpress
.Range("First_Name").Cells(nr) = txbFirstName
.Range("Mid_Init").Cells(nr) = txbMidInit
.Range("Last_Name").Cells(nr) = txbLastName
End With Note how I only had to type the first assignment line, then copied it over and over, then just changed two words in each line.