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

antocri84

New Member
Joined
Jul 31, 2019
Messages
4
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:

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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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.


Code:
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
 
Upvote 0
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.


Code:
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
 
Upvote 0
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.
 
Upvote 0
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

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
        '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
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

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