How to save Checkbox values in Userform

[thardin]

So I have a listbox in my userform that consists of a list of email addresses of people on my team. The email addresses are listed on a worksheet and I am using the "Row Source" property, and each email has a checkbox beside it. I have it setup where if the email address is checked, the email address is added to a string to create a CC string for an outlook email that is sent on a daily basis.
I am trying to make this handy and convenient in the case emails need to be changed, such as if someone leaves or joins the team.

However, every time I exit the userform and open it on a new day, the checked boxes are gone. How do I make it so the checked boxes are always checked and the string is the same, unless it needs to be changed?

 

[6StringJazzer]

You will have to whip up custom code to do this, and you will have to store the checked/unchecked status of each email someplace where it will be persistent. You should add another column to the worksheet that lists the email addresses. This column will contain TRUE or FALSE depending on whether the user has checked the box for that email address. Your code will have to update these every time the user updates the listbox. You can do this each time the listbox is clicked, or you can do it when the user clicks Submit, if you have some kind of Submit button. It's hard to give you a more detailed answer without information about your file.

If you want actual code you can plug in please provide more detail:
What is the name of the worksheet and the range that contains the emails?
What is the name of the ListBox?
Do you have an OK or Submit button? If so, what is its name?
Show the code where you generate the string for CC. Show the entire sub that contains this code.

 

[thardin]

You will have to whip up custom code to do this, and you will have to store the checked/unchecked status of each email someplace where it will be persistent. You should add another column to the worksheet that lists the email addresses. This column will contain TRUE or FALSE depending on whether the user has checked the box for that email address. Your code will have to update these every time the user updates the listbox. You can do this each time the listbox is clicked, or you can do it when the user clicks Submit, if you have some kind of Submit button. It's hard to give you a more detailed answer without information about your file.

If you want actual code you can plug in please provide more detail:
What is the name of the worksheet and the range that contains the emails?
What is the name of the ListBox?
Do you have an OK or Submit button? If so, what is its name?
Show the code where you generate the string for CC. Show the entire sub that contains this code.

Hello Jeff, Thank you for your reply. Here is the screenshot of the details to your questions. I have 2 listboxes. 1 for each type of email. I don't have a "submit" button yet, but I can create one.

Also, here is the code you requested:

Function UBT_SelectedEmails() As String
   Dim i As Integer
  
   For i = LBound(Email_lb.List) To UBound(Email_lb.List)
      If Email_lb.Selected(i) Then
         UBT_SelectedEmails = UBT_SelectedEmails & Email_lb.List(i) & ";"
      End If
   Next i
  
   If UBT_SelectedEmails <> "" Then
      UBT_SelectedEmails = Left(UBT_SelectedEmails, Len(UBT_SelectedEmails) - 1)
   Else
      MsgBox "select an email"
   End If
  
  
   Report_529.Selected_tb = UBT_SelectedEmails
  
End Function


Private Sub Email_lb_Change()
   UBT_SelectedEmails
End Sub

Function AFirm_SelectedEmails() As String
   Dim i As Integer
  
   For i = LBound(Email2_lb.List) To UBound(Email2_lb.List)
      If Email2_lb.Selected(i) Then
         AFirm_SelectedEmails = AFirm_SelectedEmails & Email_lb.List(i) & ";"
      End If
   Next i
  
   If AFirm_SelectedEmails <> "" Then
      AFirm_SelectedEmails = Left(AFirm_SelectedEmails, Len(AFirm_SelectedEmails) - 1)
   Else
      MsgBox "select an email"
   End If
  
   Report_529.Selected2_tb = AFirm_SelectedEmails
  
End Function


Private Sub Email2_lb_Change()
   AFirm_SelectedEmails
End Sub