Search code examples
checkboxtextboxlistboxuserform

VBA Edit Userform Listbox (based on excel database) using Checkboxes and Textboxes

I've a database (5 column's and the row's are adaptive). This data is sorted as: Name, Surname, Date of Birth and Promotion year. I've also made an Userform based on a Listbox that can show this 4 informations.

Now i would like to edit this database using checkboxes and textboxes in the userform. I already made my userform but the problem persists in transfering the data of the userform (checkbox: true or false and textbox) into the database on the corresponding cell (of the right person).

Any idea how I should think or work on this? I already tried some stuff but i can't show up with a solution that can transfer AND the checkbox AND the Textbox information.

Greetz Userform Layout

Solution

  • Assuming 5th column as A(status)

    |Status|Name|Surname| DOB |Year|

    Checkbx1 Data going to A(status)

    Textbx1 Data going to B(Name)

    Textbx2 Data going to C(Surname)

    Textbx3 Data going to D(DOB)

    Textbx4 Data going to E(Year)

    "You may assign this code to a command button"

    'Transfer of Data

    If Form1.checkbox1=TRUE then

    Shee1.Range("a:a").End(xlDown).Offset(1).Select

    Activecell.value="True"

    else

    Activecell.value="False"

    end if

    'transferring Name to B

    ActiveCell.Offset(0, 1).Value = StrConv(TextBox1, vbProperCase)

    'transferring Surname to C

    ActiveCell.Offset(0, 2).Value = StrConv(TextBox2, vbProperCase)

    'transferring DOB to D

    ActiveCell.Offset(0, 3).Value = StrConv(TextBox3, vbProperCase)

    'transferring Year to E

    ActiveCell.Offset(0, 4).Value = StrConv(TextBox4, vbProperCase)