0

I made a userform in excel to save the client data I enter from comboboxs to the last row of table while dispalying it on listbox and creating a sheet with the client name when i try to remove a client excel crashes, what i didn't get is it works sometimes and somtimes it crashes the code i use to add clients:

Sub add_client_sheet()

    On Error GoTo Catch
Try:
    Sheets(cafe.clientcombo.Value).Select 'Try to focus on the tab
        MsgBox Prompt:="client existe deja", _
    Title:="Add client"
    GoTo Finally
Catch:
    Sheets.Add(After:=Sheets(Sheets.count)).Name = cafe.clientcombo.Value 'If it fails create it
        Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(cafe.clientcombo.Value)
    Dim tbl As ListObject

    With ws
        .Cells.Clear
        .ListObjects.Add(xlSrcRange, .Range("A8"), , xlNo).Name = cafe.clientcombo.Value
        Set tbl = .ListObjects(1)

        tbl.HeaderRowRange.Cells(1, 1) = "Date"
        tbl.HeaderRowRange.Cells(1, 2) = "Produit"
        tbl.HeaderRowRange.Cells(1, 3) = "N°"
        tbl.HeaderRowRange.Cells(1, 4) = "Prix"
        tbl.HeaderRowRange.Cells(1, 5) = "somme"
    End With
   
    With ws
    .Range("A2") = "SAFA. LIVRAISON"
    .Range("A2:E2").Merge
    .Range("A2:E2").Font.Size = 18
    .Range("A2:E2").HorizontalAlignment = xlCenter
    .Range("A2:E2").Font.Color = 9851952
    
    .Range("B3").NumberFormat = "@"
    .Range("B3") = "07784"
    .Range("B3:D3").Merge
    .Range("B3:D3").Font.Size = 14
    .Range("B3:D3").HorizontalAlignment = xlCenter
    
    .Range("A5") = "Client:"
    .Range("B5") = cafe.clientcombo.Value
    .Range("A6") = "Secteur:"
    .Range("B6") = cafe.secteurcombo.Value
    .Range("A7") = "Telephone:"
    .Range("B7") = cafe.NclientBox.Value
    .Range("B7").NumberFormat = "0000 000 000"
    .Range("A9").EntireColumn.NumberFormat = "yyyy-mm-dd"
    .Range("B9").EntireColumn.NumberFormat = "General"
    .Range("C9").EntireColumn.NumberFormat = "General"
    .Range("D9").EntireColumn.NumberFormat = "#,#0""  DZD"""
    .Range("E9").EntireColumn.NumberFormat = "#,#0""  DZD"""
    End With
    clients.Range("G1").Value = cafe.clientcombo.Value
    clients.Range("G2").Value = cafe.secteurcombo.Value
    clients.Range("G3").Value = cafe.NclientBox.Value
    clients.Range("G3").NumberFormat = "0000 000 000"


Finally:
    On Error GoTo 0

End Sub

add client information into a table :

Sub add_client()

    On Error GoTo Finally
Try:
    Sheets(cafe.clientcombo.Value).Select 'Try to focus on the tab

    GoTo Catch
Catch:
Dim client_sheet As Worksheet
Dim table_list_client As ListObject
Dim table_client_row As ListRow
Set client_sheet = clients
Set table_list_client = client_sheet.ListObjects(1)
Set table_client_row = table_list_client.ListRows.Add
table_client_row.Range(1, 1).Value = "-"
 table_client_row.Range(1, 1).Value = clients.Range("G1").Value
 last_row_with_data = client_sheet.Range("A65536").End(xlUp).Row
 last_row_with_data = last_row_with_data
 client_sheet.Range("B" & last_row_with_data) = clients.Range("G2").Value
 client_sheet.Range("C" & last_row_with_data) = clients.Range("G3").Value

Finally:
    On Error GoTo 0

End Sub

from some answers i understood that it may be a problem with the rowsource of the combobox :

Private Sub combo_client()
Me.clientcombo.Value = ""
Me.RmclientBox.Value = ""
Me.Clientcombo2.Value = ""
Me.secteurcombo.Value = ""
Me.facBox.Value = ""
Me.factureBox.Value = ""
'
Me.clientcombo.Clear
Me.RmclientBox.Clear
Me.Clientcombo2.Clear
Me.secteurcombo.Clear
Me.facBox.Clear
Me.factureBox.Clear

Dim i As Integer
With clients
rng = .Range("A" & Rows.count).End(xlUp).Row
For i = 9 To rng
If .Range("A" & i) <> "" Then
Me.clientcombo.AddItem .Range("A" & i)
Me.RmclientBox.AddItem .Range("A" & i)
Me.Clientcombo2.AddItem .Range("A" & i)
Me.secteurcombo.AddItem .Range("B" & i)
Me.facBox.AddItem .Range("A" & i)
Me.factureBox.AddItem .Range("A" & i)

End If
Next i
End With
cafe.rmBox.RowSource = "clienttab"
cafe.addBox.RowSource = "clienttab"
cafe.stockBox.RowSource = "Tableau2"
End Sub
Private Sub combo_produit()
Me.ComboBox1.Value = ""
Me.ComboBox3.Value = ""
Me.ComboBox1.Clear
Me.ComboBox3.Clear
Dim i As Integer
With stock
rng = .Range("B" & Rows.count).End(xlUp).Row
For i = 9 To rng
If .Range("B" & i) <> "" Then
Me.ComboBox1.AddItem .Range("B" & i)
Me.ComboBox3.AddItem .Range("B" & i)

End If
Next i
End With

End Sub

i uploaded the excel file into a googledrive for more informations

visit https://docs.google.com/spreadsheets/d/1DFs_X0fgQOmLxdvSfUOXUco937xc0L2J/edit?usp=sharing&ouid=114015282963955706231&rtpof=true&sd=true

  • Seems like the majority of this code is finding the last row in a table using a nested loop. That's probably what's crashing it. I can't see your row numbers but I suspect this is looping quite a bit. Try these: [list object](https://stackoverflow.com/questions/43631926/lastrow-and-excel-table) and [sheet](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row) – Warcupine Aug 16 '21 at 18:47
  • it crashes every times , i tried this code and it workd – Youcef Merah Aug 16 '21 at 20:47
  • i can't post it here it says its too long , i'm new i don't know how to use the forum properly – Youcef Merah Aug 16 '21 at 20:50
  • @Youcef Edit your question and post the code there, never post the entire code in comment – Raymond Wu Aug 17 '21 at 00:33
  • @RaymondWu i did my friend ! i hope i've been more clear this time – Youcef Merah Aug 17 '21 at 17:49

0 Answers0