Help Needed

[kwavic]

I have created a userform to submit data to the respective cells based on vba code I found on the forum that will help me and i modifies it. But it was not working as intended.
I want to match a date in textbox1 from the userform to column G, if the date is not found, add a new row and populate data into the respective columns based the values from the textboxes at different times.
Attached image. Thank you

[/
Private Sub CommandButton1_Click()
    Dim targetsheet As String
    targetsheet = ComboBox1.Value
    
    ' Check if targetsheet is empty, exit the sub if true
    If targetsheet = "" Then
        Exit Sub
    End If

    Dim ws As Worksheet
    Set ws = Worksheets(targetsheet)
    
    Dim Lookup As String
    Dim lastRow As Long
    Dim SalesClm As Integer
    Dim IvfClm As Integer
    Dim MomoClm As Integer

    ' Disable error handling temporarily
    On Error Resume Next
    Lookup = Me.TextBox1.Value
    
    ' Use Match function to find the lookup value in column G
    lastRow = Application.Match(Lookup, ws.Range("G5:G"), 0)
    
    SalesClm = 2
    IvfClm = 3
    MomoClm = 4

    ' Check if the lookup value was found
    If Not IsError(lastRow) Then
        ' Insert a new row below the last used row in column G
        ws.Cells(ws.Rows.Count, "G").End(xlUp).Offset(1, 0).EntireRow.Insert
        lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row

        ' Populate data into respective columns based on TextBox values
        If Me.TextBox2 <> "" Then ws.Cells(lastRow, SalesClm) = Me.TextBox2.Value
        If Me.TextBox3 <> "" Then ws.Cells(lastRow, IvfClm) = Me.TextBox3.Value
        If Me.TextBox4 <> "" Then ws.Cells(lastRow, MomoClm) = Me.TextBox4.Value

        If Me.TextBox5 <> "" Then ws.Cells(lastRow, SalesClm + 3) = Me.TextBox5.Value
        If Me.TextBox6 <> "" Then ws.Cells(lastRow, IvfClm + 3) = Me.TextBox6.Value
        If Me.TextBox7 <> "" Then ws.Cells(lastRow, MomoClm + 3) = Me.TextBox7.Value

        If Me.TextBox8 <> "" Then ws.Cells(lastRow, SalesClm + 6) = Me.TextBox8.Value
        If Me.TextBox9 <> "" Then ws.Cells(lastRow, IvfClm + 6) = Me.TextBox9.Value
        If Me.TextBox10 <> "" Then ws.Cells(lastRow, MomoClm + 6) = Me.TextBox10.Value
        
        MsgBox "Data was added successfully"
        
        ' Clear TextBox values after data insertion
        With Me
            .TextBox2.Value = ""
            .TextBox3.Value = ""
            .TextBox4.Value = ""
            .TextBox5.Value = ""
            .TextBox6.Value = ""
            .TextBox7.Value = ""
            .TextBox8.Value = ""
            .TextBox9.Value = ""
            .TextBox10.Value = ""
        End With
    End If
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Dim sh As Worksheet
    ComboBox1.Clear
    
    ' Fill ComboBox1 with worksheet names from the workbook
    For Each sh In ThisWorkbook.Worksheets
        ComboBox1.AddItem sh.Name
    Next sh
End Sub

]

 

[kwavic]

Replace all code with this :

Option Explicit

Dim oWs As Worksheet
Dim iLast As Long
Dim arrData As Variant
Dim bFound As Boolean
Dim dtDate As Date
Dim dTotalSales As Double
Dim dTotalIVF As Double
Dim dTotalMOMO As Double

Private Sub ComboBox1_Change()
    Set oWs = ThisWorkbook.Worksheets(Me.ComboBox1.Value)
    LoadData
   
    If Me.TextBox1.Value <> vbNullString And Me.ComboBox1.ListIndex <> -1 Then _
        dtDate = Format(CDate(Me.TextBox1.Value & " " & Me.ComboBox1.Value & " " & Year(Now)), "dd/mm/yyyy")
End Sub

Private Sub LoadData()
    iLast = oWs.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    If iLast > 5 Then
        arrData = oWs.Range("G6:S" & iLast).Value
    End If
End Sub

Private Sub CommandButton1_Click() 'THIS IS SAVE BUTTON
    If Not bFound Then
        Dim arrDataInput As Variant
       
        arrDataInput = Array(dtDate, Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, _
                Me.TextBox5.Value, Me.TextBox6.Value, Me.TextBox7.Value, Me.TextBox8.Value, _
                Me.TextBox9.Value, Me.TextBox10.Value, dTotalIVF, dTotalMOMO, dTotalSales)
       
        oWs.Range("G" & iLast + 1).Resize(1, 13).Value = arrDataInput
       
        MsgBox "Data was added successfully", vbOKOnly + vbInformation
        LoadData
    End If
End Sub

Private Sub TotalIVF()
    If IsNumeric(Me.TextBox3.Value) And IsNumeric(Me.TextBox6.Value) And IsNumeric(Me.TextBox9.Value) Then _
        dTotalIVF = CDbl(Me.TextBox3.Value) + CDbl(Me.TextBox6.Value) + CDbl(Me.TextBox9.Value)
End Sub

Private Sub TotalMOMO()
    If IsNumeric(Me.TextBox4.Value) And IsNumeric(Me.TextBox7.Value) And IsNumeric(Me.TextBox10.Value) Then _
        dTotalMOMO = CDbl(Me.TextBox4.Value) + CDbl(Me.TextBox7.Value) + CDbl(Me.TextBox10.Value)
End Sub

Private Sub TotalSales()
    If IsNumeric(Me.TextBox2.Value) And IsNumeric(Me.TextBox5.Value) And IsNumeric(Me.TextBox8.Value) Then _
        dTotalSales = CDbl(Me.TextBox2.Value) + CDbl(Me.TextBox5.Value) + CDbl(Me.TextBox8.Value)
End Sub

Private Sub TextBox1_Change()
    If Me.TextBox1.Value <> vbNullString And Me.ComboBox1.ListIndex <> -1 Then _
        dtDate = Format(CDate(Me.TextBox1.Value & " " & Me.ComboBox1.Value & " " & Year(Now)), "dd/mm/yyyy")
   
    If Me.ComboBox1.ListIndex <> -1 And IsNumeric(Me.TextBox1.Value) And IsArray(arrData) Then
        Dim i As Long
       
        bFound = False

        For i = LBound(arrData) To UBound(arrData)
            If arrData(i, 1) = dtDate Then
                Me.TextBox2.Value = arrData(i, 2)
                Me.TextBox3.Value = arrData(i, 3)
                Me.TextBox4.Value = arrData(i, 4)
                Me.TextBox5.Value = arrData(i, 5)
                Me.TextBox6.Value = arrData(i, 6)
                Me.TextBox7.Value = arrData(i, 7)
                Me.TextBox8.Value = arrData(i, 8)
                Me.TextBox9.Value = arrData(i, 9)
                Me.TextBox10.Value = arrData(i, 10)

                bFound = True
                Exit For
            End If
        Next i
       
        If Not bFound Then
            Me.TextBox2.Value = vbNullString
            Me.TextBox3.Value = vbNullString
            Me.TextBox4.Value = vbNullString
            Me.TextBox5.Value = vbNullString
            Me.TextBox6.Value = vbNullString
            Me.TextBox7.Value = vbNullString
            Me.TextBox8.Value = vbNullString
            Me.TextBox9.Value = vbNullString
            Me.TextBox10.Value = vbNullString
        End If
    End If
End Sub

Private Sub TextBox2_Change()
    TotalSales
End Sub

Private Sub TextBox5_Change()
    TotalSales
End Sub

Private Sub TextBox8_Change()
    TotalSales
End Sub

Private Sub TextBox3_Change()
    TotalIVF
End Sub

Private Sub TextBox6_Change()
    TotalIVF
End Sub

Private Sub TextBox9_Change()
    TotalIVF
End Sub

Private Sub TextBox4_Change()
    TotalMOMO
End Sub

Private Sub TextBox7_Change()
    TotalMOMO
End Sub

Private Sub TextBox10_Change()
    TotalMOMO
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long
   
    For i = 1 To 12
        Me.ComboBox1.AddItem StrConv(MonthName(i), vbUpperCase)
    Next i
End Sub

Okay will let you know

 

[LongToast]

All my columns G for the worksheets are formatted with date so even if you submit let say 5 it will change it to some date. I try to submit 5 as the date from the userform to January worksheet, it returns nothing

Here is what i got :

 

[kwavic]

Here is what i got :

View attachment 116862

I got it, My purpose is that when the morning **** ended you submit data, same to afternoon and evening. Not to wait say the following day to enter everything. Okay wanted to send you a copy of the workbook but after downloading the XI2bb, the mini sheet copy to the clipboard, can't find it

 

[kwavic]

Here is what i got :

View attachment 116862

When I save the data it doesn't go to the formatted cells, however if you saved it at different times,it does not match it with the date in columns G to save the data on the same row. So please check it for me again. Thank you attached a copy of the submitted data.