View original (French)

Combobox and textbox to display

Solved
Linebaker Posted messages 53 Status Member -  
Willzac Posted messages 281 Status Member -

Hello,

Here is the code and my issues in comments. If someone can help me. Thank you.

Private Sub ComboBox1_Change()
Dim Lg As Long
'checks if ComboBox1 exists, if yes we add a value from column "B" into TextBox1
    With Sheets("feuil3")
        Lg = ComboBox1.ListIndex + 1
        TextBox1.Value = Cells(Lg, 1).Value
    End With
    
    If TextBox1.Value > Date - 365 Then 'if the date in TextBox1 is greater than today's date - 365 days (the value must be greater than 365)
        CommandButton2.Enabled = False
    End If
    
End Sub

Private Sub CommandButton1_Click()
Dim Cherche As String

    If ComboBox1 = "" Then
        MsgBox "You must select a name?", vbOK, "Name of the person"
        Exit Sub
    End If
            
    Sheets("Feuil3").Select

If Range("a2") = "" Then 'if A2 is empty, we add the value from ComboBox1 as well as today's date
    Range("a65536").End(xlUp).Offset(1, 0).Select
    ActiveCell = ComboBox1.Value
    ActiveCell.Offset(0, 1) = Me.TextBox1.Value = Format(Now, "yyyy-mm-dd")
Else 'if the value from ComboBox1 already exists
    Cherche = ComboBox1.Value 'searches for the name
    If Cherche = "" Then 'if search (value of ComboBox1) does not exist
        'add value from ComboBox1 in A and today's date in B
    Else 'if the value from ComboBox1 already exists, we write today's date in B
        L = Sheets("Formulaire").Cells.Find(What:=Cherche, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row
        Cells(L, "B") = Me.TextBox1.Value = Format(Now, "yyyy-mm-dd")
    End If
End If
        
    ComboBox1 = ""
    TextBox1 = ""
    Unload Me
      
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
  Set f = Sheets("feuil4")
  Set MonDico = CreateObject("Scripting.Dictionary")
  a = f.Range("A2:A" & f.[A65000].End(xlUp).Row)
  For i = LBound(a) To UBound(a)
    If a(i, 1) <> "" Then MonDico(a(i, 1)) = ""
  Next i
  Me.ComboBox1.List = MonDico.keys

    With Me.ComboBox1
           .ListIndex = -1
    End With
End Sub

6 answers

  1.
    Linebaker Posted messages 53 Status Member 1
     

    Hello,

    I managed to code everything. The only problem I have left is the date format of textbox1 when the name is already in sheet3. Textbox1 displays the date 21/04/2003 instead of 2023-04-21 as the cell does. I tried to force the display format of textbox1 without success. Do you have any idea how to code it?

    I am including the corrected file.

    https://1fichier.com/?nykoemhr7zr0qxm3alhn

    Thank you

    1
  2. Willzac Posted messages 281 Status Member 15
     

    Hello,

    Having the file will help us even more, (to test and see the Userform)

    Best regards

    Willzac

    0
  3. Linebaker Posted messages 53 Status Member 1
     

    Hello Willzac,

    Indeed.

    https://1fichier.com/?kx4qaff4chbxc0u40acq

    Thank you

    0
  4. Willzac Posted messages 281 Status Member 15
     

    Good evening,

    Can you explain what needs to be done?

    I would rather recode if possible. (for example, the form sheet is not included in your file)

    What are the steps?

    Best regards

    0
  6. Linebaker Posted messages 53 Status Member 1
     

    Good evening,

    From sheet4, select a value (name) and check if this value exists in sheet3. If this value is not present, it should be added in column A and today's date should be placed in column B. If this value already exists in column A of sheet3, only today's date should be added in column B.

    I hope this is clear; otherwise, feel free to ask me for more information.

    Thank you very much for your help.

    0
  7. Willzac Posted messages 281 Status Member 15
     

    Hello,

    You need to use the format:

      "dd-mm-yyyy"

    instead of the formats you provided.

    Willzac

    0