UserForm/If Help

[Frank Rudd via OfficeKB.com]

I have tried many ways to figure this out, with no success. I have a monthly
data sheet with data that needs to be entered for 6 days a week. This is
currently done by arrowing across the cells, entering the data by date, day,
and name and I'd like to make this much easier by using a UserForm. The
spreadsheet I'm using now is set up for 5-weeks so I can just enter a date in
the Monday field of the first week and the date populates from there. The
name fields all link back to an employee list of 55 possible people.

There are 6 pieces of data needed and they need to be separated by date and
name. The date and name fields are in ComboBoxes with drop down lists. I can
put the data in the sheet and have it populate blank lines, and I had thought
about setting up a vlookup in the daily sheets by just setting up tables and
populating those from the UserForm. The problem is the number of employees in
the sheet can vary daily from 40+, so I can't tell for certain where the data
will end each day and I haven't found a way to fill the table each day to 55
lines. My latest attempt uses If statements in VBA that would go to a sub
named by day (I've called them Daily1, Daily2, etc) based on the value in the
date ComboBox.

I have no experience with goto's (actually only a little with VBA at all
except for Macro's). Would that be better? I've pasted part of the code below,
and I would appreciate any assistance or advice.

I'm only pasting part of this, since I think it will get the idea across
without going on forever. What I tried to do was put an If statement that
referred to the 30 tables (when created anyway).The code I've made for the
submit button is first, then the "Daily" tables. I REALLY appreciate any help
anyone can give. I've been trying for some time now.

Private Sub SubmitButton_Click()
' Make sure data sheet is active
Sheets("Daily").Activate

' Determine the next empty row
NextRow = Application.WorksheetFunction.CountA(Range("AA:AA")) + 6

' Find the Date and make sure all data is entered
If ComboBox2.Text = A4 Then Daily1
If ComboBox2.Text = A66 Then Daily2
If ComboBox2.Text = A128 Then Daily3
If ComboBox2.Text = A190 Then Daily4
If ComboBox2.Text = A252 Then Daily5

Etc, etc.

End Sub

Sub Daily1()
Cells(NextRow, 78) = ComboBox1.Text
If ComboBox1.Text = "" Then
MsgBox "You must enter a name."
ComboBox1.SetFocus
Exit Sub
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 79) = ComboBox2.Text
If ComboBox2.Text = "" Then
MsgBox "You must enter a date."
ComboBox2.SetFocus
Exit Sub
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 80) = TextBox1.Text
If TextBox1.Text = "" Then
MsgBox "You must enter Credited Hours."
TextBox1.SetFocus
Exit Sub
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 81) = TextBox2.Text
If TextBox2.Text = "" Then
MsgBox "You must enter Total Hours."
TextBox2.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 82) = TextBox3.Text
If TextBox3.Text = "" Then
MsgBox "You must enter Total Items."
TextBox3.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 83) = TextBox4.Text
If TextBox4.Text = "" Then
MsgBox "You must enter Non-Amounts."
TextBox4.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 84) = TextBox5.Text
If TextBox5.Text = "" Then
MsgBox "You must enter Internal Errors."
TextBox5.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 85) = TextBox6.Text
If TextBox6.Text = "" Then
MsgBox "You must enter External Errors."
TextBox6.SetFocus
End If
Application.Run Sub_SubmitButton
End Sub

Sub Daily2()
Cells(NextRow, 86) = ComboBox1.Text
If ComboBox1.Text = "" Then
MsgBox "You must enter a name."
ComboBox1.SetFocus
Exit Sub
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 87) = ComboBox2.Text
If ComboBox2.Text = "" Then
MsgBox "You must enter a date."
ComboBox2.SetFocus
Exit Sub
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 88) = TextBox1.Text

Etc. Etc.

End Sub

 

[Frank Rudd via OfficeKB.com]

Oh, I'm using Windows XP and Excel 2003.

 

[Guest]

Frank,
Can you send me a copy of your spreadsheet so I can better
understand it? My reading of your note suggests you are going to have data
for 30 dates across the columns starting at column 78; there aren't enough
columns for this.

Subject to my understanding your requirement, you could get away with one
"daily" routine by passing "nextrow" and "Dayno" to a routine where Dayno is
calculated by subtracting date from your Start (monday) date to give a value
of 1 to 30. "Dayno" can be adjusted to allow for 6 days per week rather than
7.



Something like:


Sub Daily(nextrow,dayno)

n=(dayno-1)*8+78

Cells(NextRow, n) = ComboBox1.Text
If ComboBox1.Text = "" Then
MsgBox "You must enter a name."
ComboBox1.SetFocus
Exit Sub
End If
Application.Run Sub_SubmitButton
Cells(NextRow, n+1) = ComboBox2.Text
If ComboBox2.Text = "" Then
MsgBox "You must enter a date."
ComboBox2.SetFocus
Exit Sub
End If
Application.Run Sub_SubmitButton
Cells(NextRow, n+2) = TextBox1.Text
If TextBox1.Text = "" Then
MsgBox "You must enter Credited Hours."
TextBox1.SetFocus

Exit Sub

End If
Application.Run Sub_SubmitButton
Cells(NextRow,n+3) = TextBox2.Text
If TextBox2.Text = "" Then
MsgBox "You must enter Total Hours."
TextBox2.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, n+4) = TextBox3.Text
If TextBox3.Text = "" Then
MsgBox "You must enter Total Items."
TextBox3.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, n+5) = TextBox4.Text
If TextBox4.Text = "" Then
MsgBox "You must enter Non-Amounts."
TextBox4.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow,n+6) = TextBox5.Text
If TextBox5.Text = "" Then
MsgBox "You must enter Internal Errors."
TextBox5.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, n+7) = TextBox6.Text
If TextBox6.Text = "" Then
MsgBox "You must enter External Errors."
TextBox6.SetFocus
End If
Application.Run Sub_SubmitButton
End Sub


My e-mail address is (e-mail address removed)

HTH

 

[Frank Rudd via OfficeKB.com]

I appreciate your help and suggestions. I've sent it to you. It's amazing to
me that people are so willing to help on here.

 

[Frank Rudd via OfficeKB.com]

I appreciate your help and suggestions. I've sent it to you. It's amazing to
me that people are so willing to help on here.

It's not done, but I know where to go from here. Thanks so very much for your
help!