Vậy bạn muốn Print theo mẫu như thế nào, Next, back thì nó ra sao?
[MãNV], [Mối QH],[ Họ Tên]; [Năm sinh]
NVH00, Mẹ , Ngô T. Tô; 1950Yêu cầu các nút Next, Back cũng không khó, mình thấy bạn viết code combobox được thì các nút đó chỉ cần làm 1 số việc, ví dụ nút Next:
Mình chỉ bổ sung thêm code nút lệnh next, back và option button. Nút print chưa được. Bạn xóa code trong form đi, thay bằng code này:
Private Sub ComboBox1_Click()
On Error Resume Next
Dim rw As Long, cl As Range
rw = WorksheetFunction.Match(Me.ComboBox1.Value, Worksheets("BANGTONGHOP").Range("D:D"), 0)
If Err.Number Then MsgBox "Không coì CB-CNV câÌn tiÌm!!!"
hovaten = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 0)
namsinh = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 5)
tuoi = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 7)
chucvu = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 3)
taikhoan = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 51)
trinhdovanhoa = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 8)
trinhdochuyenmon = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 9)
ngoaingu = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 41)
tinhoc = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 42)
tnbonhiem = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 24)
dangdoan = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 12)
tncongtactaicang = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 14)
hesoluong = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 15)
tnnangluong = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 16)
thangbangbacluong = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 19)
solaodong = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 44)
sobhxh = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 45)
matncn = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 21)
nguoiphuthuoc = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 46)
hotencha = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 28)
hotenme = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 29)
hotenvochong = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 30)
socon = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 31)
nguyenquan = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 32)
noisinh = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 33)
thuongtru = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 36)
cmnd = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 40)
dienthoai = Worksheets("BANGTONGHOP").Range("D" & rw).Offset(0, 52)
For Each cl In Worksheets("BANGTONGHOP").Range("D" & rw & ":AZ" & rw)
Next cl
CommandButton2.Enabled = (ComboBox1.ListIndex < ComboBox1.ListCount - 1)
CommandButton3.Enabled = (ComboBox1.ListIndex > 0)
End Sub
Private Sub CommandButton1_Click()
Application.Quit
End Sub
Private Sub CommandButton2_Click()
ComboBox1.ListIndex = ComboBox1.ListIndex + 1
End Sub
Private Sub CommandButton3_Click()
ComboBox1.ListIndex = ComboBox1.ListIndex - 1
End Sub
'Code cho nút Close Form
Private Sub Image1_Click()
End
End Sub
Private Sub OptionButton1_Click()
option_button "H"
End Sub
Private Sub option_button(nhom As String)
Application.EnableEvents = False
ComboBox1.Clear
irow = Sheets("BANGTONGHOP").Range("E65536").End(xlUp).Row
For i = 5 To irow
If Left(Sheets("BANGTONGHOP").Cells(i, 5), 1) = nhom Then ComboBox1.AddItem Sheets("BANGTONGHOP").Cells(i, 4)
Next
CommandButton2.Enabled = True
CommandButton3.Enabled = False
Application.EnableEvents = True
End Sub
Private Sub OptionButton2_Click()
option_button "B"
End Sub
Private Sub OptionButton3_Click()
option_button "T"
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Hãy Click nút THOÁT!", vbCritical + vbOKOnly, " No Close"
End If
End Sub
Private Sub UserForm_Initialize()
Dim ws1 As Worksheet
Set ws1 = Worksheets("BANGTONGHOP")
irow = ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).Row - 1
With ComboBox1
'Chon dòng bát dàu Data Hien ra Form
Application.EnableEvents = False
For Row = 5 To irow
'Láy Data vào combobox cot E Nguòi xu ly
.AddItem Sheets("BANGTONGHOP").Cells(Row, 4)
Next Row
CommandButton3.Enabled = False
Application.EnableEvents = True
End With
End SubPrivate Sub option_button (nhom as string)
Dim s$, irow&, i&
Application. EnableEvents=false
Combobox1.clear
Irow=Sheets("bangtonghop").Range("E65536").End(3).Row
For i=5 to irow
s=sheets("bangtonghop").cells(i,5).text
If (instr(s,nhom)>0) or (nhom="HCTH" and s="BV") then combobox1.additem sheets("bangtonghop").cells(i,4)
Next
Commandbutton2.enabled=true
Commandbutton3.enabled=false
Application.enableevents=true
End sub
Bạn có thể tham khảo cách khác khi viet code cho cái Combox.
Private Sub ComboBox1_Change()
Dim rw&, i&, str(), Cot()
str = Array("hovaten", "chucvu", "namsinh", "tuoi", "trinhdovanhoa", _
"trinhdochuyenmon", "dangdoan", "tncongtactaicang", "hesoluong", "tnnangluong", _
"thangbangbacluong", "matncn", "tnbonhiem", "hotencha", "hotenme", "hotenvochong", _
"socon", "nguyenquan", "noisinh", "thuongtru", "cmnd", "ngoaingu", "tinhoc", _
"solaodong", "sobhxh", "nguoiphuthuoc", "taikhoan", "dienthoai")
Cot = Array(0, 3, 5, 7, 8, 9, 12, 14, 15, 16, 19, 21, 24, 28, 29, _
30, 31, 32, 33, 36, 40, 41, 42, 44, 45, 46, 51, 52)
With Worksheets("BANGTONGHOP")
rw = WorksheetFunction.Match(Me.ComboBox1.Value, .Range("D:D"), 0)
For i = 0 To UBound(str)
Me.Controls(str(i)).Value = .Cells(rw, 4).Offset(, Cot(i)).Value
Next
End With
End Sub
