Excel Forum

Code: Select all

if application.countif(range("a:a"),textbox1.text) > 0 then
   msgbox "abcdef"
   exit sub
end if

Code: Select all

Private Sub CommandButton1_Click()
    Dim r As Long
    If Application.CountIf(Range("a:a"), TextBox1.Text) > 0 Then
        MsgBox "abcdef"
        Exit Sub
    End If
    ' Other code
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim r As Long
    With Sheets("data")
        If Application.CountIf(.Range("a:a"), TextBox1.Text) > 0 Then
            MsgBox "มีเลขประจำตัวแล้ว!!!"
            Exit Sub
        End If
        ' Other code
        If TextBox2.Text <> "" And Application.CountIf(.Range("b:b"), TextBox2.Text) > 0 Then
            MsgBox "ชื่อนักเรียนซ้ำ!!!"
            Exit Sub
        End If
        r = .Range("a" & Rows.Count).End(xlUp).Row + 1
        .Cells(r, 1) = TextBox1.Text
        .Cells(r, 2) = TextBox2.Text
        .Cells(r, 3) = ComboBox1.Text
      End With
End Sub

Code: Select all

Private Sub ComboBox1_Change()
    On Error Resume Next
    MyComboBox1 = ComboBox1.Text
    Sheets("Data").Select
    Range("a2").Select
    Do While ActiveCell.Row <= Range("a" & Rows.Count).End(xlUp).Row
        If CInt(MyComboBox1) = ActiveCell.Value Then
            TextBox1.Text = ActiveCell.Offset(0, 1).Value
            TextBox2.Text = ActiveCell.Offset(0, 2).Value
            Exit Sub
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Code: Select all

Private Sub ComboBox1_Change()

On Error Resume Next
Set myRange = Worksheets("Data").Range("A2:D30")
ComboBox1.Value = Application.WorksheetFunction.VLookup(CLng(ComboBox1), myRange, 1, False)
TextBox1.Value = Application.WorksheetFunction.VLookup(CLng(ComboBox1), myRange, 2, False)
TextBox2.Value = Application.WorksheetFunction.VLookup(CLng(ComboBox1), myRange, 3, False)

If Err <> 0 Then
MsgBox "äÁèÁÕÃÒ¡Ò÷Õè·èÒ¹àÅ×Í¡", vbQuestion
TextBox1.Text = ""
TextBox2.Text = ""
Exit Sub
End If
End Sub

Private Sub CommandButton1_Click()

Dim lastRow As Object

          Set lastRow = Data.Range("b65536").End(xlUp)

          lastRow.Offset(1, 0).Value = ComboBox1.Text
          lastRow.Offset(1, 1).Value = TextBox1.Text
          lastRow.Offset(1, 2).Value = TextBox2.Text
        

          MsgBox "One record written to Sheet1" ' "µéͧ¡ÒÃà¡çº¢éÍÁÙÅËÃ×ÍäÁè", vbOKCancel

          response = MsgBox("Do you want to enter another record?", _
              vbYesNo)  'MsgBox("¨Ð¡ÃÍ¡¢éÍÁÙÅÍÕ¡ËÃ×ÍäÁè?", _
              vbYesNo)

          If response = vbYes Then
              ComboBox1.Text = ""
              TextBox1.Text = ""
              TextBox2.Text = ""
              
              
              ComboBox1.SetFocus

          Else
              Unload Me
          End If

End Sub