Excel VBA : Identify duplicate & Update list

[RAKESH230583]

Dear FriendsI have two userform 1st one to capture the details and 2nd one to make any update under data captured from 1st userform.

Details from 1st userform gets saved in sheet1 and userform 2 uses the same data reference for modification and update the current records.

Case 1 refer to 1st userfrom to capture details and case 2 refer to 2nd Userfrom used to modify data and status.

Case 1:

1^st Userform (Create) – Wherein under Textbox13 – Auto reference no. gets captured at userform initialization stage.

Format of Auto generated reference no. #27031801 – Once the user open the form – Textbox13 – Shows auto generated reference no. and further when userform gets saved the same data is transferred to sheet1 under column (“A:A”) starting from cell A3.

Sub:

Sub Unique_ID() ' ' Unique_ID Macro Sheets("System Issue Data").Select Range("A1").Select ActiveCell.FormulaR1C1 = _ "=CONCATENATE(""#"",VALUE(TEXT(TODAY(),""ddmmyy"")),IF(COUNTA(C[1])<10,CONCATENATE(""0"",COUNTA(C[1])),COUNTA(C[1])))" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False End Sub

<tbody>
</tbody>

Question :

1. Is it possible that – when any user save the Userform – msgbox to appear if the generated reference no. is already there in sheet1 under (“A:A”) (considering the said workbook is shared.)

Case 2:

2^nd Userform (Updatedetails) - ComboBox11 is used to evidence the auto generated reference no. under sheet1 under (“A:A”)

Below usercode works fine – but when I modify the formula of generating the under unique reference no. – to remove special characters # and to change the format from #27031801 to 27031801 – Don't know why below code does not work ?

Also, is it possible that ComboBox11 only shows the list of those cases which have status as “OPEN” and not “CLOSED” – User captured the status in 1^st Userform (Create) { same above case1} – and it further gets saved in sheet1 under column (“AB:AB”) starting from cell AB3.

Private Sub ComboBox11_enter() Dim i As Integer Dim final As Integer Dim DP As String ComboBox11.BackColor = &H80000005 For i = 1 To ComboBox11.ListCount ComboBox11.RemoveItem 0 Next i For i = 3 To 5000 If Sheet1.Cells(i, 1) = "" Then final = i - 1 Exit For End If Next For i = 3 To final DP = Sheet1.Cells(i, 1) ComboBox11.AddItem (DP) Next End Sub Private Sub ComboBox11_Click() Dim i As Integer Dim final As Integer For i = 3 To 5000 If Sheet1.Cells(i, 1) = "" Then final = i - 1 Exit For End If Next For i = 3 To final If ComboBox11 = Sheet1.Cells(i, 1) Then ComboBox1 = Sheet1.Cells(i, 3) ComboBox2 = Sheet1.Cells(i, 4) ComboBox3 = Sheet1.Cells(i, 5) ComboBox4 = Sheet1.Cells(i, 6) ComboBox5 = Sheet1.Cells(i, 7) ComboBox6 = Sheet1.Cells(i, 8) TextBox8 = Sheet1.Cells(i, 9) TextBox9 = Sheet1.Cells(i, 10) TextBox1 = Sheet1.Cells(i, 13) TextBox2 = Sheet1.Cells(i, 14) TextBox3 = Sheet1.Cells(i, 15) TextBox4 = Sheet1.Cells(i, 16) TextBox5 = Sheet1.Cells(i, 17) TextBox6 = Sheet1.Cells(i, 18) TextBox7 = Sheet1.Cells(i, 19) ComboBox8 = Sheet1.Cells(i, 20) ComboBox9 = Sheet1.Cells(i, 21) ComboBox10 = Sheet1.Cells(i, 22) Exit For End If Next If Me.ComboBox11 = "" Then Else Me.TextBox4.BackColor = &H80FFFF Me.ComboBox7.BackColor = &H80FFFF Me.TextBox9.BackColor = &H80FFFF Me.TextBox10.BackColor = &H80FFFF Me.TextBox11.BackColor = &H80FFFF Me.Date_Picker.CalendarBackColor = &H80FFFF End If End Sub

<tbody>
</tbody>

 

[RAKESH230583]

Hi Friends,

Kindly revert if above is not clear or required any additional information from my end.

Thanks & Regards,
RS

 

[RAKESH230583]

Hi Friends - If you feel that the above said requirement is not possible to do with the help of VBA code then please do let me know ...

I am saying this because till now this post has been reviewed by 23 but not even a single solution or guidance has been posted or reverted against above case.