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.

 

[James006]

Hello,

Why don't you post your ' Submit ' code ...

 

[Mooncake]

Sub Submit_Button_Click()
'
'When the Submit button is clicked
'


Dim TargetRow As Integer


TargetRow = Sheets("Engine").Range("B3").Value


'Day_Of_Month_Box


Sheets("Data").Range("Data_Start").Offset(TargetRow, 1).Value = Assigned_To_Box


Sheets("Data").Range("Data_Start").Offset(TargetRow, 2).Value = O_Box
Sheets("Data").Range("Data_Start").Offset(TargetRow, 3).Value = Y_Box


Sheets("Data").Range("Data_Start").Offset(TargetRow, 4).Value = Assigned_By_Box


Sheets("Data").Range("Data_Start").Offset(TargetRow, 0).Value = Day_Of_Month_Box


Unload ATC_Tracker


End Sub

 

[Mooncake]

As you can see, I've got a base, but I really don't know what to do with the 'Day_Of_Month_Box (The one where I need to ID 1 of 5 sheets)

 

[James006]

May be ...

Sub Submit_Button_Click()
'When the Submit button is clicked
Dim TargetRow As Integer
Dim TargetSheet As Integer
TargetRow = Sheets("Engine").Range("B3").Value


'Day_Of_Month_Box
TargetSheet = Day_Of_Month_Box
' If your Sheets are Sheet1,Sheet2, etc ...
' And Day_Of_Month_Box equal to 1,2, etc...


  With Sheets("Sheet" & TargetSheet).Range("Data_Start")
      .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

Do not know if you need a Loop or not ...

 

[Mooncake]

Sub Submit_Button_Click()
'
'When the Submit button is clicked
'


Dim TargetRow As Integer
Dim TargetSheet As Integer
TargetRow = Sheets("Engine").Range("B3").Value


'Day_Of_Month_Box
TargetSheet = Day_Of_Month_Box
' If your Sheets are Sheet1,Sheet2, etc ...
' And Day_Of_Month_Box equal to 1,2, etc...


With Sheets("Sheet" & TargetSheet).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

The test sheet I was using is called 'Data'. Should I copy and paste that for all five sheets?
Sorry for the rookie questions. I was hoping I'd be a bit smarter with this

 

[Fluff]

How about using a combobox for the sheet names like

Private Sub UserForm_Initialize()
   Dim Ary As Variant
   
   Ary = Array("Orders", "Data", "Master")
   Me.ComboBox1.List = Ary
End Sub

You can then use that like

Sub Submit_Button_Click_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")
   .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

 

[Mooncake]

I like it. I've added the code, but when I submit, nothing happens. Data does not post, and the UserForm does not close upon submission.
I've added all days of the month (in case the boss wants more)

Private Sub UserForm_Initialize()
Dim Ary As Variant
Ary = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31")
Me.ComboBox1.List = Ary
End Sub


Private Sub Close_Button_Click()
Unload ATC_Tracker
End Sub



Sub Submit_Button_Click_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")
.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

 

[My Aswer Is This]

Because I do not like writing all that code to make a array.
I would do it like this:

Private Sub CommandButton4_Click()
'Modified  4/19/2019  4:38:33 PM  EDT
Dim i As Long
For i = 1 To 31
    ComboBox1.AddItem i
Next
End Sub

 

[Fluff]

There's an extra_Click at the end of the name that needs to be removed.
It should be

Sub Submit_Button_Click()