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