VBA textbox not updating Unique ID


New Member
Aug 19, 2016
Office Version
  1. 365
  1. Windows
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:
VBA Code:
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 = ""


'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

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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.


Well-known Member
Jun 6, 2020
Office Version
  1. 365
  2. 2019
  3. 2016
  1. Windows

Instead of clearing all field use below:

VBA Code:
unload me

Hope it resolve the issue.

Upvote 0

Forum statistics

Latest member

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