CODEWITHSUNDEEP
excelvba
Muhammad Khurrum Butt
Muhammad Khurrum Butt

Reputation: 137

Excel VBA Combo Box value condition

I am trying to save data to Excel sheet combo box text box and date picker if Combo box value is A Data from textbox and date should be saved in Column A B and C every time Combo box value is A it should save in next empty row in Col A B and C If Combo Box value is B Text box value and date should go to column E, F, and G 

 Private Sub CmdButtSave_Click()
     Worksheets("Sheet1").Activate
         If CBitem.Value = "A" Then
             Range("A2").Value = TBox.Value
             Range("B2").Value =Me.DTPicker.Value
             Range("C2").Value = TBquan.Value
         Else
             Range("A1").End(xlDown).Offset(1, 0).Value = TBox.Value
             Range("B1").End(xlDown).Offset(1, 0).Value = Me.DTPicker.Value
             Range("C1").End(xlDown).Offset(1, 0).Value = TBquan.Value
         End If 
         If CBitem.value = "B" Then
 End sub

Upvotes: 0

Views: 183

Answers (2)

Muhammad Khurrum Butt
Muhammad Khurrum Butt

Reputation: 137

I make function to get next empty row that works well

  Private Sub CmdButtSave_Click()

     Dim rw As Long

     With Worksheets("Sheet1")

     If CBitem.value = "A" Then

     rw = GetLastRow("A")
     .Range("A" & rw).value = TBox.value
     .Range("B" & rw).value = Format(Me.DTPicker.value, "long date")
     .Range("C" & rw).value = TBquan.value


     End If

       If CBitem.value = "B" Then

     rw = GetLastRow("E")
     .Range("E" & rw).value = TBox.value
     .Range("F" & rw).value = Format(Me.DTPicker.value, "long date")
     .Range("G" & rw).value = TBquan.value

       End If

      End With

     End Sub
    Function GetLastRow(ByVal col As String) As Long
   With Worksheets("Sheet1")
    GetLastRow = .Range(col & .Rows.Count).End(xlUp).Row + 1
   End With
  End Function

Upvotes: 0

TheJeebo
TheJeebo

Reputation: 166

In this case that would keep placing your info into row 2 and the else would be catching if it was "B". You could try handling both scenarios back to back like this:

Private Sub CmdButtSave_Click()
Worksheets("Sheet1").Activate

If CBitem.Value = "A" Then
    Range("A1").End(xlDown).Offset(1, 0).Value = TBox.Value
    Range("B1").End(xlDown).Offset(1, 0).Value = Me.DTPicker.Value
    Range("C1").End(xlDown).Offset(1, 0).Value = TBquan.Value
ElseIf CBitem.Value = "B" Then
    Range("D1").End(xlDown).Offset(1, 0).Value = TBox.Value
    Range("E1").End(xlDown).Offset(1, 0).Value = Me.DTPicker.Value
    Range("F1").End(xlDown).Offset(1, 0).Value = TBquan.Value
End If

End Sub

Upvotes: 1

Related Questions