Create and Update file with 2 different Userforms

[parelade]

Hello everybody,
Thank you for reading my following question.

I am new to VBA and userforms. I find userforms to be very useful. However, I have some problems.

I want to create a log in excel through a userform. This userform is very simple: it has 6 textboxes (TextBox1....6), and 2 commandbuttons (Add, and Cancel).

This userform was pretty easy to create and make it functional, although I wanted the userform to create a unique ID number for easy entry yymmdd-### with "###" resetting for each day. So far I managed to get a consecutive numbers added to yymmdd.

However, My problem is the second userform which has 2 comboboxes (ComboBox1...2), with 2 commandbuttons (add and cancel).

This userform2, I want to do the following:
in combobox1 I want to select the ID number from the excel, previously created by userform1. (i did this)
in combobox2 I want to select from a small list of 5 items the status in which the ID is such as: invoiced, sent, received, deposited, voided. Upon adding I want the userform2 to populate the ID entry on the excel file with the choice I made in combobox2, and create a timestamp.

What I have got so far was to be able to choose the ID, choose the status, create timestamps for each selection, but... the status and the timestamp go to either the last entry only or to the next empty row. How do I integrate the new information in the same row with the ID that I chose in the ComboBox1?


UserForm1

Private Sub CommandButton1_Click()




Dim emptyRow As Long
'Make Sheet2 active
Sheet2.Activate


'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


        
'Transfer information




Cells(emptyRow, 1).Value = Format(Date, "yymmdd") & "-" & emptyRow - 2






Cells(emptyRow, 2).Value = TextBox1.Value
Cells(emptyRow, 3).Value = TextBox2.Value
Cells(emptyRow, 4).Value = TextBox3.Value
Cells(emptyRow, 5).Value = TextBox4.Value
Cells(emptyRow, 6).Value = TextBox5.Value
Cells(emptyRow, 7).Value = TextBox6.Value
    If TextBox6.Value <= 10 Then
        Cells(emptyRow, 8).Value = 0
    Else
        Cells(emptyRow, 8).Value = (TextBox6.Value - 10) * 0.25
    End If
Cells(emptyRow, 10).Value = Now


MsgBox "Successful!"




Me.Hide


End Sub


'All textboxes have to be filled out


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value = "" Then
    MsgBox "Please fill in Case Number"
    Cancel = True
End If
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox2.Value = "" Then
    MsgBox "Please fill in Requestor Name"
    Cancel = True
End If
End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox3.Value = "" Then
    MsgBox "Please fill in Requestor Address"
    Cancel = True
End If
End Sub
Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox4.Value = "" Then
    MsgBox "Please fill in Requestor E-mail"
    Cancel = True
End If
End Sub


Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox6.Value = "" Then
    MsgBox "Please fill in Number of Pages"
    Cancel = True
End If
End Sub






'Textboxes with numbers only


Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
        KeyAscii = 0 ' this prevents the non-numeric data from showing up in the TextBox
        MsgBox "You can only enter numbers"
    End If
    
End Sub




Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim strTemp As String
  strTemp = Me.TextBox5.Value
  If (strTemp Like "(###) ###-####") Then Exit Sub
  If (strTemp Like "##########") Then
    strTemp = "(" & Left(strTemp, 3) & ") " & Mid(strTemp, 4, 3) & "-" & Right(strTemp, 4)
    Me.TextBox5.Value = strTemp
  ElseIf (strTemp Like "###-###-####") Then
    strTemp = "(" & Left(strTemp, 3) & ") " & Right(strTemp, 8)
    Me.TextBox5.Value = strTemp
  ElseIf (strTemp Like "### ### ####") Then
    strTemp = Replace(strTemp, " ", "-")
    strTemp = "(" & Left(strTemp, 3) & ") " & Right(strTemp, 8)
    Me.TextBox5.Value = strTemp
  ElseIf (strTemp Like "(###)###-####") Then
    strTemp = Left(strTemp, 5) & " " & Right(strTemp, 8)
    Me.TextBox5.Value = strTemp
  Else
    If MsgBox("Your entry does not convert to a standard U.S. phone number format. " _
      & "Do you want to try again?", vbQuestion + vbYesNo, "Invalid Format") _
      = vbYes Then
      With Me.TextBox5
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
      End With
      Cancel = True
    Else
      Me.TextBox5.Value = strTemp
    End If
  End If
lbl_Exit:
  Exit Sub
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
        KeyAscii = 0 ' this prevents the non-numeric data from showing up in the TextBox
        MsgBox "You can only enter numbers greater than 0"
    End If
End Sub

UserForm2

Private Sub CommandButton2_Click()


Unload Me


End Sub


Private Sub CommandButton1_Click()








Dim emptyRow As Long




'Make Sheet2 active
Sheet2.Activate


Dim fnd As Range
Set fnd = Sheets("Log").Range("A:A").Find(Me.ComboBox1.Value, LookIn:=xlValues)
    If Not fnd Is Nothing Then
        Me.ComboBox2.Value = fnd.Offset(0, 2).Value
    End If






'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


    
'Transfer information




Cells(emptyRow, 9).Value = ComboBox2.Value
If Cells(emptyRow, 9).Value = "Invoice Sent" Then Cells(emptyRow, 11).Value = Now
If Cells(emptyRow, 9).Value = "Payment Received" Then Cells(emptyRow, 12).Value = Now
If Cells(emptyRow, 9).Value = "Payment Deposited" Then Cells(emptyRow, 13).Value = Now
If Cells(emptyRow, 9).Value = "Void" Then Cells(emptyRow, 14).Value = Now
If Cells(emptyRow, 9).Value = "To Be Invoiced" Then Cells(emptyRow, 15).Value = Now






    MsgBox "Successful!"
Me.Hide




End Sub














Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)


    If ComboBox1.Value = "" Then
            MsgBox "Please select an invoice"
            Cancel = True
    End If


End Sub
Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If ComboBox2.Value = "" Then
            MsgBox "Please select a payment status"
            Cancel = True
    End If


End Sub


Private Sub UserForm4_Initialize()


'Empty ComboBox1
ComboBox1.Clear
'Fill ComboBox1




'Empty ComboBox2
ComboBox2.Clear


'Fill ComboBox2
'With ComboBox2
 '   .AddItem "To Be Invoiced"
  '  .AddItem "Invoice Sent"
   ' .AddItem "Payment Received"
    '.AddItem "Payment Deposited"
    '.AddItem "Void"
'End With


End Sub










Private Sub UserForm_Click()


End Sub