VBA textbox not updating Unique ID

Raymondc190466

New Member
Joined
Aug 19, 2016
Messages
24
Office Version
  1. 365
Platform
  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 = ""

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:

Code:
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi,

Instead of clearing all field use below:

VBA Code:
unload me
me.show

Hope it resolve the issue.

Thanks,
Saurabh
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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