Solved

Excel VBA - Listbox Values Populated Based on Tabstrip Selection + Listbox Selection Based on Checkbox Selection

Posted on 2016-07-25
10
46 Views
Last Modified: 2016-08-31
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...

Email_Form1.png
Email_Form2.png
Email_Form3.png
Email_Form4.png
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 Sub

Open in new window

0
Comment
Question by:Mark Delorme
  • 4
  • 4
10 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 41729278
Why are you using a tabstrip for the departments?

Wouldn't it be simpler to use a listbox?

That listbox could easily be populated with the departments and be set up to allow multiple departments be selected simultaneously.

It could even have an 'All' option to allow all departments to be selected/included.
1
 

Author Comment

by:Mark Delorme
ID: 41729340
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.
0
 
LVL 33

Expert Comment

by:Norie
ID: 41729442
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.
0
 

Author Comment

by:Mark Delorme
ID: 41730675
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 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 Sub

Open in new window


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:

The only "CCI / MDI" department employee, "J", is visible.
Every other employee is visible, except "J".
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Expert Comment

by:Norie
ID: 41730790
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.
0
 

Author Comment

by:Mark Delorme
ID: 41731032
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.
0
 
LVL 33

Expert Comment

by:Norie
ID: 41731309
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.
0
 

Author Comment

by:Mark Delorme
ID: 41731390
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
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now