I am a newbie and this is my first post. I have two worksheets that are filled with the use of a userform depending on the choice made from a combobox. My code is pretty messy since I'm fairly new to vba, but it works except for one thing. When the user clicks the enter button, the next empty row is filled with the date, scheduled hours, actual hours, and series completed. If the enter button is clicked again, the date isn't entered but the values for the other cells are filled with the same values. I would prefer to not allow further submission with each button click unless it's an actual new entry. Tried the "Unload Me/userform.show" method to clear the userform but it didn't work very well. I would also very much appreciate some help cleaning up my code. I'm sure there is a much less messy, more efficient way to do what my code does. My code is as follows:
Private Sub cmdEnter_Click()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Found As Range, Found2 As Range
Dim Search As String
Dim iRow As Long
Set ws1 = Worksheets("PWCDaily(1st)")
Set ws2 = Worksheets("PWCDaily(2nd)")
iRow = ws1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
iRow2 = ws1.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
iRow3 = ws2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
iRow4 = ws2.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
Search = Me.MetricsDTPick.Value
Set Found = ws1.Columns(1).Find(Search, LookIn:=xlValues, LookAt:=xlWhole)
Set Found2 = ws2.Columns(1).Find(Search, LookIn:=xlValues, LookAt:=xlWhole)
If Found Is Nothing And _
Me.shiftCombo.Value = "1st" And _
Me.pwcCombo.Value = "601" Then
Do
ws1.Activate
Cells(iRow, 1).Value = Me.MetricsDTPick.Value
Cells(iRow, 2).Value = Me.schedTxt.Value
Cells(iRow, 3).Value = Me.actualTxt.Value
Cells(iRow, 4).Value = Me.compSeriesTxt.Value
Exit Do
Loop While Found Is Nothing
ElseIf Not Found Is Nothing And _
Me.shiftCombo.Value = "1st" And _
Me.pwcCombo.Value = "601" Then
Do
ws1.Activate
Cells(iRow, 2).Value = Me.schedTxt.Value
Cells(iRow, 3).Value = Me.actualTxt.Value
Cells(iRow, 4).Value = Me.compSeriesTxt.Value
Exit Do
Loop While Not Found Is Nothing
ElseIf Found Is Nothing And _
Me.shiftCombo.Value = "1st" And _
Me.pwcCombo.Value = "721" Then
Do
ws1.Activate
Cells(iRow, 1).Value = Me.MetricsDTPick.Value
Cells(iRow2, 8).Value = Me.schedTxt.Value
Cells(iRow2, 9).Value = Me.actualTxt.Value
Cells(iRow2, 10).Value = Me.compSeriesTxt.Value
Exit Do
Loop While Found Is Nothing
ElseIf Not Found Is Nothing And _
Me.shiftCombo.Value = "1st" And _
Me.pwcCombo.Value = "721" Then
Do
ws1.Activate
Cells(iRow2, 8).Value = Me.schedTxt.Value
Cells(iRow2, 9).Value = Me.actualTxt.Value
Cells(iRow2, 10).Value = Me.compSeriesTxt.Value
Exit Do
Loop While Not Found Is Nothing
ElseIf Found2 Is Nothing And _
Me.shiftCombo.Value = "2nd" And _
Me.pwcCombo.Value = "601" Then
Do
ws2.Activate
Cells(iRow3, 1).Value = Me.MetricsDTPick
Cells(iRow3, 2).Value = Me.schedTxt.Value
Cells(iRow3, 3).Value = Me.actualTxt.Value
Cells(iRow3, 4).Value = Me.compSeriesTxt.Value
Exit Do
Loop While Found2 Is Nothing
ElseIf Not Found2 Is Nothing And _
Me.shiftCombo.Value = "2nd" And _
Me.pwcCombo.Value = "601" Then
Do
ws2.Activate
Cells(iRow3, 2).Value = Me.schedTxt.Value
Cells(iRow3, 3).Value = Me.actualTxt.Value
Cells(iRow3, 4).Value = Me.compSeriesTxt.Value
Exit Do
Loop While Not Found2 Is Nothing
ElseIf Found2 Is Nothing And _
Me.shiftCombo.Value = "2nd" And _
Me.pwcCombo.Value = "721" Then
Do
ws2.Activate
Cells(iRow3, 1).Value = Me.MetricsDTPick.Value
Cells(iRow3, 8).Value = Me.schedTxt.Value
Cells(iRow3, 9).Value = Me.actualTxt.Value
Cells(iRow3, 10).Value = Me.compSeriesTxt.Value
Exit Do
Loop While Found2 Is Nothing
ElseIf Not Found2 Is Nothing And _
Me.shiftCombo.Value = "2nd" And _
Me.pwcCombo.Value = "721" Then
Do
ws2.Activate
Cells(iRow4, 8).Value = Me.schedTxt.Value
Cells(iRow4, 9).Value = Me.actualTxt.Value
Cells(iRow4, 10).Value = Me.compSeriesTxt.Value
Exit Do
Loop While Not Found2 Is Nothing
End If
Display More
Thanks in advance...:smile: