Excel VBA- Combobox, Load data from sheet

By Ajeet Kumar

EXCEL VBA INPUT FORM WITH COMBO BOX

Option Explicit

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdOK_Click()
    Dim lngRow As Long
    Dim CNTRL As Control
    
    On Error GoTo labelE
    
    Sheet1.Range("A1:F1").Value = Array("Region", "Brand", "Color", "Price", "Units", "Dates")
    Sheet1.Range("A1:F1").Font.Bold = True
    lngRow = Sheet1.Range("A1").CurrentRegion.Rows.Count + 1
    ''Check if a text box is empty?
    For Each CNTRL In Me.Controls
        If TypeName(CNTRL) = "TextBox" Then
            If CNTRL.Text = "" Then
                MsgBox Right(CNTRL.Name, Len(CNTRL.Name) - 7) & " is mandatory field.", vbInformation
                CNTRL.SetFocus
                GoTo labelE
            End If
'            Debug.Print CNTRL.Text
        End If
    Next
    
    If Not IsNumeric(TextBoxPrice.Value) Then
        MsgBox "Please enter a valid price", vbInformation
        GoTo labelE
    End If
    
    If IsDate(TextBoxDate.Value) Then
        If Year(TextBoxDate.Value) < 2000 Then
            MsgBox "Date must be after 2000"
                GoTo labelE
        End If
    Else
        MsgBox "Please enter a valid date", vbInformation
        GoTo labelE
    End If
    
'    '''Check if the Date is after year 2000
'    If Year(TextBoxDate.Value) < 2000 Then
'        MsgBox "Date must be after 2000"
'            GoTo labelE
'    End If
    
    ''Pass data from text boxes to cells
    With Sheet1
        .Range("A" & lngRow) = ComboBoxRegion.Value
        .Range("B" & lngRow) = ComboBoxBrand.Value
        .Range("C" & lngRow) = ComboBoxColor.Value
        .Range("D" & lngRow) = TextBoxPrice.Value
        .Range("E" & lngRow) = TextBoxUnits.Value
        .Range("F" & lngRow) = TextBoxDate.Value
        .Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
    End With
    
labelE:
If Err.Number <> 0 And Err.Number = 13 Then
    MsgBox "Please enter correct date.", vbInformation
ElseIf Err.Number <> 0 Then
    MsgBox "Error Number " & Err.Number & "Error Description " & Err.Description
End If
End Sub

Private Sub UserForm_Initialize()
    With Me.ComboBoxRegion
        .AddItem "East"
        .AddItem "North"
        .AddItem "South"
        .AddItem "West"
        .Text = .List(0)
    End With
    With Me.ComboBoxBrand
        .AddItem "Nokia"
        .AddItem "Sony"
        .AddItem "Lava"
        .AddItem "Intex"
        .AddItem "Samsung"
        .AddItem "Spice"
        .Text = .List(0)

    End With
    With Me.ComboBoxColor
        .AddItem "Red"
        .AddItem "White"
        .AddItem "Cream"
        .AddItem "Sunset"
        .AddItem "Gray"
        .Text = .List(0)
    End With

End Sub

Sub pShowUF()

    UserFormX.Show

End Sub