Userform issue/challenge - Copying listbox selection to a sheet

[Ronie]

Hi,
I haven't used VBA in quite a while but I recently got a chance to get back into it. Most of what I did is copying/pasting codes and editing them as best as I could but I'm a little bit stuck now that I want to do something a little bit more advanced than what the initial code was doing. Hopefully someone can help .

What I'm trying to do

I'm trying to capture information on the data colleagues are processing during 1v1 meetings. General details and list of processes are captured in a first form.

In the second form that is called from the first form, I deep dive into the processes that were listed in the first form. Some fields are carried over from one form to another, highlighted in yellow.

In that 2nd form, I have 2 sets of dependent listboxes (ListItemPurposeHighLevel/ListItemPurpose and ListItemDataCat/ListSubItemAttribute). When I select a row from one list, the rowsource of the other changes. I'm adding the code below, in case that's the cause of the issue I describe later.

Private Sub ListItemPurposeHighLevel_Click()
Dim x As Integer
x = ListItemPurposeHighLevel.ListIndex

Select Case x
Case Is = 0
ListItemPurpose.RowSource = "AccountRegistration"
[...]

Private Sub ListItemDataCat_Click()
Dim x As Integer
x = ListItemDataCat.ListIndex

Select Case x
Case Is = 0
ListSubItemAttribute.RowSource = "BackgroundChecks"
[...]

Once I have made my various selection, I can either Add a new entry/row to a specified excel table, or Edit an existing entry/row that I selected. That table range is used as a row source for yet another listbox (Listbox2) that contains all the captured information from the 2 listbox sets and other comboxbox/textbox.

Issue

The edit function works as follows:

• Double-click on a row from Listbox2
  • This causes all the previous listbox, comboboxes and textboxes to be repopulated with the value from the row I double-clicked

    Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    UserForm3.ListItemPurposeHighLevel.Value = UserForm3.ListBox2.List(Me.ListBox2.ListIndex, 4)
    UserForm3.ListItemPurpose.Value = UserForm3.ListBox2.List(Me.ListBox2.ListIndex, 5)
    UserForm3.ListItemDataCat.Value = UserForm3.ListBox2.List(Me.ListBox2.ListIndex, 6)
    UserForm3.ListSubItemAttribute.Value = UserForm3.ListBox2.List(Me.ListBox2.ListIndex, 7)
    
    Me.ComboBoxPISourceType.Value = Me.ListBox2.List(Me.ListBox2.ListIndex, 8)
    Me.TextBoxPISourceName.Value = Me.ListBox2.List(Me.ListBox2.ListIndex, 9)
    Me.TextBoxRetention.Value = Me.ListBox2.List(Me.ListBox2.ListIndex, 10)
    Me.TextBoxStorage.Value = Me.ListBox2.List(Me.ListBox2.ListIndex, 11)
    Me.TextBoxDeletion.Value = Me.ListBox2.List(Me.ListBox2.ListIndex, 12)
    Me.TextBox3rdpartytransfer.Value = Me.ListBox2.List(Me.ListBox2.ListIndex, 13)
    Me.ComboBox3rdpartytype.Value = Me.ListBox2.List(Me.ListBox2.ListIndex, 14)
    Me.TextBox3rdpartyname.Value = Me.ListBox2.List(Me.ListBox2.ListIndex, 15)

End Sub

• Change the value of any textbox/combobox/listbox that needs editing
• Click Edit

Private Sub CommandButtonEditSubForm_Click()
    If Selected_SubList = 0 Then
        MsgBox "Please select a row", vbOKOnly + vbInformation, "Edit"
        Exit Sub
    End If 'Values in TextBoxRowNumber and TextBoxSno indicates the row to overwritte
    Me.TextBoxRowNumberSub.Value = Selected_SubList + 1
    Me.TextBoxSnoSub2.Value = Me.ListBox2.List(Me.ListBox2.ListIndex, 0)
    
    Call Submit_data_details
    
    Dim irow As Long
    irow = [counta(Database2!A:A)] + 1 'Row 1 & 2 are headers, need to add +2 to ensure we don't overwrite them

    With UserForm3
        .ListBox2.ColumnCount = 18
        .ListBox2.ColumnHeads = True
        'Column no----------------01,02,03,04,005,006,007,08,09,10,11,12,13,14,15,16,17,18
        .ListBox2.ColumnWidths = "25,73,73,73,200,100,125,73,73,50,50,50,50,50,50,50,00,00"
        If irow > 2 Then
           .ListBox2.RowSource = "Database2!A3:P" & irow
        Else
           .ListBox2.RowSource = "Database2!A3:P3"
        End If
    End With
End Sub

Sub Submit_data_details()

   Dim sh As Worksheet
    Dim irow As Long

    Set sh = ThisWorkbook.Sheets("Database2")

    If UserForm3.TextBoxRowNumberSub.Value = "" Then 'If the rownumber field is empty, then

        irow = [counta(Database2!A:A)] + 2 'count cells with a value and add +2 (2 rows of header)
    Else

        irow = UserForm3.TextBoxRowNumberSub.Value 'Otherwise the rownumber that is specified

    End If

    With sh

        .Cells(irow, 1) = irow - 2
        .Cells(irow, 2) = UserForm3.TextBoxBusinessP.Value
        .Cells(irow, 3) = UserForm3.TextBoxSystemT.Value
        .Cells(irow, 4) = UserForm3.TextBoxDataT.Value
        
        .Cells(irow, 5) = UserForm3.ListItemPurposeHighLevel.Value
        .Cells(irow, 6) = UserForm3.ListItemPurpose.Value
        .Cells(irow, 7) = UserForm3.ListItemDataCat.Value
        .Cells(irow, 8) = UserForm3.ListSubItemAttribute.Value
        
        .Cells(irow, 9) = UserForm3.ComboBoxPISourceType.Value
        .Cells(irow, 10) = UserForm3.TextBoxPISourceName.Value
        
        .Cells(irow, 11) = UserForm3.TextBoxRetention.Value
        .Cells(irow, 12) = UserForm3.TextBoxStorage.Value
        .Cells(irow, 13) = UserForm3.TextBoxDeletion.Value
        
        .Cells(irow, 14) = UserForm3.TextBox3rdpartytransfer.Value
        .Cells(irow, 15) = UserForm3.ComboBox3rdpartytype.Value
        .Cells(irow, 16) = UserForm3.TextBox3rdpartyname.Value
        
        .Cells(irow, 17) = Application.UserName
        .Cells(irow, 18) = [Text(Now(), "DD-MM-YYYY HH:MM:SS")]

    End With

    With UserForm3 'To refresh the list if "Add" is clicked multiple times

        .ListBox2.ColumnCount = 18
        '1. Sno 2. Business Process 3. System/Tool 4. Data Type 5. (Purpose) Category 6. Purpose 7. (Data) Category 8.attribute 9. source type 10. source name
        '11. Retention 12. Storage 13. Deletion 14. 3rd party transfer 15. Transfer Type 16.3rd party name 17. Created by 18. Created on
        .ListBox2.ColumnHeads = True
        'Column no----------------01,02,03,04,005,006,007,08,09,10,11,12,13,14,15,16,17,18
        .ListBox2.ColumnWidths = "25,73,73,73,200,100,125,73,73,50,50,50,50,50,50,50,00,00"

        If irow > 2 Then
           .ListBox2.RowSource = "Database2!A3:P" & irow

        Else
           .ListBox2.RowSource = "Database2!A3:P3"

        End If
                    
        End With

End Sub

However, for a reason that escapes me, even though the values from the listboxes are highlighted (and therefore selected?), the values from the listboxes are not captured in the table nor in the collated data listbox if I haven't previously clicked on those listboxes, at least once after opening the form.

Initially I had issues with ListItemPurposeHighLevel and ListItemDataCat but now it seems that the columns that are not being captured are ListItemPurpose and ListSubItemAttribute. However, as mentioned previously if I manually click on each listbox previous to doubleclicking Listbox2 and clicking Add, all fields are captured appropriately.

Any idea? I hope what I explained above makes sense.

Challenges

There are 2 "advanced" features I'd like to add to my form but I have no idea how to go about it.

Feature 1. Multiselect Attribute ListSubItemAttribute
I'd like people to be able to select more than one attribute in the ListSubItemAttribute listbox so that when Add is clicked, for each selected attribute, a new row is created in listbox2 and its source table. I know I need to set multiselect to fmMultiSelectMulti or fmMultiSelectExtended but I don't know how to edit the Sub Submit_data_details() code accordingly (shared higher in this post).

e.g. This would be the result I'd expect if I chose 2 attributes. 2 rows are created that contain the same values, except for the attributes.

Sno

Business Process

System/Tool

Data Type

Category

Purpose

Category

Attribute

Type

Name

Retention

Storage

Deletion

3rd party transfer

Transfer Type

3rd party name

1​

Business Process

Tool 1

Employee Data

Recruitment

Performing Background Checks

Background Checks

Background Checks

Source Type

Source Name

Retention

Storage

Deletion

3rd party transfer

3rd party type

3rd party name

1​

Business Process

Tool 1

Employee Data

Recruitment

Performing Background Checks

Background Checks

References

Source Type

Source Name

Retention

Storage

Deletion

3rd party transfer

3rd party type

3rd party name

Any pointer/help would be welcome! Again, please understand I'm a VBA novice.

Feature 2. Multiselect rows in Listbox2 and duplicate multiselection/edit multiselection

In the 2nd form, I handle each defined business process separately, one tool/system at the time.

However sometimes, 2 processes will use the same tools and/or data, or 2 different systems/tools would use the same type of data. When that's the case, I'd like to select multiple rows from listbox2 and duplicate them as follows: the values in the row would remain the same as the duplicated rows except for column 2, 3, 4 that should match the values in the yellow fields.

Any idea of how this can be done?

Thank you so much for reading this wall of text, and thank you for your input,

-Ronie