VBA - UserForm Multiple Sheets/Table submit button code

[tedholly]

I am working on creating a user form that takes the data entered, and submits it on a table on a specified sheet. On the user form, how do I have the data submit to different tables on different sheets? I want to be able to click submit on the user form and specify which sheet that I want the data added to, through a searchable box (since there is over 100 projects/sheets. The data would then be added to the table on the corresponding sheet. The tables are identical and the name is the worksheet name (because the worksheet name is the project name) On the user form, I want to enter the data, and when I press submit have a searchable menu where he can specify which sheet (Which project name), we want to add the data too (which is a table on the sheet) How do I do this?

 

[Norie]

Add a combobox to the userform and have it list all the project/sheet names.

The user would select the project/sheet they want the data to go to from that.

You could use code like this to populate the combobox when the userform opens, it could be adjusted to not list specific worksheets or to only list worksheets with names that follow a pattern

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim arrProjects() As Variant
Dim cnt As Long

    ReDim arrProjects(1 To ThisWorkbook.Sheets.Count)

    For Each ws In ThisWorkbook.Sheets
        cnt = cnt + 1
        arrProjects(cnt) = ws.Name
    Next ws

    Me.cboProjects.List = arrProjects

End Sub

In the code of the Submit button the first thing you would do would be to check the user has actually picked a project.

 

[tedholly]

My apologies as I am very new to VBA. The first code goes under the combo box for the project name?

What would the code be for the submit button?

 

[Norie]

Sorry, not quite sure what happened with the 2nd piece of code - I'll try to fix it and post back later.