Is it possible to change the checklist output (in the VBA code) to show them items as a visual list within the cell... separated by alt+enter (carriage return list) rather than a semicolon? Is it also possible to copy/paste the checklist that I have created into other cells? I am creating a list of schools and my checklist shows the services that each school provides. I hope this makes sense! Thank you for the amazing tutorial!
Hello @amylawson404, You are most welcome. Thanks for your kind words. Yes, it is possible to change the checklist output. You can modify the VBA code to display the selected items as a list separated by Alt+Enter (carriage return) instead of a semicolon. For copying/pasting the checklist to other cells, you will need to adjust the code to handle the pasting functionality properly, ensuring the checklist works for each cell. Updated VBA Code: Sub Button_Click() Dim buttonShape As Shape, listOption As Variant, M As Integer, N As Integer Dim xP As String, resultStr As String Set buttonShape = ActiveSheet.Shapes(Application.Caller) Set checkListBox = ActiveSheet.checkList If checkListBox.Visible = False Then checkListBox.Visible = True buttonShape.TextFrame2.TextRange.Characters.Text = "Tick the Passed Students" resultStr = Range("CheckListOutput").Value If resultStr "" Then resultArr = Split(resultStr, Chr(10)) ' Split using carriage return For M = checkListBox.ListCount - 1 To 0 Step -1 xP = checkListBox.List(M) For N = 0 To UBound(resultArr) If resultArr(N) = xP Then checkListBox.Selected(M) = True Exit For End If Next N Next M End If Else checkListBox.Visible = False buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here" For M = checkListBox.ListCount - 1 To 0 Step -1 If checkListBox.Selected(M) = True Then listOption = checkListBox.List(M) & Chr(10) & listOption ' Use carriage return instead of semicolon End If Next M If listOption "" Then Range("CheckListOutput") = Left(listOption, Len(listOption) - 1) Else Range("CheckListOutput") = "" End If End If End Sub The items are now split and joined using Chr(10) (carriage return) instead of a semicolon (;), so they'll appear as a list when you press Alt+Enter. The code still allows the checklist output to be copied and pasted into other cells, and the checklist will function in those cells. Regards ExcelDemy
@@exceldemy2006 thank you very much for taking the time to provide this information. So helpful and kind! I have subscribed. I cannot figure out how to copy/paste the checklist for each school/row. Is there another video that shows how to do this? Thank you again!
Hello @amylawson404, You're most welcome, and thank you for subscribing! Unfortunately, we don't have a specific video on that yet. To copy and paste the checklist for each school or row, you must ensure that each row's checklist functions independently. Use ActiveCell in the code to tie the checklist output to the selected cell. Sub Button_Click() Dim buttonShape As Shape, listOption As Variant, M As Integer, N As Integer Dim xP As String, resultStr As String Set buttonShape = ActiveSheet.Shapes(Application.Caller) Set checkListBox = ActiveSheet.checkList If checkListBox.Visible = False Then checkListBox.Visible = True buttonShape.TextFrame2.TextRange.Characters.Text = "Tick the Passed Students" resultStr = ActiveCell.Value ' Use active cell instead of a fixed range If resultStr "" Then resultArr = Split(resultStr, Chr(10)) ' Split using carriage return For M = checkListBox.ListCount - 1 To 0 Step -1 xP = checkListBox.List(M) For N = 0 To UBound(resultArr) If resultArr(N) = xP Then checkListBox.Selected(M) = True Exit For End If Next N Next M End If Else checkListBox.Visible = False buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here" For M = checkListBox.ListCount - 1 To 0 Step -1 If checkListBox.Selected(M) = True Then listOption = checkListBox.List(M) & Chr(10) & listOption ' Use carriage return instead of semicolon End If Next M If listOption "" Then ActiveCell.Value = Left(listOption, Len(listOption) - 1) ' Store result in the active cell Else ActiveCell.Value = "" End If End If End Sub Once the checklist works for one row, simply copy the cell with the button and the checklist, then paste it into other rows. The VBA will handle the dynamic references for each cell. Regards ExcelDemy
Hello @vkas6608, To display names in a drop-down checklist and map corresponding email addresses, you can modify the code to include a lookup feature. Here's a detailed approach: Create a Helper Table: In Excel, create a table where column A contains names, and column B contains the associated email addresses. Modify VBA Code: After selecting a name from the checklist, use a lookup function to fetch the email. You can use VLOOKUP or INDEX-MATCH to search for the corresponding email. For example, assuming your table of names and emails is in the range A2:B100, you can modify the VBA code like this: Dim email As String email = Application.WorksheetFunction.VLookup(listOption, Range("A2:B100"), 2, False) This will pull the email address associated with the selected name and can be displayed in a cell or message box. Update Code for Multiple Selections: If you have multiple names selected, the code must loop through each selected name, perform the lookup, and concatenate the results into a string or update the display accordingly. Example for displaying emails: You can add another section in the code to display the emails next to the names or in a separate cell after the selection. Regards ExcelDemy
hi is it possible to make the checkbox dynamic based on a criteria? for example if I select region as south, I want to display a list of checkboxed consiting of states that are in the south region. the list should change when I select another region
Dear, thanks for your exciting question. It is definitely possible to use Excel with VBA. Please check the following: www.exceldemy.com/wp-content/uploads/2024/03/Output-of-making-the-checkbox-dynamic-based-on-a-criteria.gif You can download the solution workbook from: www.exceldemy.com/wp-content/uploads/2024/03/Ranjith-Ganesan-SOLVED.xlsm If you have any questions regarding the solution, please post them within the ExcelDemy Forum. ExcelDemy Forum: exceldemy.com/forum/
Hello @ahizalcammysh, You can follow this tutorial to learn how to use the code. Then from the given workbook just copy the code. Currently, we don't have the video tutorial for the workbook. If you face any issues at any step you can mention it hear. We are here to help you. Regards ExcelDmy
Hi, to me the VBA code is not working and, clicking several times, it get stuck at line resultStr = Range("CheckListOutput").Value Range("CheckListOutput") = Mid(listOption, 1, Len(listOption) - 1) I double checked all names are kept consistent as indicated. Any idea why this happens?
Hello @claudiasgiarovello, On our end, the VBA code is working perfectly. Rechecked the code to confirm it. www.exceldemy.com/wp-content/uploads/2024/07/selecting-value-from-drop-down-list.png Ensure you replace the curly quotation marks with straight quotation marks to avoid syntax errors in VBA. If the problem persists, double-check the name of the range "CheckListOutput" to ensure it matches exactly with what is in your Excel workbook. 1. Ensure all quotes are straight quotes ("). 2. Check that "CheckListOutput" matches the actual named range in your Excel workbook. 3. Verify that "checkList" matches the name of your ActiveX listbox control. If these steps don't resolve the issue, consider providing more details about any specific error messages or behaviors you encounter. Regards ExcelDemy
Hi, Everything went perfect. But, After I select the button and the list box shown, I wasn't able to click on it. is that a way to overcome this ? Thank you
Hello @amaliakhaerunnisa7253, The list box may not be interactive due to Excel's control properties or settings. Try selecting the list box, then go to the “Developer” tab, click on “Properties,” and ensure “Enabled” is set to “True". This should allow clicking and interacting with the list box. If issues persist, saving and reopening the file can sometimes reset control functions. Regards ExcelDemy
@@exceldemy2006 Hi, It seems the list box were not interactive because I freeze some rows. after I unfreeze them the list box work out just fine. Thank you!
Hello @amaliakhaerunnisa7253, You are most welcome. Thanks for your feedback. Glad to hear that the problem is solved. Keep exploring Excel with ExcelDemy! Regards Shamima Sultana ExcelDemy
Declaring more than one Variable on a single line. Like M, N as integer only declares the last variable as an integer. the other ones will be declared as an variant.
Hello @danp6101, We appreciate your attention to detail. It appears our video contained an oversight. Yes, your understanding is correct. In VBA, if you declare multiple variables on a single line and only specify the type for the last variable, the preceding variables will default to type Variant unless their types are explicitly specified. The correct form for a single line will be: Dim buttonShape As Shape, listOption As Variant, M As Integer, N As Integer Or, explicitly specified. Dim buttonShape As Shape Dim listOption As Variant Dim M As Integer Dim N As Integer We updated the VBA code in the article. Thanks for your feedback. Regards ExcelDemy
@@dimitooo Dear, Thanks for your comments! Of course, we will help you; we are always here to overcome your difficulties. You can add two types of list boxes to a worksheet: Form Controls and ActiveX Controls. Form Controls are more straightforward to use. On the other hand, ActiveX Controls are more versatile and offer more properties and customization options. The Property option you refer to, which allows more detailed customization, is only available in ActiveX Controls. So, in order to find the Property options by right-clicking on a list box, you must insert a list box from ActiveX Control. Please check the following: www.exceldemy.com/wp-content/uploads/2024/06/Two-types-of-list-boxes-are-used-for-a-worksheet-Form-Controls-and-ActiveX-Controls.gif
@@exceldemy2006 thank you so much :) my other question is how can I connect more Listboxes then one with one Modul or do I have to create mor codes on vba?
@@dimitooo Dear, You are very welcome. We always are here to help. If you want to insert more than one list box, follow the same procedure outlined here. However, you must create new sub-procedures and assign them to the new shapes accordingly. It is important to remember that you must modify the VBA sub-procedures as well; you can keep them in a single module. I have demonstrated your situation and inserted another list box in the same worksheet. Please check the following for a better understanding: www.exceldemy.com/wp-content/uploads/2024/06/Create-more-than-one-Multi-checkbox-dropdown-list.gif You can download the Excel file: www.exceldemy.com/wp-content/uploads/2024/06/Dimi-Topalidis-SOLVED.xlsm
Hello @kim21124, You will get it in "4:01 - Inserting VBA code into the Module". You will find the information about getting the code at 4:26 minutes. Checkout the article which is given in the description box to get the code: www.exceldemy.com/how-to-create-a-drop-down-checklist-in-excel/ Regards ExcelDemy
Hello @Tan_Rainbow, We've used an ActiveX ListBox control to create the checklist, use the following code to clear the checkbox from the checklist. Sub ClearActiveXListBox() Dim listBox As OLEObject Set listBox = ActiveSheet.OLEObjects("checkList") ' Change ListBox1 to the name of your ListBox Dim i As Long For i = 0 To listBox.Object.ListCount - 1 listBox.Object.Selected(i) = False Next i End Sub This code loops through all items in the ListBox and clears their selection. Regards ExcelDemy
Hello @jenniferalmeida5627, In our existing VBA code, the previously stored values in the output cell are overwritten when a new selection is made. This happens during the Button_Click event when the new selection is written directly into CheckListOutput, replacing the previous value. To preserve previous selections, we adjusted the logic so that the newly selected value is appended to the existing content rather than overwriting it. Here's an update to your code: Sub Button_Click() Dim buttonShape As Shape, listOption As String, M As Integer Dim xP As String Set buttonShape = ActiveSheet.Shapes(Application.Caller) Set checkListBox = ActiveSheet.checkList If checkListBox.Visible = False Then checkListBox.Visible = True buttonShape.TextFrame2.TextRange.Characters.Text = "Tick the Passed Students" resultStr = Range("CheckListOutput").Value If resultStr "" Then resultArr = Split(resultStr, ";") For M = checkListBox.ListCount - 1 To 0 Step -1 xP = checkListBox.List(M) If resultArr(0) = xP Then checkListBox.Selected(M) = True Exit For End If Next M End If Else checkListBox.Visible = False buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here" For M = checkListBox.ListCount - 1 To 0 Step -1 If checkListBox.Selected(M) = True Then listOption = checkListBox.List(M) Exit For ' Ensure only one selection End If Next M ' Append the new selection if it's not already in the output If listOption "" Then If InStr(Range("CheckListOutput").Value, listOption) = 0 Then If Range("CheckListOutput").Value "" Then Range("CheckListOutput").Value = Range("CheckListOutput").Value & ";" & listOption Else Range("CheckListOutput").Value = listOption End If End If End If End If End Sub Regards ExcelDemy
Hi there, I have a small issue. When I select the names, they don't pop up as "blue," and if I hit the interactive button, it does nothing. Then I tried double-clicking on the names; they still didn't turn blue, but using the interactive button, they inserted the names. Cant you find a solution to fix this?! Do you have any idea?
Hello @lucianabueno6178, In our end, the code is working fine. As the names don't turn blue when selected and the interactive button doesn't work as expected, follow these steps to troubleshoot your code: 1. Ensure the code is correctly linked to the List Box and that the event handlers are properly set up. 2. Verify the List Box's properties and ensure it's set to MultiSelect. Enter checkList in the (Name) text box. Refer to the cells B5:B12 in the ListFillRange text box. Choose option 1 - fmListStyleOption from the ListStyle option list. Choose option 1 - fmMultiSelectMulti from the MultiSelect option list. If the problem persists, you can share your VBA code or the Excel file. Regards ExcelDemy
Nice solution! Subscribed!
Thanks! We are delighted you found it helpful. Welcome to the channel!
Is it possible to change the checklist output (in the VBA code) to show them items as a visual list within the cell... separated by alt+enter (carriage return list) rather than a semicolon? Is it also possible to copy/paste the checklist that I have created into other cells? I am creating a list of schools and my checklist shows the services that each school provides. I hope this makes sense! Thank you for the amazing tutorial!
Hello @amylawson404,
You are most welcome. Thanks for your kind words. Yes, it is possible to change the checklist output. You can modify the VBA code to display the selected items as a list separated by Alt+Enter (carriage return) instead of a semicolon. For copying/pasting the checklist to other cells, you will need to adjust the code to handle the pasting functionality properly, ensuring the checklist works for each cell.
Updated VBA Code:
Sub Button_Click()
Dim buttonShape As Shape, listOption As Variant, M As Integer, N As Integer
Dim xP As String, resultStr As String
Set buttonShape = ActiveSheet.Shapes(Application.Caller)
Set checkListBox = ActiveSheet.checkList
If checkListBox.Visible = False Then
checkListBox.Visible = True
buttonShape.TextFrame2.TextRange.Characters.Text = "Tick the Passed Students"
resultStr = Range("CheckListOutput").Value
If resultStr "" Then
resultArr = Split(resultStr, Chr(10)) ' Split using carriage return
For M = checkListBox.ListCount - 1 To 0 Step -1
xP = checkListBox.List(M)
For N = 0 To UBound(resultArr)
If resultArr(N) = xP Then
checkListBox.Selected(M) = True
Exit For
End If
Next N
Next M
End If
Else
checkListBox.Visible = False
buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here"
For M = checkListBox.ListCount - 1 To 0 Step -1
If checkListBox.Selected(M) = True Then
listOption = checkListBox.List(M) & Chr(10) & listOption ' Use carriage return instead of semicolon
End If
Next M
If listOption "" Then
Range("CheckListOutput") = Left(listOption, Len(listOption) - 1)
Else
Range("CheckListOutput") = ""
End If
End If
End Sub
The items are now split and joined using Chr(10) (carriage return) instead of a semicolon (;), so they'll appear as a list when you press Alt+Enter.
The code still allows the checklist output to be copied and pasted into other cells, and the checklist will function in those cells.
Regards
ExcelDemy
@@exceldemy2006 thank you very much for taking the time to provide this information. So helpful and kind! I have subscribed. I cannot figure out how to copy/paste the checklist for each school/row. Is there another video that shows how to do this? Thank you again!
Hello @amylawson404,
You're most welcome, and thank you for subscribing! Unfortunately, we don't have a specific video on that yet.
To copy and paste the checklist for each school or row, you must ensure that each row's checklist functions independently.
Use ActiveCell in the code to tie the checklist output to the selected cell.
Sub Button_Click()
Dim buttonShape As Shape, listOption As Variant, M As Integer, N As Integer
Dim xP As String, resultStr As String
Set buttonShape = ActiveSheet.Shapes(Application.Caller)
Set checkListBox = ActiveSheet.checkList
If checkListBox.Visible = False Then
checkListBox.Visible = True
buttonShape.TextFrame2.TextRange.Characters.Text = "Tick the Passed Students"
resultStr = ActiveCell.Value ' Use active cell instead of a fixed range
If resultStr "" Then
resultArr = Split(resultStr, Chr(10)) ' Split using carriage return
For M = checkListBox.ListCount - 1 To 0 Step -1
xP = checkListBox.List(M)
For N = 0 To UBound(resultArr)
If resultArr(N) = xP Then
checkListBox.Selected(M) = True
Exit For
End If
Next N
Next M
End If
Else
checkListBox.Visible = False
buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here"
For M = checkListBox.ListCount - 1 To 0 Step -1
If checkListBox.Selected(M) = True Then
listOption = checkListBox.List(M) & Chr(10) & listOption ' Use carriage return instead of semicolon
End If
Next M
If listOption "" Then
ActiveCell.Value = Left(listOption, Len(listOption) - 1) ' Store result in the active cell
Else
ActiveCell.Value = ""
End If
End If
End Sub
Once the checklist works for one row, simply copy the cell with the button and the checklist, then paste it into other rows. The VBA will handle the dynamic references for each cell.
Regards
ExcelDemy
@@exceldemy2006 Thank you again!!
Hello @amylawson404,
You are most welcome. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
I want name to be appeared in the list but want to bring email ids mapped to these names..
Hello @vkas6608,
To display names in a drop-down checklist and map corresponding email addresses, you can modify the code to include a lookup feature. Here's a detailed approach:
Create a Helper Table:
In Excel, create a table where column A contains names, and column B contains the associated email addresses.
Modify VBA Code:
After selecting a name from the checklist, use a lookup function to fetch the email. You can use VLOOKUP or INDEX-MATCH to search for the corresponding email.
For example, assuming your table of names and emails is in the range A2:B100, you can modify the VBA code like this:
Dim email As String
email = Application.WorksheetFunction.VLookup(listOption, Range("A2:B100"), 2, False)
This will pull the email address associated with the selected name and can be displayed in a cell or message box.
Update Code for Multiple Selections:
If you have multiple names selected, the code must loop through each selected name, perform the lookup, and concatenate the results into a string or update the display accordingly.
Example for displaying emails: You can add another section in the code to display the emails next to the names or in a separate cell after the selection.
Regards
ExcelDemy
hi is it possible to make the checkbox dynamic based on a criteria? for example if I select region as south, I want to display a list of checkboxed consiting of states that are in the south region. the list should change when I select another region
Dear, thanks for your exciting question. It is definitely possible to use Excel with VBA.
Please check the following: www.exceldemy.com/wp-content/uploads/2024/03/Output-of-making-the-checkbox-dynamic-based-on-a-criteria.gif
You can download the solution workbook from: www.exceldemy.com/wp-content/uploads/2024/03/Ranjith-Ganesan-SOLVED.xlsm
If you have any questions regarding the solution, please post them within the ExcelDemy Forum.
ExcelDemy Forum: exceldemy.com/forum/
@@exceldemy2006 hi do you have a video tutorial on this? the workbook only shows the final result. thank you!
Hello @ahizalcammysh,
You can follow this tutorial to learn how to use the code. Then from the given workbook just copy the code.
Currently, we don't have the video tutorial for the workbook.
If you face any issues at any step you can mention it hear. We are here to help you.
Regards
ExcelDmy
Hi, to me the VBA code is not working and, clicking several times, it get stuck at line
resultStr = Range("CheckListOutput").Value
Range("CheckListOutput") = Mid(listOption, 1, Len(listOption) - 1)
I double checked all names are kept consistent as indicated.
Any idea why this happens?
Hello @claudiasgiarovello,
On our end, the VBA code is working perfectly. Rechecked the code to confirm it.
www.exceldemy.com/wp-content/uploads/2024/07/selecting-value-from-drop-down-list.png
Ensure you replace the curly quotation marks with straight quotation marks to avoid syntax errors in VBA. If the problem persists, double-check the name of the range "CheckListOutput" to ensure it matches exactly with what is in your Excel workbook.
1. Ensure all quotes are straight quotes (").
2. Check that "CheckListOutput" matches the actual named range in your Excel workbook.
3. Verify that "checkList" matches the name of your ActiveX listbox control.
If these steps don't resolve the issue, consider providing more details about any specific error messages or behaviors you encounter.
Regards
ExcelDemy
Fantastic, thank you
Hello @RearsilverGaming,
You are most welcome. Thanks for your appreciation. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hi, Everything went perfect. But, After I select the button and the list box shown, I wasn't able to click on it. is that a way to overcome this ? Thank you
Hello @amaliakhaerunnisa7253,
The list box may not be interactive due to Excel's control properties or settings.
Try selecting the list box, then go to the “Developer” tab, click on “Properties,” and ensure “Enabled” is set to “True".
This should allow clicking and interacting with the list box. If issues persist, saving and reopening the file can sometimes reset control functions.
Regards
ExcelDemy
@@exceldemy2006 Hi, It seems the list box were not interactive because I freeze some rows. after I unfreeze them the list box work out just fine. Thank you!
Hello @amaliakhaerunnisa7253,
You are most welcome. Thanks for your feedback. Glad to hear that the problem is solved. Keep exploring Excel with ExcelDemy!
Regards
Shamima Sultana
ExcelDemy
Declaring more than one Variable on a single line.
Like M, N as integer only declares the last variable as an integer.
the other ones will be declared as an variant.
Hello @danp6101,
We appreciate your attention to detail. It appears our video contained an oversight. Yes, your understanding is correct. In VBA, if you declare multiple variables on a single line and only specify the type for the last variable, the preceding variables will default to type Variant unless their types are explicitly specified.
The correct form for a single line will be:
Dim buttonShape As Shape, listOption As Variant, M As Integer, N As Integer
Or, explicitly specified.
Dim buttonShape As Shape
Dim listOption As Variant
Dim M As Integer
Dim N As Integer
We updated the VBA code in the article. Thanks for your feedback.
Regards
ExcelDemy
I can't find the properties of the listbox, where can I open them?
could u help me
@@dimitooo Dear, Thanks for your comments! Of course, we will help you; we are always here to overcome your difficulties.
You can add two types of list boxes to a worksheet: Form Controls and ActiveX Controls. Form Controls are more straightforward to use. On the other hand, ActiveX Controls are more versatile and offer more properties and customization options. The Property option you refer to, which allows more detailed customization, is only available in ActiveX Controls. So, in order to find the Property options by right-clicking on a list box, you must insert a list box from ActiveX Control. Please check the following: www.exceldemy.com/wp-content/uploads/2024/06/Two-types-of-list-boxes-are-used-for-a-worksheet-Form-Controls-and-ActiveX-Controls.gif
@@exceldemy2006 thank you so much :) my other question is how can I connect more Listboxes then one with one Modul or do I have to create mor codes on vba?
@@dimitooo Dear, You are very welcome. We always are here to help.
If you want to insert more than one list box, follow the same procedure outlined here. However, you must create new sub-procedures and assign them to the new shapes accordingly. It is important to remember that you must modify the VBA sub-procedures as well; you can keep them in a single module.
I have demonstrated your situation and inserted another list box in the same worksheet. Please check the following for a better understanding: www.exceldemy.com/wp-content/uploads/2024/06/Create-more-than-one-Multi-checkbox-dropdown-list.gif
You can download the Excel file: www.exceldemy.com/wp-content/uploads/2024/06/Dimi-Topalidis-SOLVED.xlsm
@@exceldemy2006
Please confirm which video provides details on how to get the code!
Hello @kim21124,
You will get it in "4:01 - Inserting VBA code into the Module". You will find the information about getting the code at 4:26 minutes.
Checkout the article which is given in the description box to get the code:
www.exceldemy.com/how-to-create-a-drop-down-checklist-in-excel/
Regards
ExcelDemy
Hi sir, could you please provide the code for clearing the checkbox from checklist. Thanks in advance.
Hello @Tan_Rainbow,
We've used an ActiveX ListBox control to create the checklist, use the following code to clear the checkbox from the checklist.
Sub ClearActiveXListBox()
Dim listBox As OLEObject
Set listBox = ActiveSheet.OLEObjects("checkList") ' Change ListBox1 to the name of your ListBox
Dim i As Long
For i = 0 To listBox.Object.ListCount - 1
listBox.Object.Selected(i) = False
Next i
End Sub
This code loops through all items in the ListBox and clears their selection.
Regards
ExcelDemy
Thank you so much sir..😊
Hello @Tan_Rainbow,
You are most welcome. Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hi, if i want to use single option. It doesnt work
Hello @jenniferalmeida5627,
In our existing VBA code, the previously stored values in the output cell are overwritten when a new selection is made. This happens during the Button_Click event when the new selection is written directly into CheckListOutput, replacing the previous value.
To preserve previous selections, we adjusted the logic so that the newly selected value is appended to the existing content rather than overwriting it. Here's an update to your code:
Sub Button_Click()
Dim buttonShape As Shape, listOption As String, M As Integer
Dim xP As String
Set buttonShape = ActiveSheet.Shapes(Application.Caller)
Set checkListBox = ActiveSheet.checkList
If checkListBox.Visible = False Then
checkListBox.Visible = True
buttonShape.TextFrame2.TextRange.Characters.Text = "Tick the Passed Students"
resultStr = Range("CheckListOutput").Value
If resultStr "" Then
resultArr = Split(resultStr, ";")
For M = checkListBox.ListCount - 1 To 0 Step -1
xP = checkListBox.List(M)
If resultArr(0) = xP Then
checkListBox.Selected(M) = True
Exit For
End If
Next M
End If
Else
checkListBox.Visible = False
buttonShape.TextFrame2.TextRange.Characters.Text = "Click Here"
For M = checkListBox.ListCount - 1 To 0 Step -1
If checkListBox.Selected(M) = True Then
listOption = checkListBox.List(M)
Exit For ' Ensure only one selection
End If
Next M
' Append the new selection if it's not already in the output
If listOption "" Then
If InStr(Range("CheckListOutput").Value, listOption) = 0 Then
If Range("CheckListOutput").Value "" Then
Range("CheckListOutput").Value = Range("CheckListOutput").Value & ";" & listOption
Else
Range("CheckListOutput").Value = listOption
End If
End If
End If
End If
End Sub
Regards
ExcelDemy
Hi there, I have a small issue. When I select the names, they don't pop up as "blue," and if I hit the interactive button, it does nothing. Then I tried double-clicking on the names; they still didn't turn blue, but using the interactive button, they inserted the names. Cant you find a solution to fix this?! Do you have any idea?
Hello @lucianabueno6178,
In our end, the code is working fine. As the names don't turn blue when selected and the interactive button doesn't work as expected, follow these steps to troubleshoot your code:
1. Ensure the code is correctly linked to the List Box and that the event handlers are properly set up.
2. Verify the List Box's properties and ensure it's set to MultiSelect.
Enter checkList in the (Name) text box.
Refer to the cells B5:B12 in the ListFillRange text box.
Choose option 1 - fmListStyleOption from the ListStyle option list.
Choose option 1 - fmMultiSelectMulti from the MultiSelect option list.
If the problem persists, you can share your VBA code or the Excel file.
Regards
ExcelDemy