TẠO BẢNG CHỌN DỮ LIỆU (DATA SELECTOR)
Trong các doanh nghiệp nhỏ dữ liệu thường được bố trí trên Excel. Một số chủ doanh nghiệp không quan tâm đến việc bố trí các dữ liệu này. Công việc thiết kế và sử dụng dữ liệu này hoàn toàn giao cho các kế toán viên. Một số kế toán viên khi thiết kế và bố trí các dữ liệu lại không quan tâm đến việc sử dụng lại các dữ liệu sau này khi doanh nghiệp phát triển. Việc thiết kế các bảng lưu trữ, các bảng mã cho khách hàng, nhân viên bán hàng, các sản phẩm bán ra,...là công việc rất quan trọng ban đầu để móc nối các dữ liệu với nhau trong công việc sử lý số liệu như: theo dõi công nợ, theo dõi doanh số, phân tích theo các yêu cầu...vv.
Vấn đề tôi đặt ra ở đây là khi móc nối các dữ liệu với nhau bạn sẽ phải làm việc nhiều đến việc lựa chọn một số mã từ các bảng mã. Làm sao để công việc lựa chọn của bạn được dễ dàng? Liệu Excel có cung cấp cho ta công cụ này không? Vâng, Excel có cung cấp cho ta một số công cụ nhưng theo kinh nghiệm bản thân, tôi nghĩ cách tốt nhất vẫn là tự mình thiết kế cho mình một bảng chọn. Còn các số liệu khác bạn có thể lấy ra từ các hàm chuẩn của Excel.
Ở đây tôi xin đưa ra một ví dụ nhỏ về việc tạo bảng chọn, để chọn mã sản phẩm từ bảng sản phẩm.
Ví dụ trong sheet MaSanPham, tôi có bảng mã sản phẩm như sau:
1/Đặt tên vùng :
Tôi đặt tên cho vùng A2:B570 có tên là MaSanPham. Bằng cách đánh dấu vùng A2:B570 vào Insert / Name / Define. Trong hộp thoại phần Names in Workbook bạn gõ và MaSanPham , sau đó nhấn nút OK.
2/ Phần 2 : Thiết kế form để người dùng chọn lựa mã sản phẩm.
Từ cửa sổ Excel bạn nhấn tổ hợp phím Alt + F11. Cửa sổ VBE xuất hiện, sau đó bạn chọn Insert/UserForm . Ta đặt tên form là frmDataSelector, thuộc tính Caption là Data Selector. Trên form ta đặt các đối tượng gồm có:
- 1 TextBox có tên là TxtCode
- 1 nút lệnh có tên là cmdOK
- 1 nút lệnh có tên là cmdCancel
- 1 ListView có tên là LVDataSelector. (Đối tượng ListView trên thanh Toolbox, nếu không có bạn phải vào Tools/Additional Controls và chọn
3/ Phần 3: Copy đoạn mã sau vào Module. Đoạn mã trong Module, bạn không cần sửa chỗ nào cả.
Sau đó bạn chọn Insert/Module đặt tên Module là DataSelector và gõ vào các hàm và thủ tục sau:
Code:
Option Explicit
Function RangeNameExists(Nname) As Boolean
' Kiem tra xem Ten bang co ton tai hay khong?
' Neu ton tai thi tra ve TRUE
Dim n As Name
RangeNameExists = False
For Each n In ActiveWorkbook.Names
If UCase(n.Name) = UCase(Nname) Then
RangeNameExists = True
Exit Function
End If
Next n
End Function' Day la ham de xuat cac du lieu tu Bang da duoc dat ten
' sang mot mang
Code:
Function TableToArray(ByVal TableName As String)
Dim arr
Dim vRange As Range
Dim i As Long, j As Long, m As Long, n As Long
If Not RangeNameExists(TableName) Then Exit Function 'Neu khong ton tai thi thoat
On Error Resume Next
Set vRange = Range(TableName)
i = vRange.Rows.count
j = vRange.Columns.count
ReDim arr(1 To i, 1 To j)
For m = 1 To i
For n = 1 To j
arr(m, n) = vRange(m, n).Value
Next n
Next m
TableToArray = arr
Set vRange = Nothing
End Function'Chuyen tu mang sang ListView va dinh dang ListView
Code:
Sub ArrayToListview(ByVal VlistView As Listview, ByVal InputArray)
Dim m As Long
Dim i As Integer, j As Integer
Dim bHang As Long, bCot As Long, bHeader As Integer
Dim it As ListItem
Dim anItem
If Not IsObject(VlistView) Then Exit Sub
On Error Resume Next
'Dem so hang va so cot trong InputArray
bHang = UBound(InputArray, 1)
bCot = UBound(InputArray, 2)
'Dinh dang ListView
VlistView.View = lvwReport
VlistView.FullRowSelect = True
VlistView.MultiSelect = False
VlistView.Gridlines = True
VlistView.LabelEdit = lvwManual
VlistView.HideColumnHeaders = True
bHeader = VlistView.ColumnHeaders.count
Select Case bHeader 'Xac dinh so cot cua ListView
Case Is < bCot
For i = bHeader + 1 To bCot
VlistView.ColumnHeaders.Add i
Next i
Case Is = bCot
'Khong lam gi ca
Case Is > bCot
'Khong lam gi ca
End Select
'Dien cac gia tri tu Inputarray vao Listview
For i = 1 To bHang
For j = 1 To bCot
anItem = InputArray(i, j)
If j = 1 Then
Set it = VlistView.ListItems.Add()
it.Text = anItem
Else
it.SubItems(j - 1) = anItem
End If
Next j
Next i
'Dat do rong cac cot
For i = 1 To bCot
VlistView.ColumnHeaders(i).Width = 150
Next i
Set it = Nothing
Exit Sub
Tbloi:
MsgBox "Xin loi, khong the dua mang vao Listview " , vbCritical, "Th?b?
End Sub'Dua tu bang vao mang, sau do dua tu mang vao Listview
'Va di nhien ban cung co the chuyen tu bang vao ListView
Code:
Sub NhapDuLieu()
On Error Resume Next
Call DataSelector("MaVatTu")
End Sub
Sub DataSelector(Tenbang As String)
Dim bMang1
On Error Resume Next
bMang1 = TableToArray(Tenbang)
Call ArrayToListview(frmDataSelector.LVDataSelector, bMang1)
frmDataSelector.Show
End Sub(Chỗ cần thay thế là frmDataselector và LVDataselector, nếu bạn dùng tên khác)
4/Phần 4 : Đoạn Code của Form : Đoạn Code này có những chỗ cần sửa, tôi sẽ đưa ra cụ thể, bạn hãy sửa cho phù hợp
Đoạn mã của frmDataSelector như sau:
Code:
Private Sub cmdCancel_Click()
Unload Me 'Thoat
End Sub(CmdCancel sẽ được thay thế nếu bạn dùng tên khác cho nút Button )
Code:
Private Sub cmdOK_Click()
Dim bGiatrichon
On Error Resume Next
bGiatrichon = LVDataSelector.SelectedItem.Text
ActiveCell.Value = bGiatrichon 'Dat gia tri ban chon vao o hien tai
End Sub(CmdOK sẽ được thay thế nếu bạn dùng tên khác cho nút Button, LVDataselector cũng được thay thế nếu bạn dùng tên khác )
'Muc dich cua ham sau nham cuon danh sach trong Listview den ma tuong tu
'trong danh sach khi nguoi su dung go vao Textbox txtCode cac ky tu dau tien cua ma.
Code:
Private Sub txtCode_Change()
Dim it As ListItem
On Error Resume Next
btim = Me.txtCode.Text
Set it = Me.LVDataSelector.FindItem(btim, lvwText, , lvwPartial)
bindex = it.Index
Me.LVDataSelector.ListItems.Item(bindex).Selected = True
Me.LVDataSelector.ListItems.Item(bindex).EnsureVis ible
Set it = Nothing
End Sub(TxtCode và LVDataselector là chỗ cần thay thế)
Giả sử bây giờ tôi muốn lấy mã sản phẩm từ bảng MaSanPham, trong sheet MaSanPham, khi tôi nhấn chuột phải ở cột 1 của Sheet2 thì đoạn mã trong Sheet2 như sau:
5/Phần 5 : Đoạn mã của Sheet
Code:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As _ Boolean)
If Target.Column = 1 Then
Cancel = True
Call NhapDuLieu
End If
End SubĐoạn Code này bạn cần lưu ý : Target.Column = 1, hay một số nào khác tuỳ bạn muốn bảng chọn hiện ra ở cột nào khi Click chuột phải
Bây giờ bạn hãy trở về cửa sổ Excel, chọn một ô bất kỳ ở cột 1 của Sheet2, bạn nhấn chuột phải thì frmDataSelector hiện ra , bạn hãy thử gõ vào các ký tự đầu tiên của mã sản phẩm bạn cần...và bạn sẽ thấy nó hoạt động như thế nào.
Bạn có thể cải tiến đoạn mã trong module DataSelector bằng việc sát nhập hàm TableToArray và thủ tục ArrayToListview thành một.
Chúc các bạn thành công.
Trong các doanh nghiệp nhỏ dữ liệu thường được bố trí trên Excel. Một số chủ doanh nghiệp không quan tâm đến việc bố trí các dữ liệu này. Công việc thiết kế và sử dụng dữ liệu này hoàn toàn giao cho các kế toán viên. Một số kế toán viên khi thiết kế và bố trí các dữ liệu lại không quan tâm đến việc sử dụng lại các dữ liệu sau này khi doanh nghiệp phát triển. Việc thiết kế các bảng lưu trữ, các bảng mã cho khách hàng, nhân viên bán hàng, các sản phẩm bán ra,...là công việc rất quan trọng ban đầu để móc nối các dữ liệu với nhau trong công việc sử lý số liệu như: theo dõi công nợ, theo dõi doanh số, phân tích theo các yêu cầu...vv.
Vấn đề tôi đặt ra ở đây là khi móc nối các dữ liệu với nhau bạn sẽ phải làm việc nhiều đến việc lựa chọn một số mã từ các bảng mã. Làm sao để công việc lựa chọn của bạn được dễ dàng? Liệu Excel có cung cấp cho ta công cụ này không? Vâng, Excel có cung cấp cho ta một số công cụ nhưng theo kinh nghiệm bản thân, tôi nghĩ cách tốt nhất vẫn là tự mình thiết kế cho mình một bảng chọn. Còn các số liệu khác bạn có thể lấy ra từ các hàm chuẩn của Excel.
Ở đây tôi xin đưa ra một ví dụ nhỏ về việc tạo bảng chọn, để chọn mã sản phẩm từ bảng sản phẩm.
Ví dụ trong sheet MaSanPham, tôi có bảng mã sản phẩm như sau:
1/Đặt tên vùng :
Tôi đặt tên cho vùng A2:B570 có tên là MaSanPham. Bằng cách đánh dấu vùng A2:B570 vào Insert / Name / Define. Trong hộp thoại phần Names in Workbook bạn gõ và MaSanPham , sau đó nhấn nút OK.
2/ Phần 2 : Thiết kế form để người dùng chọn lựa mã sản phẩm.
Từ cửa sổ Excel bạn nhấn tổ hợp phím Alt + F11. Cửa sổ VBE xuất hiện, sau đó bạn chọn Insert/UserForm . Ta đặt tên form là frmDataSelector, thuộc tính Caption là Data Selector. Trên form ta đặt các đối tượng gồm có:
- 1 TextBox có tên là TxtCode
- 1 nút lệnh có tên là cmdOK
- 1 nút lệnh có tên là cmdCancel
- 1 ListView có tên là LVDataSelector. (Đối tượng ListView trên thanh Toolbox, nếu không có bạn phải vào Tools/Additional Controls và chọn
3/ Phần 3: Copy đoạn mã sau vào Module. Đoạn mã trong Module, bạn không cần sửa chỗ nào cả.
Sau đó bạn chọn Insert/Module đặt tên Module là DataSelector và gõ vào các hàm và thủ tục sau:
Code:
Option Explicit
Function RangeNameExists(Nname) As Boolean
' Kiem tra xem Ten bang co ton tai hay khong?
' Neu ton tai thi tra ve TRUE
Dim n As Name
RangeNameExists = False
For Each n In ActiveWorkbook.Names
If UCase(n.Name) = UCase(Nname) Then
RangeNameExists = True
Exit Function
End If
Next n
End Function' Day la ham de xuat cac du lieu tu Bang da duoc dat ten
' sang mot mang
Code:
Function TableToArray(ByVal TableName As String)
Dim arr
Dim vRange As Range
Dim i As Long, j As Long, m As Long, n As Long
If Not RangeNameExists(TableName) Then Exit Function 'Neu khong ton tai thi thoat
On Error Resume Next
Set vRange = Range(TableName)
i = vRange.Rows.count
j = vRange.Columns.count
ReDim arr(1 To i, 1 To j)
For m = 1 To i
For n = 1 To j
arr(m, n) = vRange(m, n).Value
Next n
Next m
TableToArray = arr
Set vRange = Nothing
End Function'Chuyen tu mang sang ListView va dinh dang ListView
Code:
Sub ArrayToListview(ByVal VlistView As Listview, ByVal InputArray)
Dim m As Long
Dim i As Integer, j As Integer
Dim bHang As Long, bCot As Long, bHeader As Integer
Dim it As ListItem
Dim anItem
If Not IsObject(VlistView) Then Exit Sub
On Error Resume Next
'Dem so hang va so cot trong InputArray
bHang = UBound(InputArray, 1)
bCot = UBound(InputArray, 2)
'Dinh dang ListView
VlistView.View = lvwReport
VlistView.FullRowSelect = True
VlistView.MultiSelect = False
VlistView.Gridlines = True
VlistView.LabelEdit = lvwManual
VlistView.HideColumnHeaders = True
bHeader = VlistView.ColumnHeaders.count
Select Case bHeader 'Xac dinh so cot cua ListView
Case Is < bCot
For i = bHeader + 1 To bCot
VlistView.ColumnHeaders.Add i
Next i
Case Is = bCot
'Khong lam gi ca
Case Is > bCot
'Khong lam gi ca
End Select
'Dien cac gia tri tu Inputarray vao Listview
For i = 1 To bHang
For j = 1 To bCot
anItem = InputArray(i, j)
If j = 1 Then
Set it = VlistView.ListItems.Add()
it.Text = anItem
Else
it.SubItems(j - 1) = anItem
End If
Next j
Next i
'Dat do rong cac cot
For i = 1 To bCot
VlistView.ColumnHeaders(i).Width = 150
Next i
Set it = Nothing
Exit Sub
Tbloi:
MsgBox "Xin loi, khong the dua mang vao Listview " , vbCritical, "Th?b?
End Sub'Dua tu bang vao mang, sau do dua tu mang vao Listview
'Va di nhien ban cung co the chuyen tu bang vao ListView
Code:
Sub NhapDuLieu()
On Error Resume Next
Call DataSelector("MaVatTu")
End Sub
Sub DataSelector(Tenbang As String)
Dim bMang1
On Error Resume Next
bMang1 = TableToArray(Tenbang)
Call ArrayToListview(frmDataSelector.LVDataSelector, bMang1)
frmDataSelector.Show
End Sub(Chỗ cần thay thế là frmDataselector và LVDataselector, nếu bạn dùng tên khác)
4/Phần 4 : Đoạn Code của Form : Đoạn Code này có những chỗ cần sửa, tôi sẽ đưa ra cụ thể, bạn hãy sửa cho phù hợp
Đoạn mã của frmDataSelector như sau:
Code:
Private Sub cmdCancel_Click()
Unload Me 'Thoat
End Sub(CmdCancel sẽ được thay thế nếu bạn dùng tên khác cho nút Button )
Code:
Private Sub cmdOK_Click()
Dim bGiatrichon
On Error Resume Next
bGiatrichon = LVDataSelector.SelectedItem.Text
ActiveCell.Value = bGiatrichon 'Dat gia tri ban chon vao o hien tai
End Sub(CmdOK sẽ được thay thế nếu bạn dùng tên khác cho nút Button, LVDataselector cũng được thay thế nếu bạn dùng tên khác )
'Muc dich cua ham sau nham cuon danh sach trong Listview den ma tuong tu
'trong danh sach khi nguoi su dung go vao Textbox txtCode cac ky tu dau tien cua ma.
Code:
Private Sub txtCode_Change()
Dim it As ListItem
On Error Resume Next
btim = Me.txtCode.Text
Set it = Me.LVDataSelector.FindItem(btim, lvwText, , lvwPartial)
bindex = it.Index
Me.LVDataSelector.ListItems.Item(bindex).Selected = True
Me.LVDataSelector.ListItems.Item(bindex).EnsureVis ible
Set it = Nothing
End Sub(TxtCode và LVDataselector là chỗ cần thay thế)
Giả sử bây giờ tôi muốn lấy mã sản phẩm từ bảng MaSanPham, trong sheet MaSanPham, khi tôi nhấn chuột phải ở cột 1 của Sheet2 thì đoạn mã trong Sheet2 như sau:
5/Phần 5 : Đoạn mã của Sheet
Code:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As _ Boolean)
If Target.Column = 1 Then
Cancel = True
Call NhapDuLieu
End If
End SubĐoạn Code này bạn cần lưu ý : Target.Column = 1, hay một số nào khác tuỳ bạn muốn bảng chọn hiện ra ở cột nào khi Click chuột phải
Bây giờ bạn hãy trở về cửa sổ Excel, chọn một ô bất kỳ ở cột 1 của Sheet2, bạn nhấn chuột phải thì frmDataSelector hiện ra , bạn hãy thử gõ vào các ký tự đầu tiên của mã sản phẩm bạn cần...và bạn sẽ thấy nó hoạt động như thế nào.
Bạn có thể cải tiến đoạn mã trong module DataSelector bằng việc sát nhập hàm TableToArray và thủ tục ArrayToListview thành một.
Chúc các bạn thành công.
