Populate multiple data tables using one data entry form

I haven't used macros for a while and I am getting a bit bogged down on something simple.

I am trying to populate multiple data tables using one data entry form. For example, I have 4 stores. I need to enter daily inventory, sales, deposits, etc. Each store has its own table in a separate worksheet. I want the data entry clerk to be able to select the store # and date and then enter inventory, sales and a daily deposit and then submit. Once the data is submitted I want it to populate the appropriate table on the appropriate worksheet organized by the da开发者_StackOverflow中文版te. After the data is submitted I want the data entry box to reset to blanks so that the next entry can be made.

Any steer in the right direction would be most helpful

Thanks!

Go to VBAProject, right click -> Insert -> User Form. Create a user form - something like:

You can pre-populate Store and Inventory combo boxes to make life easier. To start a from you can use a button places on a sheet and wire its Click event. You will end up with something like:

Public frmEntry As UserForm1

Private Sub cmdAdd_Click()

    If frmEntry Is Nothing Then

        Set frmEntry = New UserForm1

        Call PopulateStores()
        Call PopulateInventory()

    End If

    frmEntry .Show Modal

End Sub

On the submit side - you need to handle Click event on the Submit button.

This link will help you create your data entry from: http://www.contextures.com/xluserform01.html

When the user is finished entering data on the form (after he clicks the "Finish" button for example), simply write the data in the controls (like a textbox) onto the appropriate worksheet and cells. For example, add this code to your userfrom:

Private Sub btnFinish_Click()
    Worksheets("Store1").Cells(2,3).Value2 = Me.TextBox1.Text
End Sub