Arrggh! If help

[Frank Rudd via OfficeKB.com]

I have tried many ways to figure this out, and I'm obviously in over my head.
I have a monthly data sheet with data that needs to be entered for 6 days a
week to track employee productivity. 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 as a 5-week spreadsheet 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 people.

There are 6 pieces of data needed and they need to be separated by date and
name. I've put them in the UserForm I've made as follows: Date, Name, and 6
text boxes for the data. 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 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. I've also tried 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 would appreciate any assistance
or advice.

 

[Guest]

Your basic idea seems sound and you should be able to get it to work. There
are ways to deal with the difficulties you mention but it is hard to tell how
to approach it unless you could perhaps post the code you have developed so
far and indicate the specific places in the code where you are running into
problems.

 

[Frank Rudd via OfficeKB.com]

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.

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
If TextBox1.Text = "" Then
MsgBox "You must enter Credited Hours."
TextBox1.SetFocus
Exit Sub
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 89) = TextBox2.Text
If TextBox2.Text = "" Then
MsgBox "You must enter Total Hours."
TextBox2.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 90) = TextBox3.Text
If TextBox3.Text = "" Then
MsgBox "You must enter Total Items."
TextBox3.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 91) = TextBox4.Text
If TextBox4.Text = "" Then
MsgBox "You must enter Non-Amounts."
TextBox4.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 92) = TextBox5.Text
If TextBox5.Text = "" Then
MsgBox "You must enter Internal Errors."
TextBox5.SetFocus
End If
Application.Run Sub_SubmitButton
Cells(NextRow, 93) = TextBox6.Text
If TextBox6.Text = "" Then
MsgBox "You must enter External Errors."
TextBox6.SetFocus
End If
Application.Run Sub_SubmitButton
End Sub