User form Text Box Entires

[raccoon588]

I have a user form with check boxes and they each have a title.
Plant Manager
Foreman
Supervisor
etc.

I would like for a user to be able to select everyone that was there. when the user hits the submit button i would like the code to be able to look in column A for the first cell that has todays date and then in Column Y add the title one after the other (one title per cell going down) so Y1 would have Plant Manager Y2 would have Foreman Y3 would have Supervisor. The next day when the form is used it will look in column A again for the right date and then go to Column Y and start dumping titles all over again based on the users selections. Everyday there are 24 rows, one row for ever hour.

 

[Roderick_E]

So the user could select only one Title? or any number of multiples? If so, you would first count how many titles the user has selected then loop through that many times. On the next day, you will have the code determine the last populated row and start inputting the data on the next row.
Something that isn't clear in your question is the 24 rows. Do you already have 24 rows in column A for each potential date??? I need more information to assist you. Here's what I have so far:

Sub buttonpush()
Dim cnt As Long
Dim rsheet As String
rsheet = "[COLOR=#ff0000]Sheet1[/COLOR]" 'change name between quotes to exact name of sheet to affect.
lastrow = Sheets(rsheet).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row  'determine last used row on a sheet
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
If ctrl.Value = 1 Then
'[COLOR=#ff0000]need more information to complete this part[/COLOR]
End If
End If
Next
End Sub

 

[raccoon588]

So the user could select only one Title? or any number of multiples? If so, you would first count how many titles the user has selected then loop through that many times. On the next day, you will have the code determine the last populated row and start inputting the data on the next row.
Something that isn't clear in your question is the 24 rows. Do you already have 24 rows in column A for each potential date??? I need more information to assist you. Here's what I have so far:

Sub buttonpush()
Dim cnt As Long
Dim rsheet As String
rsheet = "[COLOR=#ff0000]Sheet1[/COLOR]" 'change name between quotes to exact name of sheet to affect.
lastrow = Sheets(rsheet).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row  'determine last used row on a sheet
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
If ctrl.Value = 1 Then
'[COLOR=#ff0000]need more information to complete this part[/COLOR]
End If
End If
Next
End Sub

My sheet is a full year of dates. Oct1 to sept29 every day consists of 24 rows. So it is broken into hours. So row 1 is oct 1 hour 0-1 row 2 is oct 1 hour 1-2. Row 25 is oct 2 hours 0-1 etc. I need to be able to make a selection on the user forum and have those titles populate the y column cells that correlate to a cells within the current 24hour period. There are only about 9 titles to choose from so simply looking back to the last empty cell will not do. Also, yes, I need all the selected option to fill in the y column when the submit button is clicked. Thanks for all your help so far.

 

[Roderick_E]

Thanks for the clarification. Now I need a little bit more. Using your example of Oct 1. Hours 0-24. So when the user presses the button, will all the titles concatenate in the current date and hour? So in column Y for Oct 1, hour 0-1 say Plant Mgr, Foreman??? I'm still confused how the hours are going to be determined.

 

[raccoon588]

Thanks for the clarification. Now I need a little bit more. Using your example of Oct 1. Hours 0-24. So when the user presses the button, will all the titles concatenate in the current date and hour? So in column Y for Oct 1, hour 0-1 say Plant Mgr, Foreman??? I'm still confused how the hours are going to be determined.

Once we find the day I I need one title
To do into each cell in the y column. So if it was oct 1 plant manager would go into y1 foreman into y2 supervisor into y3. That an example. What ever checkboxes are marked on the user form that title
Would be placed into the cells. Then oct 2 y 25 would have foreman y26 would have supervisor etc.

 

[Roderick_E]

Ok I get that so the hours are pointless then?

 

[raccoon588]

Yea. They are meaningless for this task

 

[Roderick_E]

Ok this will look in column A for the current day's date, the first row it finds it on and then populate column Y with the selected checkboxes; row by row.
This would require column A to have at least one row with ONLY a date, no time.
This also assumes your userform is named userform1 and all the checkboxes on your userform are only related to the titles. And that the titles are the caption of the checkbox

Sub buttonpush()
Dim mrow As Long
Dim rsheet As String
rsheet = "Sheet1" 'change name between quotes to exact name of sheet to affect.
'check if today's date matched
If IsError(Application.Match(CLng(Date), Sheets(rsheet).Range("A:A"), 0)) = False Then
mrow = Application.Match(CLng(Date), Sheets(rsheet).Range("A:A"), 0)
End If
'error trapping if today's date not matched.
If mrow = 0 Then
MsgBox "No date found matching " & Date & " in column Y on sheet: " & rsheet
Exit Sub
End If
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
If ctrl.Value = 1 Then
Sheets(rsheet).Cells(mrow, "y") = ctrl.Caption
mrow = mrow + 1
End If
End If
Next
End Sub

 

[raccoon588]

So i do not get the message that there was no date found, i also get no errors. But it does not dump the info into the Y column

 

[Roderick_E]

Hmm... added new line for testing:
You should get the first row number where today's date happens on your sheet. Let me know what happens and if accurate. We can check some other things, like the name of your userform and checkboxes and such.

Sub buttonpush()
Dim mrow As Long
Dim rsheet As String
rsheet = "Sheet1" 'change name between quotes to exact name of sheet to affect.
'check if today's date matched
If IsError(Application.Match(CLng(Date), Sheets(rsheet).Range("A:A"), 0)) = False Then
mrow = Application.Match(CLng(Date), Sheets(rsheet).Range("A:A"), 0)
End If
'error trapping if today's date not matched.
If mrow = 0 Then
MsgBox "No date found matching " & Date & " in column Y on sheet: " & rsheet
Exit Sub
End If

[COLOR=#ff0000]msgbox mrow '***FOR TESTING TO SEE IF DATE MATCHED.  remove later***[/COLOR]


For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
If ctrl.Value = 1 Then
Sheets(rsheet).Cells(mrow, "y") = ctrl.Caption
mrow = mrow + 1
End If
End If
Next
End Sub