Excel Forum

Code: Select all

Private Sub id_txtbox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim db_student As Worksheet
Dim sdLR As Long, y As Long, x As Long
Set db_student = ThisWorkbook.Sheets("db_sheet ")
If db_student.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
sdLR = 2
Else
sdLR = Application.WorksheetFunction.Max(1, .db_student.Cells(Rows.Count, 1).End(xlUp).Row)
End If
For x = 2 To sdLR
If UCase(db_student.Cells(x, 1)) = id_txtbox.Text Then
MsgBox "ÁÕ¢éÍÁÙÅÍÂÙèáÅéÇ"
id_txtbox.Text = ""
Me.id_txtbox.SetFocus
Cancel = True
Exit Sub
End If
Next x
End Sub

Code: Select all

Private Sub id_txtbox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        Dim db_student As Worksheet
        Dim sdLR As Long, y As Long, x As Long
        Dim lngCount As Long
        Set db_student = ThisWorkbook.Sheets("db_student")
        lngCount = db_student.Cells(Rows.Count, 1).End(xlUp).Row

        If lngCount = 1 Then
            sdLR = 2
        Else
            sdLR = Application.WorksheetFunction.Max(2, lngCount)
        End If
        
        If sdLR = 2 Then
            Exit Sub
        Else
        For x = 2 To sdLR

                If UCase(db_student.Cells(x, 1)) = id_txtbox.Text Then
                    MsgBox "ÁÕ¢éÍÁÙÅÍÂÙèáÅéÇ"
                    id_txtbox.Text = ""
                    Me.id_txtbox.SetFocus
                    Cancel = True
                    Exit Sub
                End If
        Next x
        End If
        
        Set db_student = Nothing
End Sub

Code: Select all

id_check = Worksheets(db_sheet).Cells(data_row, id_col).Value

Code: Select all

Public Sub search_mode()
Dim id_search As String
Dim id_check As String
Dim data_row As Single
Dim i As Single
id_search = entry_form.id_txtbox.Value
data_row = 2
id_check = Worksheets(db_sheet).Cells(data_row, id_col).Value
With Worksheets(db_sheet)
Do Until id_check = id_search
data_row = data_row + 1
id_check = Worksheets(db_sheet).Cells(data_row, id_col).Value
Loop
End With
With entry_form
.id_txtbox.Value = Worksheets(db_sheet).Cells(data_row, id_col).Value
.name_txtbox.Value = Worksheets(db_sheet).Cells(data_row, name_col).Value
.surname_txtbox.Value = Worksheets(db_sheet).Cells(data_row, surname_col).Value
.year_txtbox.Value = Worksheets(db_sheet).Cells(data_row, year_col).Value
.sequence_txtbox.Value = Worksheets(db_sheet).Cells(data_row, sequence_col).Value
.sex_cbbox.Value = Worksheets(db_sheet).Cells(data_row, sex_col).Value
.faculty_txtbox.Value = Worksheets(db_sheet).Cells(data_row, faculty_col).Value
.major_cbbox.Value = Worksheets(db_sheet).Cells(data_row, major_col).Value
End With
End Sub

Code: Select all

MsgBox "ไม่พบข้อมูล", vbCritica

Code: Select all

Public Sub search_mode()
        Dim id_search As String
        Dim id_check As String
        Dim data_row As Single
        Dim i As Single
        On Error GoTo err
        id_search = entry_form.id_txtbox.Value
        data_row = 2
        id_check = Worksheets(db_sheet).Cells(data_row, id_col).Value

        With Worksheets(db_sheet)
        Do Until id_check = id_search
            data_row = data_row + 1
            id_check = Worksheets(db_sheet).Cells(data_row, id_col).Value
        Loop
        End With
        
            With entry_form
                .id_txtbox.Value = Worksheets(db_sheet).Cells(data_row, id_col).Value
                .name_txtbox.Value = Worksheets(db_sheet).Cells(data_row, name_col).Value
                .surname_txtbox.Value = Worksheets(db_sheet).Cells(data_row, surname_col).Value
                .year_txtbox.Value = Worksheets(db_sheet).Cells(data_row, year_col).Value
                .sequence_txtbox.Value = Worksheets(db_sheet).Cells(data_row, sequence_col).Value
                .sex_cbbox.Value = Worksheets(db_sheet).Cells(data_row, sex_col).Value
                .faculty_txtbox.Value = Worksheets(db_sheet).Cells(data_row, faculty_col).Value
                .major_cbbox.Value = Worksheets(db_sheet).Cells(data_row, major_col).Value
        End With
        Exit Sub
err:
        MsgBox "äÁ辺¢éÍÁÙÅ", vbCritical

End Sub

Code: Select all

'Other code
Do Until id_check = id_search
    data_row = data_row + 1
    id_check = Worksheets(db_sheet).Cells(data_row, id_col).Value
    If Worksheets(db_sheet).Cells(data_row, id_col).Row > _
        Worksheets(db_sheet).Cells(Rows.Count, id_col).End(xlUp).Row Then
        MsgBox "Not found"
        Exit Sub
    End If
Loop
'Other code

Code: Select all

Dim rall As Range, r As Range
With Sheets("db_student")
    Set rall = .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
End With
With entry_form
    For Each r In rall
        If r.Value = .id_txtbox.Text Then
            .LstData.AddItem r.Offset(0, 1).Value & " " & r.Offset(0, 2).Value & " " & r.Offset(0, 5).Value
        End If
    Next r
End With

Code: Select all

Public Sub entry_mode()
Dim blank_row As Single
    blank_row = Worksheets(db_sheet).Cells(1, 1).End(xlDown).Row + 1
With Worksheets(db_sheet)
.Cells(blank_row, id_col).Value = entry_form.id_txtbox.Value
.Cells(blank_row, name_col).Value = entry_form.name_txtbox.Value
.Cells(blank_row, surname_col).Value = entry_form.surname_txtbox.Value
.Cells(blank_row, year_col).Value = entry_form.year_txtbox.Value
.Cells(blank_row, sequence_col).Value = entry_form.sequence_txtbox.Value
.Cells(blank_row, sex_col).Value = entry_form.sex_cbbox.Value
.Cells(blank_row, faculty_col).Value = entry_form.faculty_txtbox.Value
.Cells(blank_row, major_col).Value = entry_form.major_cbbox.Value
End With
MsgBox "ºÑ¹·Ö¡¢éÍÁÙÅàÃÕºÃéÍÂáÅéÇ", vbInformation, "Student Record"
Unload entry_form
 End Sub
Public Sub search_mode()
Dim id_search As String
Dim id_check As String
Dim data_row As Single
Dim i As Single
Dim rall As Range, r As Range
With Sheets("db_student")
    Set rall = .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
End With
With entry_form
    For Each r In rall
        If r.Value = .id_txtbox.Text Then
            .LstData.AddItem r.Offset(0, 1).Value & " " & r.Offset(0, 2).Value & " " & r.Offset(0, 5).Value
        End If
    Next r
End With
id_search = entry_form.id_txtbox.Value
data_row = 2
id_check = Worksheets(db_sheet).Cells(data_row, id_col).Value
With Worksheets(db_sheet)
Do Until id_check = id_search
    data_row = data_row + 1
    id_check = Worksheets(db_sheet).Cells(data_row, id_col).Value
    If Worksheets(db_sheet).Cells(data_row, id_col).Row > _
        Worksheets(db_sheet).Cells(Rows.Count, id_col).End(xlUp).Row Then
        MsgBox "äÁ辺¢éÍÁÙÅ"
        Exit Sub
    End If
Dim name_search As String
Dim name_check As String
Dim data1_row As Single
Dim i1 As Single
Dim rall1 As Range, r1 As Range
With Sheets("db_student")
    Set rall1 = .Range("b2", .Range("a" & .Rows.Count).End(xlUp))
End With
With entry_form
    For Each r In rall
        If r.Value = .name_txtbox.Text Then
            .LstData.AddItem r.Offset(0, 1).Value & " " & r.Offset(0, 2).Value & " " & r.Offset(0, 5).Value
        End If
    Next r
End With
name_search = entry_form.name_txtbox.Value
data1_row = 2
name_check = Worksheets(db_sheet).Cells(data1_row, name_col).Value
With Worksheets(db_sheet)
Do Until name_check = name_search
    data1_row = data_row + 1
    name_check = Worksheets(db_sheet).Cells(data_row, name_col).Value
    If Worksheets(db_sheet).Cells(data_row, name_col).Row > _
        Worksheets(db_sheet).Cells(Rows.Count, name_col).End(xlUp).Row Then
        MsgBox "äÁ辺¢éÍÁÙÅ"
        Exit Sub
    End If
Loop
End With
With entry_form
.id_txtbox.Value = Worksheets(db_sheet).Cells(data_row, id_col).Value
.name_txtbox.Value = Worksheets(db_sheet).Cells(data_row, name_col).Value
.surname_txtbox.Value = Worksheets(db_sheet).Cells(data_row, surname_col).Value
.year_txtbox.Value = Worksheets(db_sheet).Cells(data_row, year_col).Value
.sequence_txtbox.Value = Worksheets(db_sheet).Cells(data_row, sequence_col).Value
.sex_cbbox.Value = Worksheets(db_sheet).Cells(data_row, sex_col).Value
.faculty_txtbox.Value = Worksheets(db_sheet).Cells(data_row, faculty_col).Value
.major_cbbox.Value = Worksheets(db_sheet).Cells(data_row, major_col).Value
End With
           End Sub

Code: Select all

Dim name_search As String
Dim name_check As String
Dim data1_row As Single
Dim i1 As Single
Dim rall1 As Range, r1 As Range
With Sheets("db_student")
    Set rall1 = .Range("b2", .Range("a" & .Rows.Count).End(xlUp))
End With
With entry_form
    For Each r In rall
        If r.Value = .name_txtbox.Text Then
            .LstData.AddItem r.Offset(0, 1).Value & " " & r.Offset(0, 2).Value & " " & r.Offset(0, 5).Value
        End If
    Next r
End With
name_search = entry_form.name_txtbox.Value
data1_row = 2
name_check = Worksheets(db_sheet).Cells(data1_row, name_col).Value
With Worksheets(db_sheet)
Do Until name_check = name_search
    data1_row = data_row + 1
    name_check = Worksheets(db_sheet).Cells(data_row, name_col).Value
    If Worksheets(db_sheet).Cells(data_row, name_col).Row > _
        Worksheets(db_sheet).Cells(Rows.Count, name_col).End(xlUp).Row Then
        MsgBox "äÁ辺¢éÍÁÙÅ"
        Exit Sub
    End If