UserForm Entry: Item # (1-5) = Sheet # (1-5)

[Mooncake]

I've created a box on my data entry UserForm for entering 1 of 5 numbers
Once the "Submit" button is clicked, how can I make that number entered direct the data entries to the corresponding sheet?

Example: UserForm. 2 is entered. Data is entered. Submit.
The data is now listed on Sheet 2

I'm sure it's simple, but I'll be darned If I can figure it out.

 

[My Aswer Is This]

Just in case you want to know.
When creating an array if your just using numbers you do not need the quotation marks.

You can write it like this:

Ary = Array(1, 2, 3, 4, 5)

 

[Mooncake]

Simple mistake. Fixed that, but now I'm getting this error. I feel like I'm SO close to getting this working! Fingers crossed...

Sub Submit_Button_Click()
'
'When the Submit button is clicked
'
Dim TargetRow As Integer
TargetRow = Sheets("Engine").Range("B3").Value
With Sheets(Me.ComboBox1.Value).Range("Data_Start") <-------------------Run-time error '1004': Application-defined or object-defined error
.Offset(TargetRow, 1).Value = Assigned_To_Box
.Offset(TargetRow, 2).Value = O_Box
.Offset(TargetRow, 3).Value = Y_Box
.Offset(TargetRow, 4).Value = Assigned_By_Box
.Offset(TargetRow, 0).Value = Day_Of_Month_Box
End With

Unload ATC_Tracker
End Sub

 

[Fluff]

@MAIT
Normally you would be correct, but as these are sheet names, they need to be strings. Otherwise the code would be looking at the sheet index.

 

[Fluff]

Do you have a named range on that sheet called Data_Start?

 

[Mooncake]

To clarify: Do I need to create, potentially, 31 Data_Start (one for each sheet)?

 

[Mooncake]

"Data_Start" is an anchor cell on the page from which offset starts counting. It's not a sheet

 

[Fluff]

To clarify: Do I need to create, potentially, 31 Data_Start (one for each sheet)?

Yes you do, unless there is another way to determine where you want the data placed.

 

[My Aswer Is This]

Somehow in your own mine your going to decide where this Data_Start should be.

Just tell us the logic you use to determine this and we should be able to write a script to use that same logic.

 

[Mooncake]

Upon selecting the sheet, the first entry starts at B5, the next entry goes in B6, and so on.
All sheets are the same, so the starting cell is B5.

With Sheets(Me.ComboBox1.Value).Range("Data_Start")

"Data_Start" works for one sheet only. I'm more than happy to change "Data_Start" to something else as long as I can get this monkey of a project off my back.
I really appreciate the assist!

 

[My Aswer Is This]

So are you saying on all sheets Data_Start is B5

Then why not use this:

With Sheets(Me.ComboBox1.Value).Range("B5")
​