Excel VBA - Listbox Values Populated Based on Tabstrip Selection + Listbox Selection Based on Checkbox Selection
I realize this might be a little to ambitious to even be posting, but here goes:
I have a userform with a tabstrip that offers each of the available departments. The tabstrip selection dictates the population of listbox items (employees), based on different named ranges (for each department, while removing any blank values in the ranges).
I've managed to write a working code such that as the tabstrip selection (deparment) changes and the listbox values (employees) change accordingly, selecting the current listbox items (employees) and clicking the "Send" command button will loop through each listbox item and send a personalized email to each of the selected employees. This is working perfectly.
My intent is to be able to allow selection of whole departments, rather than having to click a single department, select all users, complete the personalized email form fields, click send, and then have to repeat this whole process for a separate department. Example: if the email is intended to be sent out company-wide and have every employee in every department receive the same email.
One thing I've also accomplished is the ability to have checkboxes placed next to each tabstrip item (departments) act in such a way that selecting the checkbox will change the tabstrip value to the designated department and display each of the corresponding listbox employee names, already selected. This only functions for a single department, but I feel as though I'm getting closer to my end goal.
My question is whether someone can suggest a way to have each of these checkboxes function simultaneously, such that if, for example, 5 out of the 8 checkboxes are selected, those 5 corresponding tabstrips (departments) and listboxes will become simultaneously selected and therefore recognized by my "Send" command button.
I've provided a couple screenshots of the userform, and also my code below...
Dim User_Count As LongDim DeptData As Range Dim CCI As Long Dim CEM As Long Dim MAN As Long Dim OTH As Long Dim PUR As Long Dim MAR As Long Dim TAM As Long Dim WAR As LongPrivate Sub cbCCI_Click()Dim U As LongIf Me.cbCCI.Value = True Then Me.DEPT_GROUPS.Value = 0 For U = 0 To lbUsers.ListCount - 1 lbUsers.Selected(U) = cbCCI Next UEnd IfEnd SubPrivate Sub cbCEM_Click()Dim U As LongIf Me.cbCEM.Value = True Then Me.DEPT_GROUPS.Value = 1 For U = 0 To lbUsers.ListCount - 1 lbUsers.Selected(U) = cbCEM Next UEnd IfEnd SubPrivate Sub cbMAN_Click()Dim U As LongIf Me.cbMAN.Value = True Then Me.DEPT_GROUPS.Value = 2 For U = 0 To lbUsers.ListCount - 1 lbUsers.Selected(U) = cbMAN Next UEnd IfEnd SubPrivate Sub cbOTH_Click()Dim U As LongIf Me.cbOTH.Value = True Then Me.DEPT_GROUPS.Value = 3 For U = 0 To lbUsers.ListCount - 1 lbUsers.Selected(U) = cbOTH Next UEnd IfEnd SubPrivate Sub cbPUR_Click()Dim U As LongIf Me.cbPUR.Value = True Then Me.DEPT_GROUPS.Value = 4 For U = 0 To lbUsers.ListCount - 1 lbUsers.Selected(U) = cbPUR Next UEnd IfEnd SubPrivate Sub cbMAR_Click()Dim U As LongIf Me.cbMAR.Value = True Then Me.DEPT_GROUPS.Value = 5 For U = 0 To lbUsers.ListCount - 1 lbUsers.Selected(U) = cbMAR Next UEnd IfEnd SubPrivate Sub cbTAM_Click()Dim U As LongIf Me.cbTAM.Value = True Then Me.DEPT_GROUPS.Value = 6 For U = 0 To lbUsers.ListCount - 1 lbUsers.Selected(U) = cbTAM Next UEnd IfEnd SubPrivate Sub cbWAR_Click()Dim U As LongIf Me.cbWAR.Value = True Then Me.DEPT_GROUPS.Value = 7 For U = 0 To lbUsers.ListCount - 1 lbUsers.Selected(U) = cbWAR Next UEnd IfEnd SubPrivate Sub DEPT_GROUPS_Change()If Me.DEPT_GROUPS.Value = 0 Then With Me.lbUsers .RowSource = "" Set DeptData = Sheet1.Range("CCI_MDI") .List = DeptData.Cells.Value For CCI = .ListCount - 1 To 0 Step -1 If .List(CCI, 1) = "" Then .RemoveItem CCI End If Next CCI End WithEnd IfIf Me.DEPT_GROUPS.Value = 1 Then With Me.lbUsers .RowSource = "" Set DeptData = Sheet1.Range("CE_MARKETING") .List = DeptData.Cells.Value For CEM = .ListCount - 1 To 0 Step -1 If .List(CEM, 1) = "" Then .RemoveItem CEM End If Next CEM End WithEnd IfIf Me.DEPT_GROUPS.Value = 2 Then With Me.lbUsers .RowSource = "" Set DeptData = Sheet1.Range("MANAGEMENT") .List = DeptData.Cells.Value For MAN = .ListCount - 1 To 0 Step -1 If .List(MAN, 1) = "" Then .RemoveItem MAN End If Next MAN End WithEnd IfIf Me.DEPT_GROUPS.Value = 3 Then With Me.lbUsers .RowSource = "" Set DeptData = Sheet1.Range("OTHER") .List = DeptData.Cells.Value For OTH = .ListCount - 1 To 0 Step -1 If .List(OTH, 1) = "" Then .RemoveItem OTH End If Next OTH End WithEnd IfIf Me.DEPT_GROUPS.Value = 4 Then With Me.lbUsers .RowSource = "" Set DeptData = Sheet1.Range("PURCHASING") .List = DeptData.Cells.Value For PUR = .ListCount - 1 To 0 Step -1 If .List(PUR, 1) = "" Then .RemoveItem PUR End If Next PUR End WithEnd IfIf Me.DEPT_GROUPS.Value = 5 Then With Me.lbUsers .RowSource = "" Set DeptData = Sheet1.Range("SALES_MAR") .List = DeptData.Cells.Value For MAR = .ListCount - 1 To 0 Step -1 If .List(MAR, 1) = "" Then .RemoveItem MAR End If Next MAR End WithEnd IfIf Me.DEPT_GROUPS.Value = 6 Then With Me.lbUsers .RowSource = "" Set DeptData = Sheet1.Range("SALES_TAM") .List = DeptData.Cells.Value For TAM = .ListCount - 1 To 0 Step -1 If .List(TAM, 1) = "" Then .RemoveItem TAM End If Next TAM End WithEnd IfIf Me.DEPT_GROUPS.Value = 7 Then With Me.lbUsers .RowSource = "" Set DeptData = Sheet1.Range("WAREHOUSE") .List = DeptData.Cells.Value For WAR = .ListCount - 1 To 0 Step -1 If .List(WAR, 1) = "" Then .RemoveItem WAR End If Next WAR End WithEnd IfEnd SubPrivate Sub cmdSend_Click()Dim User_Name As StringDim User_Pass As StringDim User_Mail As StringDim Mail_Subject As StringDim Mail_Body As String Dim U As Integer'If Me.lbUsers.ListCount = 0 Then' MsgBox "No email recipients have been selected from the user list.", vbOKOnly + vbExclamation, "INVALID ENTRY"' Exit Sub'End IfIf Me.txtSubject.text = "" Then MsgBox "Please include an email subject heading.", vbOKOnly + vbExclamation, "INVALID ENTRY" Exit SubEnd IfIf MsgBox("Send email notifications to selected users?", vbOKCancel, "CONFIRM EMAIL") = vbCancel Then Exit SubEnd If For U = 0 To Me.lbUsers.ListCount - 1 If Me.lbUsers.Selected(U) Then User_Name = Me.lbUsers.List(U, 0) User_Pass = Me.lbUsers.List(U, 1) User_Mail = Me.lbUsers.List(U, 2) Mail_Subject = Me.txtSubject.text Mail_Body = "Hello " & User_Name & "," & Sheet1.Range("G1") & "<strong>" & User_Pass & "</strong>" & _ "<p>" & Me.txtBody.text & "<p>" & Sheet1.Range("G2") Call SendEmail(User_Mail, Mail_Subject, Mail_Body) End If Next UUnload MeEnd SubSub SendEmail(User_Mail As String, Mail_Subject As String, Mail_Body As String)Dim olApp As Outlook.ApplicationSet olApp = CreateObject("Outlook.Application") Dim olMail As Outlook.MailItem Set olMail = olApp.CreateItem(olMailItem) olMail.To = User_Mail olMail.Subject = Mail_Subject olMail.BodyFormat = olFormatHTML olMail.HTMLBody = Mail_Body olMail.SendEnd SubPrivate Sub cmdCancel_Click() Unload MeEnd Sub
Thanks for the reply, Norie. Sometimes when something is started, it's hard to change your way of thinking about things, but that's why I sought expert advice in this situation.
I'll go back to the drawing board, give this some thought, and update this thread once I've designed a reconstruction, hopefully this evening.
I'm thinking I might use toggle buttons in conjunction with a listbox in order to give the same layout and effect as what I have I've got going now. The solution feels more within my grasp now.
Norie
If you were to use togglebuttons you would need to write code for each toggle button, though the code would be pretty much identical for each one.
With a listbox you would only need one set of code to check what's been selected.
Also, with properties MultiSelect set to fmMultiSelectMulti and ListStyle to fmListStyleOption a listbox will show a checkbox for each item.
Mark Delorme
ASKER
I've been contemplating different possibilities and I've come up with the following revamp to my form. I've eliminated the tabstrip altogether, as this was making it very difficult to multi-populate my existing listbox (lbUsers) with different departments, simultaneously.
Instead, I've resorted to togglebuttons, such that whenever a togglebutton is True, the listbox is populated with the corresponding department. Another major modification I've made is that instead of using different ranges, I'm referring to a single DNR ("USERS") for all togglebuttons, which seems to be doing the trick just fine so far. Here is a snippet of my code for one togglebutton, the "CCI / MDI" department in this case:
Private Sub tbCCI_Click()If Me.tbCCI.Value = True Then With Me.lbUsers .RowSource = "" Set DeptData = Sheet1.Range("USERS") .List = DeptData.Cells.Value For U = .ListCount - 1 To 0 Step -1 If Not .List(U, 3) = "CCI / MDI" Then .RemoveItem U End If Next U End WithElse With Me.lbUsers .RowSource = "" Set DeptData = Sheet1.Range("USERS") .List = DeptData.Cells.Value For U = .ListCount - 1 To 0 Step -1 If .List(U, 3) = "CCI / MDI" Then .RemoveItem U End If Next U End WithEnd IfEnd Sub
What this achieves is that whenever the "CCI / MDI" togglebutton value is True, only the employees in the DNR with this department designation are listed in the listbox, and whenever the togglebutton value is False, then every other employee except those in this department are listed, as shown below:
Where I remain perplexed is with how to write the code such that if multiple togglebuttons are selected/deselected simultaneously, then the corresponding listbox items are added/removed (i.e. visible/invisible) simultaneously as well.
The reason I want to achieve this goal is because if the email only needs to go out to certain departments, then by selecting the designated department togglebuttons simultaneously, followed by clicking a simple checkbox object that would select all the currently visible listbox items, the process is made easy to the user and the email goes out to all the appropriate employees without missing anyone.
If you used a listbox for the departments it should be straightforward to handle the selection of multiple/all departments.
For one thing you would only be dealing with a single control instead of multiple toggle buttons.
Mark Delorme
ASKER
The reason for the togglebuttons controlling the listbox of employees is for easy visual confirmation of the employees in each department, but mainly also that selective choices can be differentiated.
If I can't crack a code that will enable what I'm hoping for, I'll need to settle for selecting only 1 department at a time or selecting the entire company listing to be visible within the listbox. By throwing in some extra lines that disables/enables togglebuttons under certain conditions, e.g. only 1 can be selected at a time, this will keep user operation relatively simple and straightforward.
If anyone can provide any further thoughts or suggestions, it would be much appreciated.
Norie
Have you tried using a listbox for the departments?
It would be easy to set up, populate and work with.
The only possible disadvantage might be that it might not be as aesthetically pleasing as command/toggle buttons.
If you could attach a sample workbook without any sensitive data I could post code for setting up up and working with it a listbox for the departments.
Thanks for your assistance, Norie. Please see the attached workbook. You'll notice I have 3 "email" userforms. The two most current are "frmEmail_TABSTRIP" and "frmEmail_TOGGLES", which employ the tabstrip department selection and the togglebuttons department selection, respectively.
If I am to settle for one over the other, I'm currently more a fan of the tabstrip version, although if there were someway to make the togglebutton version work so that multiple departments could be displayed in the listbox simultaneously, this would be the exact solution I'm seeking.
Your help is very much appreciated!
NOTE: The filename seems to be automatically appended with ".xlsx", which will obviously need to be removed to open the file. EMAIL_TEST_HTML.xlsm
I'll go back to the drawing board, give this some thought, and update this thread once I've designed a reconstruction, hopefully this evening.
I'm thinking I might use toggle buttons in conjunction with a listbox in order to give the same layout and effect as what I have I've got going now. The solution feels more within my grasp now.