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.
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:
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.
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
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.
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 SubHowever, 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
