Excel Forum

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
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
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
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("a2", .Range("a" & .Rows.Count).End(xlUp))
End With
With entry_form
    For Each r In rall1
        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
data_row = 2
name_check = Worksheets(db_sheet).Cells(data_row, name_col).Value
With Worksheets(db_sheet)
Do Until name_check = name_search
    data_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

'Other code
With entry_form
    For Each r In rall
        If r.Value = .id_txtbox.Text Or r.Offset(0, 1).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
'Other code