Help Needed

K

kwavic

New Member
Joined
Sep 13, 2024
Messages
9
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
VBA Code: 
[/
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

]
 

Attachments

  • Screenshot (14).png
    Screenshot (14).png
    131.6 KB · Views: 8

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
LongToast said:
How data in column G look like?
Click to expand...
I want to put date in columns G like the first one will be like 01/01/2024,then next one 02/01/2024 for the January worksheet. I want to match/lookup the date in the textbox one to columns G and if found populate the textboxes if not found then it should add a new row.
 
Upvote 0
I don't know if this is work, but let's try it.

I assume :

1. Your data from January to December always start at G6.
2. You naming your sheets as JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER.

Replace your code in UserForm with this :

VBA Code: 
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
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)
        
        oWs.Range("G" & iLast + 1).Resize(1, 10).Value = arrDataInput
        
        MsgBox "Data Saved.", vbOKOnly + vbInformation
        LoadData
    End If
End Sub

Private Function TotalIVF() As Double
    If IsNumeric(Me.TextBox3.Value) And IsNumeric(Me.TextBox6.Value) And IsNumeric(Me.TextBox9.Value) Then _
        TotalIVF = Me.TextBox3.Value + Me.TextBox6.Value + Me.TextBox9.Value
End Function

Private Function TotalMOMO() As Double
    If IsNumeric(Me.TextBox4.Value) And IsNumeric(Me.TextBox7.Value) And IsNumeric(Me.TextBox10.Value) Then _
        TotalIVF = Me.TextBox4.Value + Me.TextBox7.Value + Me.TextBox10.Value
End Function

Private Function TotalSales() As Double
    If IsNumeric(Me.TextBox2.Value) And IsNumeric(Me.TextBox5.Value) And IsNumeric(Me.TextBox8.Value) Then _
        TotalIVF = Me.TextBox2.Value + Me.TextBox5.Value + Me.TextBox8.Value
End Function

Private Sub TextBox1_Change()
    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()
    dTotalSales = TotalSales
End Sub

Private Sub TextBox3_Change()
    dTotalIVF = TotalIVF
End Sub

Private Sub TextBox4_Change()
    dTotalMOMO = 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

Let me know if there's an error.
 
Upvote 0
LongToast said:
I don't know if this is work, but let's try it.

I assume :

1. Your data from January to December always start at G6.
2. You naming your sheets as JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER.

Replace your code in UserForm with this :

VBA Code: 
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
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)
       
        oWs.Range("G" & iLast + 1).Resize(1, 10).Value = arrDataInput
       
        MsgBox "Data Saved.", vbOKOnly + vbInformation
        LoadData
    End If
End Sub

Private Function TotalIVF() As Double
    If IsNumeric(Me.TextBox3.Value) And IsNumeric(Me.TextBox6.Value) And IsNumeric(Me.TextBox9.Value) Then _
        TotalIVF = Me.TextBox3.Value + Me.TextBox6.Value + Me.TextBox9.Value
End Function

Private Function TotalMOMO() As Double
    If IsNumeric(Me.TextBox4.Value) And IsNumeric(Me.TextBox7.Value) And IsNumeric(Me.TextBox10.Value) Then _
        TotalIVF = Me.TextBox4.Value + Me.TextBox7.Value + Me.TextBox10.Value
End Function

Private Function TotalSales() As Double
    If IsNumeric(Me.TextBox2.Value) And IsNumeric(Me.TextBox5.Value) And IsNumeric(Me.TextBox8.Value) Then _
        TotalIVF = Me.TextBox2.Value + Me.TextBox5.Value + Me.TextBox8.Value
End Function

Private Sub TextBox1_Change()
    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()
    dTotalSales = TotalSales
End Sub

Private Sub TextBox3_Change()
    dTotalIVF = TotalIVF
End Sub

Private Sub TextBox4_Change()
    dTotalMOMO = 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

Let me know if there's an error.
Click to expand...
Thank you,I will let you know
 
Upvote 0
kwavic said:
When I am typing the date in the text box in the userform, it gives me run time error 13 type mismatch
Click to expand...
You should type number from 1 to 31, since you provide month in combobox, and year always took "Now" year so Date's textbox only need input number from 1 to 31
 
Upvote 0
LongToast said:
You should type number from 1 to 31, since you provide month in combobox, and year always took "Now" year so Date's textbox only need input number from 1 to 31
Click to expand...
Okay, so when I start typing the amount like 50000 at the morning side under sales it gives me function call on left-hand side of assignment must return variant or object
 
Upvote 0
Replace all code with this :

VBA Code: 
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Textbox on userform not updating when navigating through records
2
Replies
10
Views
368
ipbr21054
I
S
  • Solved
date formatting within vba
Replies
3
Views
118
shina67
S
A
  • Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
432
amoverton2
A
J
  • Question
VBA Search box and button code
Replies
0
Views
85
jlcodes
J
M
  • Solved
vba to sum up each column for each employee on column (A)
Replies
6
Views
208
kvsrinivasamurthy
K

Forum statistics

Threads
1,221,418
Messages
6,159,791
Members
451,589
Latest member
Harold14

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top