Quy trình của việc tạo Userform trong Excel và các bước thực hiện

[ptm0412]

Trên diễn đàn đã có rất nhiều chủ đề hỏi về Userform, cũng như nhiều chủ đề giới thiệu hoặc chia sẻ Userform. Tuy nhiên nói về căn bản tạo dựng userform không phải ai cũng biết, kết quả là userform tạo ra rất hoành tráng, rất đẹp đẽ, nhưng khi xảy ra lỗi thì lại không biết do đâu. Cũng có khi tạo userform không đúng trình tự khiến cho công việc trở nên khó khăn hơn, hoặc chọn control(s) không phù hợp khiến cho code phải viết phức tạp hơn.
Do đó tôi muốn giới thiệu kiến thức căn bản nhất trong việc tạo dựng form, và quy trình tuần tự cho việc tạo form. Nếu có thời gian, tôi sẽ viết nhiều hơn, tạm thời như sau:

Khái niệm về User form​

User form là một form mẫu nhập liệu hoặc hiển thị như 1 giao diện thay thế cho bảng tính với dòng cột đơn điệu. User form với những control đặc thù, nếu biết sắp xếp, trình bày một chút sẽ làm cho công việc của người dùng với Excel bớt tẻ nhạt.

Chức năng cơ bản của user form:​

• Nhập liệu trên user form, tính toán một số trường sau đó xem kết quả hoặc lưu xuống bảng tính
• Nạp dữ liệu từ bảng tính lên form để xem và hiệu chỉnh nếu cần, sau đó lưu xuống bảng tính lại
• Tính toán ra kết quả ngay trên user form
• Tạo các nút lệnh thực thi các thủ tục tác động lên bảng tính, lên workbook, worksheet, vùng dữ liệu như in worksheet, đóng mở workbook, tác động lên các controls của form, mở form khác, tạo mới sheet và chép dữ liệu xuống, …
• Tạo và in báo cáo từ các nút lệnh

Chức năng phụ​

Các chức năng không cần thiết có thể bỏ qua hoặc hạn chế dùng:

• Đăng nhập với user name và password: Hoàn toàn có thể bỏ qua, người dùng biết đọc code có thể tắt chức năng này.
• Thay thế việc nhập liệu, sửa xóa trên bảng tính bằng cách ẩn hiển thị bảng tính. Hạn chế dùng hoặc hoàn toàn không dùng nếu không biết cách kiểm soát, vì không thể sử dụng song song với những file Excel khác.
• Trang trí: Dùng logo, hình ảnh, định dạng màu sắc … để trang trí form. Chỉ nên dùng ở mức tối giản.

Yêu cầu phải có khi muốn tạo 1 userform​

Không phải ai muốn vẽ form cũng được, và không phải viết code thế nào form cũng chạy được. Và cũng không phải tạo form cho 1 mình mình xài, nhất là khi hỗ trợ tạo form cho người khác. Cần đáp ứng những yêu cầu sau nếu muốn tạo form ở mức hoàn thiện tối thiểu:

• Biết căn bản về lập trình, biết tìm lỗi khi chạy thử
• Biết rõ tính chất, tính năng và các phương thức cho từng loại control
• Nắm vững và thực hiện đúng quy trình
• Biết phương pháp test
• Thấu hiểu rằng code đơn giản là code dễ quản lý, dễ hiệu chỉnh, dễ bổ sung.
• Tư duy logic là cần thiết nhất, tư duy trang trí nghệ thuật là không cần thiết nhất

Quy trình chuẩn​

Xem sơ đồ sau:



Trong đó 3 bước đầu là quan trọng nhất, đặc biệt là phần vẽ ra giấy.
Trang trí form là việc ít quan trọng nhất, và làm sau cùng.

​

 

[ptm0412]

Nhờ vậy mới biết chương tiếp là có cái thủ thuật này...

Nhá hàng 1 chút nè

 

[ptm0412]

Chọn tiếng Anh thì tên công ty khách hàng tiếng Anh luôn nè

 

[ongke0711]

Chọn tiếng Anh thì tên công ty khách hàng tiếng Anh luôn nè

View attachment 293497

Demo cái Msgbox luôn anh.
Nói chung làm cái này khá là mất công xây dựng nguồn dữ liệu ban đầu .

 

[ptm0412]

Demo cái Msgbox luôn anh.
Nói chung làm cái này khá là mất công xây dựng nguồn dữ liệu ban đầu .

Cái Msgbox nó không chịu 1 số ký tự tiếng Việt có dấu. Thí dụ "đ" và "ệ". Trên command button thì ok



Sao bạn làm theo cột? Tôi làm theo dòng, chỉ 2 cột thôi và bao nhiêu dòng cũng được. Coi như 1 tự điển, và khi mở rộng ngôn ngữ (thêm ngôn ngữ thứ 3) rất dễ dàng.

 

[ptm0412]

Tùy chọn ngôn ngữ hiển thị​

Khi ta muốn tạo ra file excel cho nhiều người dùng, trong đó có người nước ngoài thì muốn có hiển thị 2 ngôn ngữ. Do diện tích form có giới hạn nên không phải lúc nào cũng có thể tạo label 2 ngôn ngữ như sau:



Những tiêu đề nhỏ như sau lại càng không thể:


Hãy làm 1 cách chuyên nghiệp: Cho phép người dùng chọn ngôn ngữ hiển thị, và code căn cứ vào ngôn ngữ được chọn để thay đổi tất cả caption của tất cả các control hiển thị. Bao gồm:

• Form title
• Label
• Frame
• Option button
• Checkbox
• Command button

Tạo 1 tự điển Anh Việt​

Trên sheet, hãy tạo 1 tự điển 2 cột: 1 cột tiếng Anh và 1 cột tiếng Việt tương đương. Bên phải bỏ trống không được làm gì.
Cột tiếng Anh liệt kê hết các caption có trên tất cả các form, không trùng. Cột tiếng Việt là các hiển thị tương đương bằng tiếng Việt.



Vùng này đặt 1 name động co giãn 2 chiều. Chiều dọc có thể thêm dòng dữ liệu, chiều ngang có thể thêm ngôn ngữ thử 4, thứ 5.

Tạo 1 form chuyển đổi ngôn ngữ​

Gồm 1 combobox chọn ngôn ngữ và 1 nút Apply



Khi nhấn nút Apply, lưu 1 giá trị của combobox xuống sheet

Private Sub BtnClose_Click()
 Sheet2.[B1].Value = Me.ComboBox1.ListIndex
 Unload Me
End Sub

Giá trị ô B1 này sẽ dùng để xác định ngôn ngữ và tìm tên tiếng Việt tương ứng tiếng Anh.

Thiết kế các user form​

Vẽ các user form như hướng dẫn ở trên, caption các control tất cả là tiếng Anh. Viết code đến khi hoàn thiện.

Viết trong Module:​

Viết 1 hàm lấy caption đúng ngôn ngữ

Function GetLg(FindCap As String) As String
 CurrLang = Sheet2.[B1].Value
 GetLg = Sheet2.Range("EVDictionary").Find(FindCap).Offset(0, CurrLang).Value
End Function

Với mỗi caption tiếng Anh sẽ tìm được 1 chuỗi tiếng Việt tương ứng, hoặc để nguyên nếu ngôn ngữ chọn là tiếng Anh. Biến Current language đọc ở ô B1 (là 1 con số đã được gán từ form chuyển đổi ngôn ngữ)

Viết 1 thủ tục dùng chung, có tham số truyền là Userform. Thủ tục này duyệt tất cả control để xét control nào có caption thì chuyển ngôn ngữ đúng như ngôn ngữ chọn. (chuyển bằng hàm GetLg). Có thể chuyển ngôn ngữ cho cả controlTipText.

Sub ApplyLanguage(SForm)
Dim Ctrl As Control
With SForm
 .Caption = GetLg(.Caption)
For Each Ctrl In .Controls
 If Ctrl.ControlTipText <> "" Then Ctrl.ControlTipText = GetLg(Ctrl.ControlTipText)
 If TypeOf Ctrl Is msforms.CommandButton Or _
 TypeOf Ctrl Is msforms.Label Or _
 TypeOf Ctrl Is msforms.Frame Or _
 TypeOf Ctrl Is msforms.CheckBox Or _
 TypeOf Ctrl Is msforms.OptionButton Then
 Ctrl.Caption = GetLg(Ctrl.Caption)
 End If
Next
End With
End Sub

Thêm lệnh cho các form​

Mỗi user form, trong sự kiện Initialize thêm 1 câu lệnh ApplyLanguage Me

Private Sub UserForm_Initialize()
 HideUnhide
 ApplyLanguage Me
 CalSquare = True
 Shape1 = True
End Sub

Trường hợp đặc biệt​

Các trường hợp label có caption phụ thuộc vào control khác, thì thay vì gán caption mới, hãy gán GetLg(“Caption mới”)

Sub TwoDimension()
  Shape1.Caption = GetLg("Rectangle")
 Shape2.Caption = GetLg("Round")
 Shape3.Caption = GetLg("Square shape")
End Sub

Trường hợp gán caption ngay khi mở form cũng vậy:

Private Sub UserForm_Initialize()
 Me.LblTieude.Caption = GetLg(Sheet1.Range("PNK"))
 ApplyLanguage Me
 Me.OptPNhap = True
End Sub

Trường hợp message box cũng cứ ghi vào tự điển và GetLng

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
 MsgBox GetLg("Finish your work and click Close button"), , GetLg("Close form fail")
 Cancel = True
End If
End Sub

Kết quả:






Cả tooltip cũng tiếng Việt theo tự điển



Thông báo tiếng Việt cũng cho vào tự điển:

Hiển thị dữ liệu đa ngôn ngữ​

Nếu dữ liệu có thêm cột tiếng Anh kế bên cột tiếng Việt, thậm chí còn có thể hiển thị theo ngôn ngữ đã chọn.
Thí dụ danh mục khách hàng:



Code như sau:

Private Sub UserForm_Initialize()
ApplyLanguage Me
If Sheet2.[B1].Value = 0 Then
 Me.CbCustomer.BoundColumn = 1
 Me.CbCustomer.ColumnWidths = "200;0;0;0"
Else
 Me.CbCustomer.BoundColumn = 2
 Me.CbCustomer.ColumnWidths = "0;200;0;0"
End If
End Sub

Lấy đủ các cột A, B , C, D nhưng nếu tiếng Anh thì Bound column là cột 1, ngược lại là cột 2. Column with thì nếu không chọn sẽ bằng 0, chọn sẽ bằng 200

​

Thêm ngôn ngữ​

Với cách lập trình và tổ chức cơ sở dữ liệu mở, người dùng có thể tự thêm ngôn ngữ thứ 3 mà không cần viết code lại:
Thí dụ thêm ngôn ngữ tiếng Pháp:
Thêm mục chọn tiếng Pháp cho combobox



Thêm cột tự điển:



Kết quả không cần lập trình thêm:






Xem file LanguageSetup.xlsm

File đính kèm tùy chọn ngôn ngữ

 

[ptm0412]

Thủ thuật tùy chọn màu cho form​

Trang trí màu sắc cho form là tôi sẽ không làm nếu là tôi sử dụng. Nếu làm cho người khác sử dụng và nếu họ yêu cầu màu, tôi sẽ có những bộ theme màu cho họ chọn, còn tôi xài thì sẽ chọn theme mặc định.

Thí dụ tôi có sẵn các theme như sau và họ chọn Light Yelow:



Form sẽ chuyển màu theo theme đã chọn



Hoặc chọn màu chói chang Romantic Violet, kệ họ. Họ dùng họ chói mắt chứ tôi không chói.



Khi nhấn nút Gắn thiết lập (Apply theme), tất cả các form sẽ có cùng 1 theme màu

Code
Private Sub ApplyAll_Click()
If ThemeList = "" Then
 MsgBox "You should select a theme", , "ptm0412"
 ThemeList.SetFocus
 Exit Sub
Else
 Sheet2.[B2] = ThemeList.Column(0)
End If
Unload Me
End Sub

Tên theme chọn được gắn xuống ô B2, sau này sẽ là Current theme

Dữ liệu lưu trữ các theme có sẵn​

Lưu trên sheet. Lưu trữ màu dạng hex cho từng loại control. Bắt Excel lưu trữ chứ nhớ làm chi.

Thủ tục dùng chung cho mọi form​

Viết trong module:

Sub ApplyColor(SForm)
Dim Ctrl, ThemeRng, MyUserTheme
MyUserTheme = Sheet2.[B2].Value ‘Tên theme hiện hành
Set ThemeRng = Sheet6.Range("J3:j1000").Find(MyUserTheme).Resize(1, 15) ''tìm tên theme trong bảng theme,
        ''lấy ra các màu của từng control, sau đó gắn màu cho các control.
With SForm
     .BackColor = ThemeRng.Cells(1, 2)
    For Each Ctrl In .Controls
         If TypeOf Ctrl Is MSForms.CommandButton Then
             Ctrl.BackColor = ThemeRng.Cells(1, 3)
             Ctrl.ForeColor = ThemeRng.Cells(1, 4)
         ElseIf TypeOf Ctrl Is MSForms.TextBox Then
             Ctrl.BackColor = ThemeRng.Cells(1, 5)
             Ctrl.ForeColor = ThemeRng.Cells(1, 6)
        ElseIf TypeOf Ctrl Is MSForms.Combobox Then
            Ctrl.BackColor = ThemeRng.Cells(1, 7)
            Ctrl.ForeColor = ThemeRng.Cells(1, 8)
         ElseIf TypeOf Ctrl Is MSForms.ListBox Then
             Ctrl.BackColor = ThemeRng.Cells(1, 9)
             Ctrl.ForeColor = ThemeRng.Cells(1, 10)
         ElseIf TypeOf Ctrl Is MSForms.Label Then
             Ctrl.BackStyle = 0
            Ctrl.ForeColor = ThemeRng.Cells(1, 11)
         ElseIf TypeOf Ctrl Is MSForms.Frame Then
             Ctrl.BackColor = ThemeRng.Cells(1, 12)
             Ctrl.ForeColor = ThemeRng.Cells(1, 13)
         ElseIf TypeOf Ctrl Is MSForms.OptionButton Then
             Ctrl.ForeColor = ThemeRng.Cells(1, 14)
            Ctrl.BackStyle = 0
         ElseIf TypeOf Ctrl Is MSForms.CheckBox Then
             Ctrl.ForeColor = ThemeRng.Cells(1, 15)
             Ctrl.BackStyle = 0
         End If
    Next
End With
End Sub

Áp dụng cho mọi form​

Mỗi form viết thêm trong thủ tục initialize 1 câu lệnh duy nhất ApplyColor Me

 

[ptm0412]

Tự tạo theme màu​

Mục số 7 ta tạo sẵn một số theme (bộ màu) cho người dùng chọn. Nếu người dùng không thích những theme màu đó, ta tạo cơ hội cho họ tự tạo theme riêng.

Bảng màu​

Lưu 1 bảng màu tại 1 sheet, đã phân loại theo tone màu, mỗi tone màu phân ra 3 loại sáng, trung bình và đậm (bright, medium và dark).

Tạo form New theme​

Form tạo mới theme: cho chọn tone màu, chọn độ sáng (brightness) cho màu nền, màu chữ. Cho phép chọn màu nền màu chữ cho từng loại controls, sau đó lưu lại vào bảng theme màu ở mục 7.
Form này được mở từ form chọn theme thiết lập màu.
Sau khi lưu là đã có thể chọn theme mới và thiết lập cho mọi form như những theme làm sẵn.

Code​

Sau khi chọn tone, chọn độ sáng cho nền và độ sáng cho chữ, thì tất cả combobox chọn màu đều được trỏ đến đúng tone và độ sáng đó.

Thí dụ chọn tone Blue, độ sáng nền là Bright và độ sáng chữ là Dark



Người dùng có quyền chọn ngược lại nền tối chữ sáng, kệ họ.



Việc thay đổi rowsource của các combobox thực hiện bằng thủ tục ChangeSource:



Code changeSource dựa vào:

• Việc đặt tên controls có quy tắc (có chứa chữ “Back” hoặc chữ “Fore”)
• Name động ColorList và ForeColorList dựa vào giá trị 2 combobox Brightness đã lưu xuống sheet trước đó.
• Việc đặt tên controls có quy tắc (có chứa chữ “Back” hoặc chữ “Fore”)
• Name động ColorList và ForeColorList dựa vào giá trị 2 combobox Brightness đã lưu xuống sheet trước đó.

Lưu:
Dùng mảng ghi lại mỗi chọn lựa và khi nhấn “Lưu” sẽ ghi xuống bảng theme.
Nếu hủy thì đóng form, không làm gì cả

Xóa theme​

Trên form thiết lập màu có 1 nút xóa theme, chỉ chọn và xóa. Nhấn nút xóa thì sẽ xóa 1 dòng trong bảng theme. Không cảnh báo, lỡ xóa ráng chịu.



Code:



Xem file đính kèm ColorSetup.xlsm, sheet Menu mẫu

 

[ptm0412]

Loạt bài giới thiệu User form Excel đến đây là tạm ngưng. Nếu tạo user form đúng theo quy trình thì mọi việc sẽ dễ dàng hơn nhiều. Ngoài ra áp dụng được những thủ thuật đặc biệt sẽ làm cho code được tinh giản, dễ kiểm soát, sửa chữa.
Bài viết thiên về lý thuyết nên các file mẫu chưa hoặc không dùng được ngay, chỉ dùng để minh họa vấn đề. Người đọc phải chú ý trọng tâm vấn đề đang được trình bày, để mở rộng và áp dụng vào công việc của mình. Sao chép code nguyên xi để dùng cho form khác là không nên, và nếu sinh ra lỗi tôi không chịu trách nhiệm sửa.

Tóm tắt các thủ thuật:
- Viết thủ tục và hàm dùng chung, càng nhiều càng tốt, tuy nhiên không lạm dụng cho code quá đơn giản dùng chỉ ít lần.
- Tạo user form đa nhiệm, control đa chức năng một cách linh hoạt và thuần thục. Trong đó sẽ phải viết code cho các control phụ thuộc vào nhau.
- Tận dụng sheet Excel để: lưu trữ các biến, lập name động, name phụ thuộc, tình trạng hiện tại, ... thay vì dùng code
- Nếu có nhiều cách lập trình cho 1 vấn đề, hãy chọn cách đơn giản mà hiệu quả thay vì dùng cách cao siêu, hàn lâm.
- Ràng buộc trách nhiệm người dùng, buộc họ phải cẩn thận khi khi nhấn nút, hoặc chặn không cho nhấn nút tầm bậy.
- Ngăn chặn lỗi trước khi xảy ra: ẩn các controls, disable các control nhập liệu khi không muốn sử dụng, đặc biệt dấu những nút lệnh khác khi đang dùng chức năng này. Đừng để người dùng gõ hoặc nhấn tầm bậy rồi phải thông báo, mắng mỏ.
- Cho người dùng tham gia vào việc thiết lập, và họ tự chịu trách nhiệm khi làm sai mặc dù đã chặn lỗi phần lớn. Đặc biệt: dữ liệu phải mở (có khả năng co giãn) và code phải có tính mở (linh hoạt), khi người dùng thêm bớt trong phạm vi cho phép thì không phải viết code lại.

Mong rằng loạt bài này hữu ích cho người đọc, nếu có ý kiến thêm xin vui lòng cho biết.

 

[ptm0412]

Vẫn còn thấy ngứa tay nên viết tiếp

Tìm kiếm khi nhập liệu​

Đã có nhiều bài viết và nhiều form nhập liệu mẫu cho việc tìm kiếm nhanh khi nhập liệu. Thay vì xổ xuống chọn trong 1 combobox có chứa 1 danh sách rất dài, người ta dùng 1 textbox gõ những ký tự gợi nhớ, danh sách xổ xuống sẽ lọc ngắn lại để tìm nhanh. Gõ càng nhiều ký tự thì danh sách càng ngắn.

Tìm kiếm đa chức năng​

Sau đây là 1 trong các cách nói trên: khi gõ vào textbox sẽ xuất hiện 1 list chọn ngắn dần. Tuy nhiên, nhớ lại việc sử dụng control đa chức năng, ta chỉ dùng 1 listbox cho việc tìm kiếm 2 thông tin. Nên code phải viết lại.

Vẽ form​

Vẽ form với 2 textbox tìm kiếm khách hàng/ mặt hàng và chỉ 1 listbox ẩn, listbox bỏ trống nhiều properties để gắn sau bằng code. Chiều cao listbox chỉ cần tối thiểu nhìn thấy khi vẽ và lập trình. Các controls khác vẽ bình thường.

Lập trình​

Sử dụng các sự kiện KeyDown và KeyUp vì sẽ dùng đến các phím điều khiển Down arrow, Up arrow, Enter, Esc

Lưu ý đọc code kỹ vì có những câu lệnh chỉ thực thi khi Key Up, không thực thi trong KeyDown và ngược lại. Để phân biệt khi nào thì áp dụng chức năng tìm kiếm khách hàng, khi nào tìm kiếm mặt hàng thì khai báo 1 biến public FilterType, bằng 1 khi tìm khách hàng và bằng 2 khi tìm mặt hàng. Sự kiện của Listbox sẽ ứng xử khác nhau với giá trị khác nhau này của FilterType.

Code cho sự kiện khi gõ các ký tự của textbox tìm kiếm khách hàng (sự kiện KeyUp)

Private Sub txtCustomer_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim ListKH()
FilterType = 1
ListKH = Sheet1.Range("KH").Value
With LbFilter
 .Clear
 .Visible = True
 .Height = 150
 .ColumnWidths = "150"
 .ListWidth = 100
 .ColumnCount = 1
 .Top = 50
 For i = 1 To UBound(ListKH, 1)
 If LCase(ListKH(i, 1)) Like "*" & LCase(txtCustomer) & "*" Then
 .AddItem ListKH(i, 1
 End If
 Next
End With
End Sub

Gán FilterType = 1, cho xuất hiện Listbox và gán các thuộc tính tương ứng với danh sách khách hàng. Sau đó lọc mảng danh sach lấy các dữ liệu khách hàng có chứa các ký tự vừa gõ.



Đối với textbox tìm kiếm mặt hàng cũng tương tự nhưng gán FilterType = 2



Code cho sự kiện bắt đầu chọn khi đang đứng ở textbox tìm kiếm (sự kiện KeyDown)

Private Sub txtCustomer_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
 If KeyCode = 40 Then 'down arrow
 LbFilter.SetFocus
 LbFilter.Value = LbFilter.List(0, 0)
 End If
End Sub

Nhấn phím mũi tên xuống, chọn dòng đầu tiên trong listbox.



Code cho việc di chuyển để chọn, thực chọn trong listbox (sự kiện KeyUp)

Private Sub LbFilter_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer
If FilterType = 1 Then
 If KeyCode = 13 Then 'Enter
 txtCustomer.Value = LbFilter.Value
 LbFilter.Height = 0
 TxtProduct.SetFocus
 ElseIf KeyCode = 40 Or KeyCode = 38 Then 'down Arrow or up arrow
 txtCustomer.Value = LbFilter.Value
 ElseIf KeyCode = 27 Then 'Escape
 txtCustomer = ""
 txtCustomer.SetFocus
 End If
Els
…

Nhấn mũi tên di chuyển, chọn tạm thời và cho kết quả chọn vào textbox tìm kiếm
Nhấn enter để thực chọn, gắn kết quả chọn vào textbox tìm kiếm, và ẩn listbox
Nhấn Esc để chọn lại từ đầu



Có thể thêm sự kiện double click để chọn trong listbox. Chia ra 2 trường hợp FilterType = 1 và 2

Xem file đính kèm SeachForInput.xlsm

 

[DuLieuHangTrieuDong]

Mong anh sớm viết thêm phần listbox hơn 10 cột nữa.

 

[ptm0412]

Lọc listbox nhiều cột​

Lọc 1 cột​

Tôi đã thấy nhiều form có thiết kế lọc listbox theo từ khóa, gõ vài ký tự vào 1 textbox, gõ đến đâu list được lọc ngắn đến đó.
Đại khái là vầy: Gõ tìm kiếm khách hàng 1 ký tự f



Gõ tiếp r thành fr



Gõ tiếp fr*2 . Lưu ý có thể dùng dấu * trong search key



Như vậy cũng chỉ lọc được 1 cột khách hàng, không lọc theo cột khác.

Lọc nhiều cột​

Để có thể tùy chọn cột để lọc, ta tạo 1 combobox chọn cột, viết code sao cho chọn cột nào lọc cột đó.



Và cũng chỉ dùng 1 textbox Search key mà thôi (hãy nhớ lại control đa chức năng)

Thực hiện​

Liệt kê các cột muốn lọc thành 2 cột dữ liệu, đặt name là “ColumnName”. Cột F là tên cột, cột G là số thứ tự cột kể từ cột A. Dùng name này làm RowSource cho combobox CbbFields.



Code: Nếu chọn tên cột là Customer, thì thứ tự cột là 5, code sẽ so sánh dữ liệu cột 5 với key tìm kiếm. Chọn cột khác thì thứ tự cột khác, code lại tìm đúng cột. Code cũng đa chức năng, không phải viết nhiều lần. Thủ tục lọc như sau:

If CbbFields <> “” Then Field = CbbFields.Column(1) Else Field = 1
 Sarr = Sheet1.Range(“Data”).Value
 ReDim Tmp(1 To Ubound(Sarr, 1), 1 To 15)
 For i = 1 To Ubound(Sarr, 1)
 If Ucase(Sarr(i, Field)) Like “*” & Ucase(txbSearch) & “*” And Sarr(i, 13) <> “x” Then
 k = k + 1
 Tmp(k, 1) = i + 1
 Tmp(k, 2) = k
 For j = 1 To 13
 Tmp(k, j + 2) = Sarr(i, j)
 Next
 TotalRev = TotalRev + Sarr(i, 11)
 TotalPaymt = TotalPaymt + Sarr(i, 12)
 End I
 Next

Kết quả lọc sẽ ít dòng có dữ liệu và 1 đống dòng trống, nên không gán ngay vào Listbox, mà chạy qua mảng trung gian ít cột hơn, sau đó mới gắn vào listbox:

If k > 0 Then
 ReDim ListArr(1 To k, 1 To 15)
 For i = 1 To k
   For j = 1 To 1
     ListArr(i, j) = Tmp(i, j)
   Next
 Next
 ListBox1.Clear
 ListBox1.List = ListArr

Lọc nhiều điều kiện​

Như mục trên, nếu người dùng muốn lọc 2 hay nhiều điều kiện sẽ không được, thí dụ lọc khách hàng trong tháng 4, hoặc lọc chỉ những dòng thu tiền khách hàng theo nhân viên bán hàng Duy trong tháng 7.

Muốn vậy ta sẽ tách ra 2 trường hợp:

• Lọc riêng rẽ từng lần, mỗi lần chỉ lọc 1 tiêu chí,
• trường hợp 2 là lọc luân phiên: lọc 1 tiêu chí đầu tiên, lấy kết quả đem lọc lần nữa theo tiêu chí 2, rồi có thể lọc tiếp tiêu chí 3, v.v.

Thực hiện​

Tạo 1 frame với 2 option button để chọn FilterType



Nếu FilterType = 1 thì lấy data nguyên gốc từ dưới sheet để lọc như phần trên. Nếu FilterType = 2 thì lấy kết quả từ Listbox (là kết quả lọc lần trước), làm dữ liệu nguồn để lọc lần sau (tiêu chí sau).

Code:

ReDim Tmp(1 To ListBox1.ListCount + 1, 1 To 15)
 For i = 0 To UBound(ListBox1.List, 1)
   If UCase(ListBox1.List(i, Field + 1)) Like "*" & UCase(txbSearch) & "*" And
     ListBox1.List(i, 14) <> "x" The
     k = k + 1
     Tmp(k, 1) = ListBox1.List(i, 0)
     Tmp(k, 2) = k
     For j = 3 To 15
       Tmp(k, j) = ListBox1.List(i, j - 1
     Next
 End If
 Next

Kết quả​

Lọc khách hàng FOOD



Lọc tiếp FOOD tháng 5



Lọc tiếp thu tiền FOOD tháng 5



Xem file FilterMultiColumns.xlsm

Mong anh sớm viết thêm phần listbox hơn 10 cột nữa.

Listbox trong file này 15 cột bao gồm:
13 cột dữ liệu từ A đến M
1 cột ẩn là thứ tự dòng trên sheet. Dùng giá trị này để sửa xóa, giá trị này của từng dòng là không đổi dù cho lọc ngắn lại
1 cột số thứ tự trên listbox, mỗi lần lọc sẽ đánh STT lại.

Đối với listbox nhiều hơn 10 cột:
Nạp list bằng mảng, không nạp bằng AddItem.
Nạp lần đầu là mảng lấy từ giá trị trên sheet
Mỗi lần lọc, xét dữ liệu trên sheet hoặc dữ liệu hiện thời của listbox gán vào 1 mảng tạm, rồi gán mảng tạm vào listbox.

 

[ptm0412]

1. Tại sao tìm kiếm để lọc nhiều cột cần xác định tên cột?
Tôi có thấy 1 file, trên user form chỉ có 1 ô tìm kiếm duy nhất, gõ bất kỳ thứ gì thì code cũng tìm trên tất cả các cột và ra kết quả so khớp trên tất cả các cột. Tôi không làm vậy và khuyên đừng ai làm vậy. Lý do:
- Viết code cực khổ. Code không thông minh đến mức suy đoán người dùng đang muốn tìm thông tin gì, nên phải tìm tất cả các cột.
- Từ khóa tìm kiếm trùng sẽ gây kết quả sai, và người dùng chê code dỏm: chẳng hạn chủ ý tìm công ty có tên Bắc Âu, nhưng khu vực địa lý cũng có khu Bắc Âu. Tìm kiếm tên nhân viên lại ra cả tên nhân viên và tên công ty, mỗi thông tin ra 1 lô các dòng kết quả. Tìm nhóm mặt hàng "thức ăn nhanh" lại ra cả tên 1 đống cửa hàng có tên chứa "Thức Ăn Nhanh"
- Tôi không thích phải đoán yêu cầu. Người dùng muốn gì phải đưa ra yêu cầu 1 cách rõ ràng, có trách nhiệm. Dù làm cho chính mình xài, tôi cũng xác định là mình xài cũng phải có trách nhiệm.
- Cuối cùng, có chọn tên cột mới có thể chọn nhiều lần để lọc luân phiên với filterType là Continue filter

2. Tại sao đã sử dụng tmpArr để lọc lại còn thêm 1 vòng lặp chuyển tmpArr sang ListArr rồi mới đưa vào Listbox?
Do chưa biết kết quả sau khi lọc có bao nhiêu dòng, nên phải Redim tmpArr lên số dòng tối đa của dữ liệu (trường hợp lọc với từ khóa rỗng). Nếu lấy ngay tmpArr cho vào listbox sẽ bị 1 đống dòng trắng ở bên dưới.
Để giải quyết thì còn 1 cách nữa là xoay ngang tmpArr để có thể Redim Preserve. Tuy nhiên cách này có hạn chế:
- với người mới học sẽ khó kiểm soát
- Khi kết quả nhiều quá 1 số lượng dòng/ cột nào đó, khi transpose sẽ bị lỗi
- Khi kết quả trả về chỉ có 1 cột, sau khi transpose sẽ biến thành 1 dòng, nhưng lại là mảng 1 chiều. Mảng 1 chiều thì không gán trực tiếp được cho listbox và lại phải code thêm cho trường hợp này.

3. Tại sao dùng textbox mà không dùng combobox cho việc nhập liệu với danh sách:
- Vì tôi có sử dụng chức năng search cho 3 trường này: khách hàng, mặt hàng và tên nhân viên kinh doanh
- Tôi có thể đặt tên 12 textbox nhập liệu là txtb1 đến txtb12. Khi cần gán dữ liệu cho chúng (khi click listbox), hoặc khi cần lấy dữ liệu của chúng gán xuống sheet (khi sửa hoặc tạo mới dữ liệu), chỉ cần viết 1 vòng lặp for i và sử dụng Controls("txtb" & i), thay vì viết 12 dòng lệnh.

bạn ơi có thể giúp mình thêm 1 cột thời gian nhập ko ạ. ví dụ là nếu cột A mà có dữ liệu mới thì ở một cột khác hiện thời gian mình nhập, nhưng sẽ không bị thay đổi cho dù có xóa dòng phía trên

Bạn đọc thật kỹ code sẽ thấy có những dòng lệnh gắn "x" (xóa) và "s" (sửa) xuống sheet dù cho listbox không có giá trị này. Vậy thì bạn tự viết thêm 1 câu lệnh gán Date() xuống 1 cột nào đó của bạn. Nếu vẫn không làm được thì mở chủ đề mới, chủ đề này thuộc dạng lý thuyết chứ không viết theo yêu cầu.

 

[ptm0412]

Phục hồi dữ liệu đã xóa​

Thông thường dữ liệu Excel đã xóa và file đã lưu là không thể phục hồi. Tuy nhiên với file excel này, khi xóa chỉ đánh dấu xóa, nên hoàn toàn có thể phục hồi. Chỉ cần xóa chữ x đã đánh dấu là được.
Khác với code upload dữ liệu và lọc dữ liệu loại bỏ các dòng có chữ x, code lấy dữ liệu để xóa chỉ lấy dòng có chữ x (thủ tục SearchDeleted)



Dữ liệu tương ứng: 3 dấu x ở cột M

Khi

Nhấn Confirm restore, code đơn giản là xóa chữ x ở cột M và load lại các dòng chưa phục hồi.

If ListBox1 <> "" Then Sheet1.Cells(ListBox1.Value, 13) = ""
 SearchDeleted

Nếu phục hồi tiếp thì lại nhấn, nếu không thì nhấn nút finish restore.
Nếu phục hồi hết dữ liệu đã xóa thì cuối cùng sẽ trả về dữ liệu chuẩn.

 

[ptm0412]

Tài liệu pdf và các file thực hành liên quan:

Tài liệu User form Excel: quy trình tạo, thực hiện tạo, các thủ thuật thông dụng và nâng cao

Trên diễn đàn đã có rất nhiều chủ đề hỏi về Userform, cũng như nhiều chủ đề giới thiệu hoặc chia sẻ Userform. Tuy nhiên nói về căn bản tạo dựng userform không phải ai cũng biết, kết quả là userform tạo ra rất hoành tráng, rất đẹp đẽ, nhưng khi...

www.giaiphapexcel.com