Link Listbox and sheets in excel for delete - VBA

I create listbox in excel with VBA userform. Its values are obtained from the Sheet in Excel. How can I delete the values in the sheet "database" while deleting the box list item? please help me.

Private Sub UserForm_Initialize()
Dim ws      As Worksheet
Dim rng     As Range

Dim MyArray 
Set ws = Sheets("Database")

Set rng = ws.Range("K2:L" & ws.Range("K" & ws.Rows.Count).End(xlUp).Row)

With Me.ListBox1
.Clear
.ColumnHeads = False
.ColumnCount = rng.Columns.Count

 MyArray = rng

.List = MyArray

.ColumnWidths = "90;90"
.TopIndex = 0
End With
End Sub

Private Sub CommandButton2_Click()
For lItem = Me.ListBox1.ListCount - 1 To 0 Step -1
    If ListBox1.Selected(lItem) Then
        ListBox1.RemoveItem lItem
        If Me.ListBox1.MultiSelect = fmMultiSelectSingle Then
            Exit For
        End If
    End If
Next
End Sub

Solution

How do I delete the values in the sheet "database"?

As you assign database items via the array method (not using ControlSource), you want to know how to synchronize listbox items with your data base after manual deletion.

Approach A) - Write the entire Listbox1.List

If you want a mirror image of the listbox items after the For- Next loop, you could simply write these items back to a given range (of course you should clear 'surplus rows', too) via the following one liner

    rng.Resize(Me.ListBox1.ListCount, 2) = Me.ListBox1.List

Instead of reduplicating the data range declaration in CommandButton2_Click, I'd suggest to declare it ONCE in the declaration head of the Userform code module (and omit it in Userform_Initialize):

Thus the complete code would be as follows:

►Additional notes due to comment

Insert these two code lines on top of your UserForm code module (and before any procedures).

Option Explicit is strictly recommended in any code to force the declaration of variable types (but you can't use this statement within a Sub as you did). The declaration Dim rng As Range OUTSIDE the other procedures (i.e. on top) allows that any procedure within this code module knows the rng variable.

Option Explicit               ' declaration head of the UserForm module
Dim rng as Range              ' ONE database declaration only!
                              ' << OUTSIDE of following procedures 
' << Start of regular procedures                              
Private Sub UserForm_Initialize()
Dim ws      As Worksheet
' Dim rng   As Range    ' << not needed here, see top declaration
Dim MyArray
Set ws = Sheets("Database")
Set rng = ws.Range("K2:L" & ws.Range("K" & ws.Rows.Count).End(xlUp).Row)
With Me.ListBox1
    .Clear
    .ColumnHeads = False
    .ColumnCount = rng.Columns.Count

     MyArray = rng

    .List = MyArray
    .ColumnWidths = "90;90"
    .TopIndex = 0
End With
End Sub

Private Sub CommandButton3_Click()   
Dim lItem&
For lItem = Me.ListBox1.ListCount - 1 To 0 Step -1
    If ListBox1.Selected(lItem) Then
        ListBox1.RemoveItem lItem           ' remove item from listbox
        If Me.ListBox1.MultiSelect = fmMultiSelectSingle Then
            Exit For
        End If
    End If
Next

rng.Offset(Me.ListBox1.ListCount, 0).Resize(rng.Rows.Count, 2) = "" ' clear rows
rng.Resize(Me.ListBox1.ListCount, 2) = Me.ListBox1.List             ' write list back

End Sub

Note that no rows are deleted physically, the resulting listbox items in the two target columns K:L are shifted up only (approach B allows to delete entire rows as well).

Approach B) - Help procedure within main loop

Using the same data range declaration in the declaration head of the UserForm ► as shown above (i.e. OUTSIDE the procedures as Subs or Functions), you could use a help procedure DelData allowing to distinguish between two principal cases:

[1] Shift up deleted cells in your database
[2] Delete the entire row

Event procedure CommandButton2_Click

Private Sub CommandButton2_Click()
' Purpose: delete items both from database and listbox
Dim lItem&
For lItem = Me.ListBox1.ListCount - 1 To 0 Step -1
    If ListBox1.Selected(lItem) Then
        DelData lItem, True     ' [1] True=delete items and shift up
       'DelData lItem, False    ' [2] False=delete entire row

        ListBox1.RemoveItem lItem           ' remove item from listbox
        If Me.ListBox1.MultiSelect = fmMultiSelectSingle Then
           Exit For                ' do it once in single select case
        End If
    End If
Next
End Sub

Help procedure DelData

Sub DelData(ByVal indx&, Optional ByVal bShiftUp As Boolean = True)
' Purpose: delete indicated row items in database
' Note:    data set in OP includes header
    If bShiftUp Then    ' [1] bShiftUp = True: delete row items and shift up
       rng.Offset(indx).Resize(1, rng.Columns.Count).Delete xlShiftUp
    Else                ' [2] bShiftUp = False: delete entire row of indicated items
       rng.Offset(indx).Resize(1, rng.Columns.Count).EntireRow.Delete
    End If
End Sub

Side note

It's recommended to fully qualify range references to avoid getting data from wrong workbooks, so I'd suggest the following statement in your UserForm_Initialize procedure:

Set ws = ThisWorkbook.Worksheets("Database")

Enjoy it :-)