Snasui Excel Forum

Code: Select all

Private Sub UserForm_Initialize()
    Dim ra As Range, r As Range
    With Worksheets("Sheet1")
        Me.ComboBox6.RowSource = ""
        Set ra = .Range("c3", .Range("c" & .Rows.Count).End(xlUp))
        For Each r In ra
            Me.ComboBox6.AddItem r.Value
        Next r
    End With
End Sub

Code: Select all

'Other code
    If Application.CountIf(Range("c:c"), ComboBox6.Text) > 0 Then
        irow = Application.Match(Val(ComboBox6.Text), Range("c:c"), 0)
    Else
        irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If
'Other code

Code: Select all

Private Sub CommandButton1_Click()
    Dim irow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("sheet1")

    If Application.CountIf(Range("c:c"), ComboBox6.Text) > 0 Then
        irow = Application.Match(ComboBox6.Text, Range("c:c"), 0)
    Else
        irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If
    ws.Cells(irow, 1).Value = Me.ComboBox1.Value
    ws.Cells(irow, 2).Value = Me.ComboBox2.Value
    ws.Cells(irow, 3).Value = Me.ComboBox6.Value
    ws.Cells(irow, 4).Value = Me.TextBox2.Value
    ws.Cells(irow, 5).Value = Me.TextBox3.Value
    ws.Cells(irow, 6).Value = Me.ComboBox3.Value
    ws.Cells(irow, 7).Value = Me.ComboBox4.Value
    ws.Cells(irow, 8).Value = Me.ComboBox5.Value
    ws.Cells(irow, 9).Value = Me.TextBox4.Value
    ws.Cells(irow, 10).Value = Me.TextBox5.Value
    ws.Cells(irow, 11).Value = Me.TextBox6.Value
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim irow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("sheet1")

    If Application.CountIf(Range("c:c"), ComboBox6.Text) > 0 Then
        irow = Application.Match(ComboBox6.Text, Range("c:c"), 0)
    Else
        irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    End If
    ws.Cells(irow, 1).Value = Me.ComboBox1.Value
    ws.Cells(irow, 2).Value = Me.ComboBox2.Value
    ws.Cells(irow, 3).Value = Me.ComboBox6.Value
    ws.Cells(irow, 4).Value = Me.TextBox2.Value
    ws.Cells(irow, 5).Value = Me.TextBox3.Value
    ws.Cells(irow, 6).Value = Me.ComboBox3.Value
    ws.Cells(irow, 7).Value = Me.ComboBox4.Value
    ws.Cells(irow, 8).Value = Me.ComboBox5.Value
    ws.Cells(irow, 9).Value = Me.TextBox4.Value
    ws.Cells(irow, 10).Value = Me.TextBox5.Value
    ws.Cells(irow, 11).Value = Me.TextBox6.Value
    End If
End Sub

Code: Select all

Private Sub CommandButton1_Click()
Dim y As Long
Dim x As Integer
     x = MsgBox("Do you want to update?", vbOKCancel, "Progam")
 If x = vbOK Then
    y = Application.Match(Me.ComboBox6.Text, Range("C3:C20"), 0)
  Cells(y, 1) = ComboBox1.Text
    Cells(y, 2) = ComboBox2.Text
    Cells(y, 3) = ComboBox6.Text
    Cells(y, 4) = TextBox2.Text
    Cells(y, 5) = TextBox3.Text
    Cells(y, 6) = ComboBox3.Text
    Cells(y, 7) = ComboBox4.Text
    Cells(y, 8) = ComboBox5.Text
    Cells(y, 9) = Application.Text(TextBox4.Text, "dd/mm/yyyy")
    Cells(y, 10) = Application.Text(TextBox5.Text, "dd/mm/yyyy")
    Cells(y, 11) = Application.Text(TextBox6.Text, "dd/mm/yyyy")
    Else
         Dim q, p As Long
        q = Application.WorksheetFunction.CountA(Sheet1.Range("A2:Z2")) - 1
        For p = 1 To q
        Me("textbox" & p).Value = Application.WorksheetFunction.VLookup(ComboBox6.Value, Sheet1.Range("A3:K20"), p + 1, 0)
        Next p
    End If
End Sub

Code: Select all

Private Sub ComboBox6_Change()
    Dim q As Integer, p As Integer
    Dim ra As Range, r As Range
    With Worksheets("Sheet1")
        Set ra = .Range("c3", .Range("c" & .Rows.Count).End(xlUp))
        If Application.CountIfs(ra, Me.ComboBox6.Value) = 0 Then Exit Sub
        q = Application.Match(Val(Me.ComboBox6.Value), ra, 0)
    End With
    With Me
        .TextBox2.Value = ra(q).Offset(0, 1).Value
        .TextBox3.Value = ra(q).Offset(0, 2).Value
        .ComboBox3.Value = ra(q).Offset(0, 3).Value
        .ComboBox4.Value = ra(q).Offset(0, 4).Value
        .ComboBox5.Value = ra(q).Offset(0, 5).Value
        .TextBox4.Value = ra(q).Offset(0, 6).Value
        .TextBox5.Value = ra(q).Offset(0, 7).Value
        .TextBox6.Value = ra(q).Offset(0, 8).Value
         .ComboBox1.Value = ra(q).Offset(0, -2).Value
          .ComboBox2.Value = ra(q).Offset(0, -1).Value
    End With
        UserForm2.TextBox4.Text = Format(UserForm2.TextBox4.Text, "dd/mm/yy")
        UserForm2.TextBox5.Text = Format(UserForm2.TextBox5.Text, "dd/mm/yy")
        UserForm2.TextBox6.Text = Format(UserForm2.TextBox6.Text, "dd/mm/yy")
End Sub

Code: Select all

Private Sub ComboBox6_Change()
    Dim q As Integer, p As Integer
    Dim ra As Range, r As Range
    With Worksheets("Sheet1")
        Set ra = .Range("c3", .Range("c" & .Rows.Count).End(xlUp))
        If Application.CountIfs(ra, Me.ComboBox6.Value) = 0 Then Exit Sub
        q = Application.Match(Val(Me.ComboBox6.Value), ra, 0)
    End With
    With Me
        .TextBox2.Value = ra(q).Offset(0, 1).Value
        .TextBox3.Value = ra(q).Offset(0, 2).Value
        'Other code
    End With
End Sub

Code: Select all

Private Sub ComboBox6_Change()
    Dim q, p As Integer
    q = Application.WorksheetFunction.CountA(Sheet1.Range("A2:Z2")) - 1
    For p = 1 To q
        UserForm2("textbox" & p).Value = _
        Application.WorksheetFunction.VLookup(ComboBox6.Value, Sheet1.Range("A3:K20"), p + 1, 0)
    Next p
    UserForm2.TextBox4.Text = Format(UserForm2.TextBox4.Text, "dd-mmm-yy")
    UserForm2.TextBox5.Text = Format(UserForm2.TextBox5.Text, "dd-mmm-yy")
    UserForm2.TextBox6.Text = Format(UserForm2.TextBox6.Text, "dd-mmm-yy")
    
End Sub
Private Sub CommandButton1_Click()
Dim y As Long
Dim x As Integer
     x = MsgBox("Do you want to update?", vbOKCancel, "Progam")
 If x = vbOK Then
    y = Application.Match(Me.ComboBox6.Text, Range("C3:C20"), 0)
  Cells(y, 1) = ComboBox1.Text
    Cells(y, 2) = ComboBox2.Text
    Cells(y, 3) = ComboBox6.Text
    Cells(y, 4) = TextBox2.Text
    Cells(y, 5) = TextBox3.Text
    Cells(y, 6) = ComboBox3.Text
    Cells(y, 7) = ComboBox4.Text
    Cells(y, 8) = ComboBox5.Text
    Cells(y, 9) = Application.Text(TextBox4.Text, "dd/mm/yyyy")
    Cells(y, 10) = Application.Text(TextBox5.Text, "dd/mm/yyyy")
    Cells(y, 11) = Application.Text(TextBox6.Text, "dd/mm/yyyy")
    Else
         Dim q, p As Long
        q = Application.WorksheetFunction.CountA(Sheet1.Range("A2:Z2")) - 1
        For p = 1 To q
        Me("textbox" & p).Value = Application.WorksheetFunction.VLookup(ComboBox6.Value, Sheet1.Range("A3:K20"), p + 1, 0)
        Next p
    End If
End Sub