none
Excel VBA ::: Identify duplicate & Update combobox list RRS feed

  • Question

  • Dear Friends,

    I 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<sup>st</sup> 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


    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<sup>nd</sup> 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<sup>st</sup> 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

    Wednesday, March 28, 2018 7:08 AM