Private Sub UserForm_Initialize()
Call khoi_tao_du_lieu
End Sub
Sub khoi_tao_du_lieu()
Me.txt_name.Value = "Ho va Ten"
Me.txt_date.Value = "Ngay thang nam sinh mm/dd/yyyy"
Me.txt_email.Value = "Dia chi email"
Me.txt_phone.Value = "So dien thoai"
Me.btn_nam.Value = True
Me.btn_nu.Value = False
Me.txt_addr.Value = "Dia chi"
Me.txt_name.ForeColor = RGB(191, 191, 191)
Me.txt_date.ForeColor = RGB(191, 191, 191)
Me.txt_email.ForeColor = RGB(191, 191, 191)
Me.txt_phone.ForeColor = RGB(191, 191, 191)
Me.txt_addr.ForeColor = RGB(191, 191, 191)
Me.txt_rowid.Value = ""
Me.txt_sex = "Nam"
Me.txt_img_url = ""
Me.img_picture.Picture = LoadPicture("")
End Sub
Private Sub txt_name_Enter()
If Me.txt_name.Value = "Ho va Ten" Then
Me.txt_name.Value = ""
Me.txt_name.ForeColor = vbBlack
End If
End Sub
Private Sub txt_name_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.txt_name.Value = "" Then
Me.txt_name.Value = "Ho va Ten"
Me.txt_name.ForeColor = RGB(191, 191, 191)
End If
End Sub
Private Sub txt_date_Enter()
If Me.txt_date.Value = "Ngay thang nam sinh mm/dd/yyyy" Then
Me.txt_date.Value = ""
Me.txt_date.ForeColor = vpBlack
End If
End Sub
Private Sub txt_date_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.txt_date.Value = "" Then
Me.txt_date.Value = "Ngay thang nam sinh mm/dd/yyyy"
Me.txt_date.ForeColor = RGB(191, 191, 191)
End If
End Sub
Private Sub txt_email_Enter()
If Me.txt_email.Value = "Dia chi email" Then
Me.txt_email.Value = ""
Me.txt_email.ForeColor = vpBlack
End If
End Sub
Private Sub txt_email_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.txt_email.Value = "" Then
Me.txt_email.Value = "Dia chi email"
Me.txt_email.ForeColor = RGB(191, 191, 191)
End If
End Sub
Private Sub txt_phone_Enter()
If Me.txt_phone.Value = "So dien thoai" Then
Me.txt_phone.Value = ""
Me.txt_phone.ForeColor = vpBlack
End If
End Sub
Private Sub txt_phone_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.txt_phone.Value = "" Then
Me.txt_phone.Value = "So dien thoai"
Me.txt_phone.ForeColor = RGB(191, 191, 191)
End If
End Sub
Private Sub txt_addr_Enter()
If Me.txt_addr.Value = "Dia chi" Then
Me.txt_addr.Value = ""
Me.txt_addr.ForeColor = vpBlack
End If
End Sub
Private Sub txt_addr_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.txt_addr.Value = "" Then
Me.txt_addr.Value = "Dia chi"
Me.txt_addr.ForeColor = RGB(191, 191, 191)
End If
End Sub
Private Sub img_brown_Click()
Dim img As String
img = Application.GetOpenFilename(filefilter:="jpec file, *.jpg", Title:="chon file")
If Dir(img) <> "" Then
Me.txt_img_url.Value = img
Me.img_picture.Picture = LoadPicture(Me.txt_img_url.Value)
End If
End Sub
Private Sub btn_nam_Click()
Call gioitinh
End Sub
Sub gioitinh()
If Me.btn_nam.Value = True Then
Me.txt_sex.Value = "Nam"
Else
Me.txt_sex.Value = "nu"
End If
End Sub
Private Sub btn_nu_Click()
Call gioitinh
End Sub
Private Sub img_add_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data_QLNS")
If Excel.WorksheetFunction.And(Me.txt_name.Value <> "", Me.txt_date.Value <> "", Me.txt_addr.Value <> "") Then
If Excel.WorksheetFunction.CountIfs(ws.Range("D:D"), Me.txt_email.Value) Then
MsgBox "Email nay da ton tai"
Exit Sub
End If
If Excel.WorksheetFunction.CountIfs(ws.Range("E:E"), Me.txt_phone.Value) Then
MsgBox "Sdt da ton tai"
Exit Sub
End If
Dim lastrow As Double
lastrow = Excel.WorksheetFunction.CountA(ws.Range("B:B")) + 1
ws.Range("B" & lastrow + 1).Value = Me.txt_name.Value
ws.Range("C" & lastrow + 1).Value = Format(Me.txt_date.Value, "mm/dd/yyyy")
ws.Range("D" & lastrow + 1).Value = Me.txt_email.Value
ws.Range("E" & lastrow + 1).Value = Me.txt_phone.Value
ws.Range("F" & lastrow + 1).Value = Me.txt_sex.Value
ws.Range("G" & lastrow + 1).Value = Me.txt_addr.Value
ws.Range("H" & lastrow + 1).Value = Me.txt_img_url.Value
MsgBox "Them du lieu thanh cong"
Call khoi_tao_du_lieu
Else
MsgBox "Chua nhap du du lieu"
End If
End Sub
Private Sub img_delete_click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data_QLNS")
If MsgBox("Ban co chac chan muan xoa khong", vbYesNo, "Xoa du lieu") = vbNo Then
Exit Sub
Else
ws.Rows(Me.txt_rowid.Value).Delete
MsgBox "Da xoa du lieu"
End If
End Sub
Private Sub img_reset_click()
Call khoi_tao_du_lieu
End Sub
Private Sub img_seach_email_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data_QLNS")
If Me.txt_email.Value = "" Then
MsgBox "Ban chua nhap email"
Exit Sub
End If
If Excel.WorksheetFunction.CountIfs(ws.Range("D:D"), Me.txt_email.Value) > 1 Then
MsgBox "khong tim thay do email bi trung"
Exit Sub
End If
If Excel.WorksheetFunction.CountIfs(ws.Range("D:D"), Me.txt_email.Value) = 0 Then
MsgBox " Email nay khong co trong danh sach"
Exit Sub
End If
If Excel.WorksheetFunction.CountIfs(ws.Range("D:D"), Me.txt_email.Value) = 1 Then
Me.txt_rowid.Value = Excel.WorksheetFunction.Match(Me.txt_email.Value, ws.Range("D:D"), 0)
Me.txt_name.Value = ws.Range("B" & Me.txt_rowid.Value).Value
Me.txt_date.Value = ws.Range("C" & Me.txt_rowid.Value).Value
Me.txt_email.Value = ws.Range("D" & Me.txt_rowid.Value).Value
Me.txt_phone.Value = ws.Range("E" & Me.txt_rowid.Value).Value
Me.txt_sex.Value = ws.Range("F" & Me.txt_rowid.Value).Value
Me.txt_addr.Value = ws.Range("G" & Me.txt_rowid.Value).Value
Me.txt_img_url.Value = ws.Range("H" & Me.txt_rowid.Value).Value
If Me.txt_sex.Value = "Nam" Then
Me.btn_nam.Value = True
Else
Me.btn_nu.Value = True
End If
Call hien_du_lieu
End If
End Sub
Private Sub img_seach_name_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data_QLNS")
If Me.txt_name.Value = "" Then
MsgBox "Ban chua nhap ten"
Exit Sub
End If
If Excel.WorksheetFunction.CountIfs(ws.Range("B:B"), Me.txt_name.Value) > 1 Then
MsgBox "khong tim thay do ten bi trung"
Exit Sub
End If
If Excel.WorksheetFunction.CountIfs(ws.Range("B:B"), Me.txt_name.Value) = 0 Then
MsgBox "khong co nguoi nay trong danh sach"
Exit Sub
End If
If Excel.WorksheetFunction.CountIfs(ws.Range("B:B"), Me.txt_name.Value) = 1 Then
Me.txt_rowid.Value = Excel.WorksheetFunction.Match(Me.txt_name.Value, ws.Range("B:B"), 0)
Me.txt_name.Value = ws.Range("B" & Me.txt_rowid.Value).Value
Me.txt_date.Value = ws.Range("C" & Me.txt_rowid.Value).Value
Me.txt_email.Value = ws.Range("D" & Me.txt_rowid.Value).Value
Me.txt_phone.Value = ws.Range("E" & Me.txt_rowid.Value).Value
Me.txt_sex.Value = ws.Range("F" & Me.txt_rowid.Value).Value
Me.txt_addr.Value = ws.Range("G" & Me.txt_rowid.Value).Value
Me.txt_img_url.Value = ws.Range("H" & Me.txt_rowid.Value).Value
If Me.txt_sex.Value = "Nam" Then
Me.btn_nam.Value = True
Else
Me.btn_nu.Value = True
End If
Call hien_du_lieu
End If
End Sub
Sub hien_du_lieu()
Me.txt_name.ForeColor = vbBlack
Me.txt_date.ForeColor = vbBlack
Me.txt_email.ForeColor = vbBlack
Me.txt_phone.ForeColor = vbBlack
Me.txt_sex.ForeColor = vbBlack
Me.txt_addr.ForeColor = vbBlack
Me.txt_img_url.ForeColor = vbBlack
If Me.txt_img_url <> "" Then
Me.img_picture.Picture = LoadPicture(Me.txt_img_url.Value)
End If
End Sub
Private Sub img_seach_phone_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data_QLNS")
If Me.txt_phone.Value = "" Then
MsgBox "Ban chua nhap sdt"
Exit Sub
End If
If Excel.WorksheetFunction.CountIfs(ws.Range("E:E"), Me.txt_phone.Value) > 1 Then
MsgBox "khong tim thay do sdt bi trung"
Exit Sub
End If
If Excel.WorksheetFunction.CountIfs(ws.Range("E:E"), Me.txt_phone.Value) = 0 Then
MsgBox "khong sdt nay trong danh sach"
Exit Sub
End If
If Excel.WorksheetFunction.CountIfs(ws.Range("e:e"), Me.txt_phone.Value) = 1 Then
Me.txt_rowid.Value = Excel.WorksheetFunction.Match(Me.txt_phone.Value * 1, ws.Range("e:e"), 0)
Me.txt_name.Value = ws.Range("B" & Me.txt_rowid.Value).Value
Me.txt_date.Value = ws.Range("C" & Me.txt_rowid.Value).Value
Me.txt_email.Value = ws.Range("D" & Me.txt_rowid.Value).Value
Me.txt_phone.Value = ws.Range("E" & Me.txt_rowid.Value).Value
Me.txt_sex.Value = ws.Range("F" & Me.txt_rowid.Value).Value
Me.txt_addr.Value = ws.Range("G" & Me.txt_rowid.Value).Value
Me.txt_img_url.Value = ws.Range("H" & Me.txt_rowid.Value).Value
If Me.txt_sex.Value = "Nam" Then
Me.btn_nam.Value = True
Else
Me.btn_nu.Value = True
End If
Call hien_du_lieu
End If
End Sub
Private Sub img_update_click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data_QLNS")
If Excel.WorksheetFunction.And(Me.txt_name.Value <> "", Me.txt_date.Value <> "", Me.txt_addr.Value <> "") Then
ws.Range("B" & Me.txt_rowid).Value = Me.txt_name.Value
ws.Range("C" & Me.txt_rowid).Value = Format(Me.txt_date.Value, "mm/dd/yyyy")
ws.Range("D" & Me.txt_rowid).Value = Me.txt_email.Value
ws.Range("E" & Me.txt_rowid).Value = Me.txt_phone.Value
ws.Range("F" & Me.txt_rowid).Value = Me.txt_sex.Value
ws.Range("G" & Me.txt_rowid).Value = Me.txt_addr.Value
ws.Range("H" & Me.txt_rowid).Value = Me.txt_img_url.Value
MsgBox "Cap nhat du lieu thanh cong"
Call khoi_tao_du_lieu
Else
MsgBox "du lieu trong khong update duoc"
End If
End Sub
0 nhận xét:
Đăng nhận xét