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...
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 Long
Dim 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 Long
Private Sub cbCCI_Click()
Dim U As Long
If Me.cbCCI.Value = True Then
Me.DEPT_GROUPS.Value = 0
For U = 0 To lbUsers.ListCount - 1
lbUsers.Selected(U) = cbCCI
Next U
End If
End Sub
Private Sub cbCEM_Click()
Dim U As Long
If Me.cbCEM.Value = True Then
Me.DEPT_GROUPS.Value = 1
For U = 0 To lbUsers.ListCount - 1
lbUsers.Selected(U) = cbCEM
Next U
End If
End Sub
Private Sub cbMAN_Click()
Dim U As Long
If Me.cbMAN.Value = True Then
Me.DEPT_GROUPS.Value = 2
For U = 0 To lbUsers.ListCount - 1
lbUsers.Selected(U) = cbMAN
Next U
End If
End Sub
Private Sub cbOTH_Click()
Dim U As Long
If Me.cbOTH.Value = True Then
Me.DEPT_GROUPS.Value = 3
For U = 0 To lbUsers.ListCount - 1
lbUsers.Selected(U) = cbOTH
Next U
End If
End Sub
Private Sub cbPUR_Click()
Dim U As Long
If Me.cbPUR.Value = True Then
Me.DEPT_GROUPS.Value = 4
For U = 0 To lbUsers.ListCount - 1
lbUsers.Selected(U) = cbPUR
Next U
End If
End Sub
Private Sub cbMAR_Click()
Dim U As Long
If Me.cbMAR.Value = True Then
Me.DEPT_GROUPS.Value = 5
For U = 0 To lbUsers.ListCount - 1
lbUsers.Selected(U) = cbMAR
Next U
End If
End Sub
Private Sub cbTAM_Click()
Dim U As Long
If Me.cbTAM.Value = True Then
Me.DEPT_GROUPS.Value = 6
For U = 0 To lbUsers.ListCount - 1
lbUsers.Selected(U) = cbTAM
Next U
End If
End Sub
Private Sub cbWAR_Click()
Dim U As Long
If Me.cbWAR.Value = True Then
Me.DEPT_GROUPS.Value = 7
For U = 0 To lbUsers.ListCount - 1
lbUsers.Selected(U) = cbWAR
Next U
End If
End Sub
Private 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 With
End If
If 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 With
End If
If 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 With
End If
If 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 With
End If
If 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 With
End If
If 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 With
End If
If 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 With
End If
If 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 With
End If
End Sub
Private Sub cmdSend_Click()
Dim User_Name As String
Dim User_Pass As String
Dim User_Mail As String
Dim Mail_Subject As String
Dim 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 If
If Me.txtSubject.text = "" Then
MsgBox "Please include an email subject heading.", vbOKOnly + vbExclamation, "INVALID ENTRY"
Exit Sub
End If
If MsgBox("Send email notifications to selected users?", vbOKCancel, "CONFIRM EMAIL") = vbCancel Then
Exit Sub
End 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 U
Unload Me
End Sub
Sub SendEmail(User_Mail As String, Mail_Subject As String, Mail_Body As String)
Dim olApp As Outlook.Application
Set 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.Send
End Sub
Private Sub cmdCancel_Click()
Unload Me
End SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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:
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.
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 With
Else
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 With
End If
End SubWhat 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.
For one thing you would only be dealing with a single control instead of multiple toggle buttons.
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.
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.
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.
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.
ASKER
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
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
ASKER
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.