Hi,

I am new to VBA and I am trying to manipulate a form that someone else created within Excel. The form has 3 ActiveX ComboBoxes (UnitComboBox1, TypeComboBox1, TaskComboBox1) among other things (checking rows for data, submitting form to email list, approve, disapprove buttons that goto different email lists).

There are a few things that I would like to do with my ActiveX comboboxes
1.) First when a user selects from the UnitComboBox1, based on the Unit they select, it populates the TypeComboBox1, and then again when a user selects the TypeComboBox1, based on the Type they select, it populates the TaskComboBox1.
2.) Depending on the combinations that the user selects of the 3 combo boxes, it needs to send the form via email to a select group of people (for which I have created an array).

I know this is possible, but have not been able to find an example of how to do 3 combo boxes, using SelectCase and being that I am new to all of this, I don't even know where to begin to VBA code this using Active X.

There are also different email lists (depending on the selections in the comboboxes) that this form will get sent to.

Hope I am making sense....ANY help/guidance would be GREATLY appreciated.

I have included the Excel sheet VB below. Thank you!

VB@N3wb|3


Private Sub Calendar2_Click()
[G4] = Calendar2.Value + Time
Me.Calendar2.Visible = False
Me.DateCommandButton4.Visible = True
End Sub

Private Sub cmdSend_Click()
Dim Recip As Variant
Dim c As Range
Dim r As Integer
Dim c1 As Range
Dim r1 As Integer
Dim c2 As Range
Dim r2 As Integer
Dim RowComplete As Boolean

r2 = 0

If [E4] <> "" Then

    If [G4] <> "" Then
    
        r1 = 12
        
            For Each c2 In Range("a14:g19")
                If c2 = "" Then
                    r2 = r2 + 1
                End If
            Next c2
            
            If r2 = 42 Then
                MsgBox "You must have at least 1 complete row filled in before sending this form."
                Exit Sub
            Else
            
                For Each c1 In Range("e14:e19")
                    r = 0
                        For Each c In Range("a" & r1 & ":" & "g" & r1)
                            If c = "" Then
                                r = r + 1
                            End If
                        Next c
                        If r <> 6 Then
                        End If
                        r1 = r1 + 1
                        RowComplete = True
                Next c1
            
                If RowComplete = True Then
'********************************************************************************************************************************************************************
'*****************************************TEST Address***************************************************************************************************************
'                            Recip = Array("anyemail@anyaddy.com")
'*****************************************End TEST Address***********************************************************************************************************
'********************************************************************************************************************************************************************
                    
                    Recip = Array("emails@email.com", "email@email.com")

                                   ActiveWorkbook.SendMail Recipients:=Recip

                                  
                End If
         End If
    
    Else
        MsgBox "You must enter in the date for this request.  Click the Date button for a pop up calendar."
    End If
Else
    MsgBox " You must choose a Requesting Unit/Activity from the dropdown list provided"
End If
                   
End Sub

Private Sub DateCommandButton4_Click()
Me.Calendar2.Visible = True
Me.Calendar2.Value = Date
End Sub

Private Sub cmdApproved_Click()

Dim Password As String, ComparePW As String

Password = "rev22jan07"

ComparePW = InputBox("Enter the Password")

If Len(ComparePW) = 0 Then
    Exit Sub
ElseIf ComparePW = Password Then
    Me.lblApproved.Visible = True
    DeleteButtons
End If

End Sub
Private Sub cmdDisapproved_Click()

Dim Password As String, ComparePW As String

Password = "rev22jan07"

ComparePW = InputBox("Enter the Password")

If Len(ComparePW) = 0 Then
    Exit Sub
ElseIf ComparePW = Password Then
    Me.lblDisapproved.Visible = True
    DeleteButtons
End If

End Sub
Private Sub lblDisapproved_Click()

End Sub

Private Sub UnitComboBox1_Change()

End Sub

Private Sub TypeComboBox1_Change()

End Sub

Private Sub TaskComboBox1_Change()

End Sub

Private Sub worksheet_selectionChange(ByVal target As Range)

    If Me.Calendar2.Visible = True Then
        Me.Calendar2.Visible = False
        Me.DateCommandButton4.Visible = False
    End If

End Sub

Sub Mail_Workbook()

Dim Recip As Variant
Recip = Array("emails@email.com", "email@email.com")

ActiveWorkbook.SendMail Recipients:=Recip
End Sub

Function DeleteButtons()

Dim Recip As Variant
Dim c As Range
Dim r As Integer
Dim c1 As Range
Dim r1 As Integer
Dim c2 As Range
Dim r2 As Integer
Dim RowComplete As Boolean
Dim vApproval As String
r2 = 0

If [E4] <> "" Then
    If [G4] <> "" Then
        r1 = 12
            For Each c2 In Range("a14:G19")
                If c2 = "" Then
                    r2 = r2 + 1
                End If
            Next c2
            If r2 = 42 Then
            MsgBox "You must have at least 1 complete row filled in before sending this form."
                If Sheet1.lblApproved.Visible = True Then
                    Sheet1.lblApproved.Visible = False
                ElseIf Sheet1.lblDisapproved.Visible = True Then
                    Sheet1.lblDisapproved.Visible = False
                End If
            Exit Function
       
    Else
        For Each c1 In Range("F14:F19")
        
            r = 0
            For Each c In Range("a" & r1 & ":" & "G" & r1)
                If c = "" Then
                    r = r + 1
                End If
            Next c
        
        If r <> 7 Then
            If r <> 0 Then
                MsgBox "This form must be filled out in its entirety before being sent.  Please complete the missing data in row " & r1 & " or delete the data already entered"
                    If Sheet1.lblApproved.Visible = True Then
                        Sheet1.lblApproved.Visible = False
                    ElseIf Sheet1.lblDisapproved.Visible = True Then
                        Sheet1.lblDisapproved.Visible = False
                    End If
                Exit Function
            End If
        End If
            r1 = r1 + 1
            RowComplete = True
        Next c1
        If RowComplete = True Then
If Sheet1.lblApproved.Visible = True Then
    vApproval = "APPROVED"
ElseIf Sheet1.lblDisapproved.Visible = True Then
    vApproval = "DISAPPROVED"
End If


Sheet1.cmdDisapproved.Visible = False
Sheet1.cmdApproved.Visible = False

                        ActiveWorkbook.SaveAs vApproval & " - Request " & Sheet1.Range("E4") & "-" & Format(Sheet1.Range("G4"), "dd mmm yyyy h:mm AM/PM")
                        
'********************************************************************************************************************************************************************
'*****************************************TEST Address***************************************************************************************************************
'                        Recip = Array("anyemail@anyemail.com")
'*****************************************End TEST Address***********************************************************************************************************
'********************************************************************************************************************************************************************

                Recip = Array("Recip = Array("emails@email.com", "email@email.com")")
)
                              





                        ActiveWorkbook.SendMail Recipients:=Recip
                        
                        
        End If
End If
    Else
        MsgBox "You must enter in the date for this request.  Click the Date button for a pop up calendar"
            If Sheet1.lblApproved.Visible = True Then
                Sheet1.lblApproved.Visible = False
            ElseIf Sheet1.lblDisapproved.Visible = True Then
                Sheet1.lblDisapproved.Visible = False
            End If
    End If
Else
    MsgBox "You must choose a Requesting Unit/Activity from the dropdown list provided"
            If Sheet1.lblApproved.Visible = True Then
                Sheet1.lblApproved.Visible = False
            ElseIf Sheet1.lblDisapproved.Visible = True Then
                Sheet1.lblDisapproved.Visible = False
            End If
End If
End Function