Userform to next table row

AlexSrois

New Member
Joined
Aug 14, 2021
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hi Guys! Need your help again!

I need a code to make my userform write information in a table on another worksheet. Right now, my code is making it write the information on the next avalable row, which is usualy inside the table, but for some reason sometime the date gets entered several row after the table, which causes a lot of problem.

Here is the code I'm using so far ;

VBA Code:
Private Sub CommandButton1_Click()

If Trim(TextBox3.Value) = "" Or Trim(TextBox5.Value) = "" Or Trim(TextBox6.Value) = "" Then
MsgBox "Informations manquantes!" & vbCrLf & " " & " " & vbCrLf & "Les champs en jaune sont obligatoires", vbCritical, "Erreur"
Cancel = True
Exit Sub

Else

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Panier")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
With ws
.Cells(iRow, 1).Value = "En attente"
.Cells(iRow, 2).Value = "Non assigné"
  .Cells(iRow, 3).Value = Me.TextBox1.Value
  .Cells(iRow, 4).Value = Me.TextBox6.Value
  .Cells(iRow, 5).Value = Me.TextBox2.Value
  .Cells(iRow, 6).Value = Me.TextBox3.Value
  .Cells(iRow, 7).Value = Me.TextBox4.Value
  .Cells(iRow, 8).Value = Me.TextBox5.Value
  .Cells(iRow, 9).Value = Me.TextBox8.Value
  .Cells(iRow, 10).Value = Me.TextBox7.Value
  .Cells(iRow, 11).Value = Format(Now, "dd-mm-yyyy")

End With

TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
Unload Me
Save

ActiveWorkbook.Worksheets("Panier").ListObjects("Panier").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Panier").ListObjects("Panier").Sort.SortFields.Add _
Key:=Range("Panier[[#All],[Requis Le]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Panier").ListObjects("Panier").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

MsgBox "Demande envoyée!" & vbCrLf & " " & " " & vbCrLf & "Fermeture de l'application"

For Each w In Application.Workbooks
 w.Save
Next w
Application.Quit

End If
End Sub

To be honest, I do not understand the code I'm using very well. I copied it from another worksheet that was doing something similar but I have no idea how to fix my problem. I know the code should refer to the table at some point, but unsure on how to achieve this. The ''dim'' and irow things confuses me a lot.

Thank you!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
I think I might have found a solution ; Could you let me know what you think about this?

Excel Formula:
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Panier")
Dim tbl As ListObject
Set tbl = ws.ListObjects("Panier")
Dim newrow As ListRow
Set newrow = tbl.ListRows.Add
With newrow

.Range(1) = "En attente"
.Range(2) = "Non assigné"
.Range(3) = Me.TextBox1.Value
.Range(4) = Me.TextBox6.Value
.Range(5) = Me.TextBox2.Value
.Range(6) = Me.TextBox3.Value
.Range(7) = Me.TextBox4.Value
.Range(8) = Me.TextBox5.Value
 .Range(9) = Me.TextBox8.Value
 .Range(10) = Me.TextBox7.Value
.Range(11) = Format(Now, "dd-mm-yyyy")

End With

Is it gonna be stable enough?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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