VBA textbox not updating Unique ID

[Raymondc190466]

Hello All,

Below code writes the data to excel, and clear all data on the userform.
And the new complaint started, the Unique Id is not updated, if you stay on the form.

If you go to main menu, and re-open the form the Uniqe Id is updated.

How can I solve this issue?

Code for data to excel:

Private Sub CommandButton2_Click()

    'Write data to worksheet Overview Complaints

Dim ctl_Cont As Control
Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("Overview Complaints")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'copy the data to the database

ws.Cells(iRow, 1).Value = Me.TextBox6.Value 'Klacht Id
ws.Cells(iRow, 2).Value = Me.ComboBox2.Value 'Creator
ws.Cells(iRow, 3).Value = Me.TextBox8.Value 'Date of creation
ws.Cells(iRow, 6).Value = Me.TextBox7.Value 'Vendor Nr
ws.Cells(iRow, 7).Value = Me.ComboBox1.Value 'Vendor
ws.Cells(iRow, 8).Value = Me.TextBox10.Value 'Received Complaint
ws.Cells(iRow, 9).Value = Me.ComboBox3.Value 'Material
ws.Cells(iRow, 10).Value = Me.TextBox4.Value 'Purchase order
ws.Cells(iRow, 11).Value = Me.ComboBox4.Value 'Type of Complaint
ws.Cells(iRow, 12).Value = Me.TextBox11.Value 'Description
ws.Cells(iRow, 13).Value = Me.TextBox12.Value 'Corrective action
ws.Cells(iRow, 14).Value = Me.TextBox13.Value 'total cost

'clear the data

'Me.TextBox8.Value = ""
'Me.TextBox6.Value = ""
Me.ComboBox2.Value = ""
Me.TextBox7.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox10.Value = ""
Me.ComboBox3.Value = ""
Me.TextBox4.Value = ""
Me.ComboBox4.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
Me.TextBox17.Value = ""
Me.TextBox16.Value = ""
Me.TextBox18.Value = ""
Me.TextBox19.Value = ""
Me.TextBox20.Value = ""
Me.TextBox21.Value = ""
Me.TextBox13.Value = ""

Me.TextBox8.SetFocus

'For Each ctl_Cont In Me.Controls
'If TypeName(ctl_Cont) = "TextBox" Or TypeName(ctl_Cont) = "ComboBox" Then
'If ctl_Cont.Value = "" Then
'MsgBox "Please " & TypeName(ctl_Cont) & Space(1) & ctl_Cont.Name & " fill in!"
'End If
'End If
'Next

End Sub

Code with Unique Id creater:

Private Sub UserForm_Initialize()
    Dim xRg As Range
    Dim Location As String
    Dim SerialNumber As Long
    Dim x As Long
    
    With Sheets("Overview Complaints").Range("A2", Sheets("Overview Complaints").Range("A" & Rows.Count).End(xlUp))
        x = Evaluate("max(--right(" & .Address & ",3))")
    End With
    TextBox6.Value = "DES-" & Format(Date, "yyyy-mm-") & Format(x + 1, "000")
    
    Set xRg = Worksheets("Supplier").Range("A2:B77")
    Me.ComboBox1.List = xRg.Columns(1).Value
  
    With Worksheets("Combobox")
        ComboBox2.List = .Range("C1:C" & .Range("C" & .Rows.Count).End(xlUp).Row).Value
    With Worksheets("Combobox")
        ComboBox3.List = .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
    With Worksheets("Combobox")
        ComboBox4.List = .Range("E1:E" & .Range("E" & .Rows.Count).End(xlUp).Row).Value
    End With
    End With
    End With
    
TextBox8.Value = Format(Date, "dd/mm/yyyy")
TextBox1.Font.Size = 20
Me.TextBox2.Value = "URSA Benelux"
FrmMenu.BackColor = RGB(0, 0, 255)
FrmComplaint.BackColor = RGB(0, 0, 255)
      
End Sub

 

[Saurabhj]

Hi,

Instead of clearing all field use below:

unload me
me.show

Hope it resolve the issue.

Thanks,
Saurabh