Userform issue/challenge - Copying listbox selection to a sheet

Ronie

New Member
Joined
Nov 30, 2012
Messages
9
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.

1663084886120.png


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.

1663085286259.png


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.

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

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

VBA Code:
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
VBA Code:
    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
VBA Code:
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

VBA Code:
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.

SnoBusiness ProcessSystem/ToolData TypeCategoryPurposeCategoryAttributeTypeNameRetentionStorageDeletion3rd party transferTransfer Type3rd party name
1​
Business ProcessTool 1Employee DataRecruitmentPerforming Background ChecksBackground ChecksBackground ChecksSource TypeSource NameRetentionStorageDeletion3rd party transfer3rd party type3rd party name
1​
Business ProcessTool 1Employee DataRecruitmentPerforming Background ChecksBackground ChecksReferencesSource TypeSource NameRetentionStorageDeletion3rd party transfer3rd party type3rd 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
 

Attachments

  • 1663084960878.png
    1663084960878.png
    33 KB · Views: 14

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top