Excel VBA & Userform: How to insert data on a specific row which value is based on the value of a textbox in userform

[antocri84]

Dear All,
i need a problem with an excel file where I use an userform with a date in a textbox which is always the date of today.
I would like to insert data from the userform only in the row which value in column C (a date) is correspondant to the date of the textbox in the Userform. I want that the user everyday with the userform can insert data only in the row correspondant to the date of today.
Here is my code:

Private Sub OKButton_Click()
Dim emptyRow As Long
ActiveSheet.Activate
    
    If NSTextBox.Value = "" Or _
        TRXTextBox.Value = "" Or _
        SalesTextBox.Value = "" Or _
        TRTextBox.Value = "" Or _
        LGTextBox.Value = "" Or _
        PEEKTextBox.Value = "" Or _
        SLGTextBox.Value = "" Or _
        ACCTextBox.Value = "" Or _
        ShoesTextBox.Value = "" Or _
        RTWTextBox.Value = "" Or _
        FURTextBox.Value = "" Or _
        MANTextBox.Value = "" Or _
        HTTextBox.Value = "" Then
    
        
        Call MsgBox(Prompt:="All fields are mandatory!", _
                       Buttons:=vbCritical, _
                       Title:="Not filled!")
                       Else
                       Call inserimento
                       MsgBox "Form filled succesfully!", vbQuestion
                      
    End If
    End Sub


 Sub inserimento()


 ActiveSheet.Activate


 Dim emptyRow As Long


emptyRow = WorksheetFunction.CountA(Range("D:D")) + 2


Cells(emptyRow, 4).Value = NSTextBox.Value
Cells(emptyRow, 5).Value = TRXTextBox.Value
Cells(emptyRow, 6).Value = SalesTextBox.Value
Cells(emptyRow, 8).Value = TRTextBox.Value
Cells(emptyRow, 9).Value = LGTextBox.Value
Cells(emptyRow, 10).Value = PEEKTextBox.Value
Cells(emptyRow, 11).Value = SLGTextBox.Value
Cells(emptyRow, 12).Value = ACCTextBox.Value
Cells(emptyRow, 13).Value = ShoesTextBox.Value
Cells(emptyRow, 14).Value = RTWTextBox.Value
Cells(emptyRow, 15).Value = FURTextBox.Value
Cells(emptyRow, 16).Value = MANTextBox.Value
Cells(emptyRow, 17).Value = HTTextBox.Value


End Sub

i don't know the code to add.
just to explain:
1. The textbox with date is DateTextBox and the data to insert start in the excel fil is from the column D at row 4
2. I would like to insert data in the specific row in the sheet (column c which is a date starting from row 4) which value is equal to the DataTextBox

Really appreciate your help

Best

 

[Steve_]

Dear All,
i need a problem with an excel file where I use an userform with a date in a textbox which is always the date of today.
I would like to insert data from the userform only in the row which value in column C (a date) is correspondant to the date of the textbox in the Userform. I want that the user everyday with the userform can insert data only in the row correspondant to the date of today.

i don't know the code to add.
just to explain:
1. The textbox with date is DateTextBox and the data to insert start in the excel fil is from the column D at row 4
2. I would like to insert data in the specific row in the sheet (column c which is a date starting from row 4) which value is equal to the DataTextBox

Really appreciate your help

Best

I am not convinced that this is exactly what you are looking for but I am certain it is a good start. Let me know where we need to go from here.

Private Sub OkButton_Click()
'Your existing code...
'
'
'
'
' Now add this code beaneath your code...


    For i = 4 To Sheets("Sheet1").UsedRange.Rows.Count
        If Sheets("Sheet1").Cells(i, 3) = Me.DateTextBox.Text Then
            Exit For
        End If
    Next i
    Sheets("Sheet1").Cells(i, 4) = Me.DateTextBox.Text
End Sub

 

[antocri84]

I am not convinced that this is exactly what you are looking for but I am certain it is a good start. Let me know where we need to go from here.

Private Sub OkButton_Click()
'Your existing code...
'
'
'
'
' Now add this code beaneath your code...


    For i = 4 To Sheets("Sheet1").UsedRange.Rows.Count
        If Sheets("Sheet1").Cells(i, 3) = Me.DateTextBox.Text Then
            Exit For
        End If
    Next i
    Sheets("Sheet1").Cells(i, 4) = Me.DateTextBox.Text
End Sub

Thank you very much Steve
It doens't work. Says that the varibale i has not been defined.
But I need to add this code before the Sub inserimento?
Thanks

 

[Steve_]

Thank you very much Steve
It doens't work. Says that the varibale i has not been defined.
But I need to add this code before the Sub inserimento?
Thanks

My code would go after your validations of the text boxes.

But before your insert.

the value of i after the for will be your row number for your data.

 

[antocri84]

My code would go after your validations of the text boxes.

But before your insert.

the value of i after the for will be your row number for your data.

Thank you Steve but it doesn't work. Please Helppppp

Private Sub OKButton_Click()


Dim emptyRow As Long




ActiveSheet.Activate
    
    If NSTextBox.Value = "" Or _
        TRXTextBox.Value = "" Or _
        SalesTextBox.Value = "" Or _
        TRTextBox.Value = "" Or _
        LGTextBox.Value = "" Or _
        PEEKTextBox.Value = "" Or _
        SLGTextBox.Value = "" Or _
        ACCTextBox.Value = "" Or _
        ShoesTextBox.Value = "" Or _
        RTWTextBox.Value = "" Or _
        FURTextBox.Value = "" Or _
        MANTextBox.Value = "" Or _
        HTTextBox.Value = "" Then
        'DataTextBox.Value = Cells(3) Then
        
        Call MsgBox(Prompt:="All fields are mandatory!", _
                       Buttons:=vbCritical, _
                       Title:="Not filled!")
                       Else
                       Call inserimento
                       MsgBox "Form filled succesfully!", vbQuestion
    
    End If
    
[B]    For i = 4 To Sheets("MANUAL DATA").UsedRange.Rows.Count[/B]
[B]        If Sheets("MANUAL DATA").Cells(i, 3) = Me.DataTextBox.Text Then[/B]
[B]            Exit For[/B]
[B]        End If[/B]
[B]    Next i[/B]
[B]    Sheets("MANUAL DATA").Cells(i, 4) = Me.DataTextBox.Text[/B]
    
    End Sub


 Sub inserimento()
 
 Dim emptyRow As Long
 
   
emptyRow = WorksheetFunction.CountA(Range("D:D")) + 2


Cells(emptyRow, 4).Value = NSTextBox.Value
Cells(emptyRow, 5).Value = TRXTextBox.Value
Cells(emptyRow, 6).Value = SalesTextBox.Value
Cells(emptyRow, 8).Value = TRTextBox.Value
Cells(emptyRow, 9).Value = LGTextBox.Value
Cells(emptyRow, 10).Value = PEEKTextBox.Value
Cells(emptyRow, 11).Value = SLGTextBox.Value
Cells(emptyRow, 12).Value = ACCTextBox.Value
Cells(emptyRow, 13).Value = ShoesTextBox.Value
Cells(emptyRow, 14).Value = RTWTextBox.Value
Cells(emptyRow, 15).Value = FURTextBox.Value
Cells(emptyRow, 16).Value = MANTextBox.Value
Cells(emptyRow, 17).Value = HTTextBox.Value


End Sub

Did i puyt your code exactly?
What I need to change?
Thank you very much

 

[Steve_]

Thank you Steve but it doesn't work. Please Helppppp


Did i puyt your code exactly?
What I need to change?
Thank you very much

Could you explain how it is not working?

 

[antocri84]

Could you explain how it is not working?

SOLVED!! Thank you Steve for your input
I just added Dim i As Long and then I cancelle my variable emptyrow

Thank you very much

best

 

[Steve_]

SOLVED!! Thank you Steve for your input
I just added Dim i As Long and then I cancelle my variable emptyrow

Thank you very much

best

Happy I could help! Feel free to throw a like on the code