Trước tiên mình rất cảm ơn tuhocvba.net luôn là 1 địa chỉ đáng tin cậy để chia sẻ những thắc mắc, chia sẻ những thành quả trong quá trình tự mày mò VBA và để học hỏi những kiến thức rộng lớn của VBA
Bối cảnh hôm nay của mình: Cần tạo các bảng biểu, gồm:
1 bảng để xác định thời gian cho việc trông xe, bảo vệ nhà sách (mình gọi tắt là thời gian Bảo vệ),
và 1 bảng để thông báo số tiền để yêu cầu khách hàng thanh toán.
Các quy tắc cơ bản và yêu cầu công việc:
+Quy tắc Xác định thời gian bảo vệ: 1 ngày chia 2 ca (ngày 14 h, đêm 10h) ,Thời gian bảo vệ 1 ngày bình thường là 24 tiếng, các ngày nghỉ lễ (ví dụ 30/04,01/05, ngày tết v..v) thời gian bổ sung nhân thêm 2 lần (phần cộng thêm này người làm tự bổ sung theo thực tế), tính tổng thời gian bảo vệ theo tháng
+Quy tắc Xác định số tiền thanh toán: đơn giá chưa tính thuế / 1 h :15.000 đ, Thuế 10%.
+Hàng tháng In ra 3 bản giấy gửi Khách hàng ký xác nhận bảng thời gian và Thông báo tiền
Phương án + ý tưởng:
Nếu như theo khả năng của chính mình với excel cách đây khoảng 2 năm: Thì phương án của mình làm sẽ là tạo ra 1 file xls có 13 sheet, trong đó 12 sheet đầu là đại diện cho 12 tháng và 1 sheet cuối là tính tiền. Và cứ thế đến tháng chốt số thì cập nhập thời gian theo từng sheet tương ứng. Rồi Ctrl+P chọn số lượng bản in, trang cần in.., còn nếu lùi thêm 4 năm trước nữa thì có lẽ là mình đã có tới 12 file xls.
Nhưng vui mừng là trên con đường tới VBA, hôm nay mình đã rút gọn chỉ 1 file với 2 sheet,và các thao tác in đều gán cho 1 Button.
Ý tưởng của mình ở đây là: thay vì tạo ra 12 cái bảng ứng với 12 tháng (số lượng cột của từng bảng phụ thuộc vào tên của từng tháng). Ta chỉ cần tạo 1 bảng, có số lượng cột ngày tối đa là 31 cột. Khi tên tháng thay đổi , mà có số ngày < 31ngày (ví dụ tháng 2,4,6...),lúc đó sẽ ẩn bớt (1 hoặc 3) cột
Cách thức:
Sử dụng VBA tạo bảng xác định thời gian bảo vệ:
Sheet1 (Xác định thời gian bảo vệ) : Tạo Vùng List 12 tháng và sử dụng Datavalidation gán cho 1 ô trong bảng tính
1/Gán điều kiện cho sự thay đổi của 1 ô, nếu ô này thay đổi giá trị thì bảng tính sẽ tự động cập nhập
2/Refress vùng nội dung của bảng tính
3/Sử dụng For...to Step 1 tạo các cập nhập:
+điền ngày bảo vệ từ ngày 1 đến ngày 31,
+điền thời gian bảo vệ theo quy tắc cơ bản cho từng ngày (14h và 10 h)
+điền công thức tính tổng cho từng ngày (24h cho 1 ngày)
3/Sử dụng Tham chiếu Range(Row,Col) tạo công thức cho các cột tính tổng thời gian
4/Gán biến cho tham số cột cần ẩn
Sử dụng VBA gán Button thao tác in 3 bản
Xác định chỉ số trên cùng; dưới cùng của trang cần in
Gán biến cho các chỉ số
Đưa các biến vào Marco in
Sheet2 (Thông báo số tiền) : Sheet này mình chỉ đơn thuần sử dụng DataValidation List kết hợp các hàm nhẹ như Vlookup; hàm nối chuỗi, Copy lại code tạo bản in tương tự như ở Sheet1
Như vậy là với VBA khi áp dụng cho công việc này; mỗi khi đến kỳ cần chốt số với khách hàng thao tác của mình sẽ đơn giản gồm những cú Click chuột, 4 bước tuần tự:
1/ Tại ô M3. Lựa chọn kỳ cần xác định thời gian bảo vệ
2/Điều chỉnh lại thời gian các ngày cho phù hợp với thực tế và các ngày nghỉ lễ
(ví dụ 30/04, 01/05 , 02/09 v...v thời gian nhân đôi)
3/ Sang sheet Thong bao, tại ô A3 Lựa chọn kỳ theo đúng kỳ của sheet 2019
4/ Nếu Cần in Click nút In (3 bản)
Đây là giao diện của 2 sheet tương ứng với bảng thời gian bảo vệ và bảng thông báo số tiền
(Trong lúc làm việc với VBA trên bảng tính này, do mình cũng chưa hiểu lắm nên có 1 đoạn code mình bị mất kha khá thời gian để xử lý, nhưng Do thời gian có hạn, mình sẽ chia sẻ ý này sau)
Chúc các anh, các chị, các bạn cuối tuần vui vẻ
Bối cảnh hôm nay của mình: Cần tạo các bảng biểu, gồm:
1 bảng để xác định thời gian cho việc trông xe, bảo vệ nhà sách (mình gọi tắt là thời gian Bảo vệ),
và 1 bảng để thông báo số tiền để yêu cầu khách hàng thanh toán.
Các quy tắc cơ bản và yêu cầu công việc:
+Quy tắc Xác định thời gian bảo vệ: 1 ngày chia 2 ca (ngày 14 h, đêm 10h) ,Thời gian bảo vệ 1 ngày bình thường là 24 tiếng, các ngày nghỉ lễ (ví dụ 30/04,01/05, ngày tết v..v) thời gian bổ sung nhân thêm 2 lần (phần cộng thêm này người làm tự bổ sung theo thực tế), tính tổng thời gian bảo vệ theo tháng
+Quy tắc Xác định số tiền thanh toán: đơn giá chưa tính thuế / 1 h :15.000 đ, Thuế 10%.
+Hàng tháng In ra 3 bản giấy gửi Khách hàng ký xác nhận bảng thời gian và Thông báo tiền
Phương án + ý tưởng:
Nếu như theo khả năng của chính mình với excel cách đây khoảng 2 năm: Thì phương án của mình làm sẽ là tạo ra 1 file xls có 13 sheet, trong đó 12 sheet đầu là đại diện cho 12 tháng và 1 sheet cuối là tính tiền. Và cứ thế đến tháng chốt số thì cập nhập thời gian theo từng sheet tương ứng. Rồi Ctrl+P chọn số lượng bản in, trang cần in.., còn nếu lùi thêm 4 năm trước nữa thì có lẽ là mình đã có tới 12 file xls.
Nhưng vui mừng là trên con đường tới VBA, hôm nay mình đã rút gọn chỉ 1 file với 2 sheet,và các thao tác in đều gán cho 1 Button.
Ý tưởng của mình ở đây là: thay vì tạo ra 12 cái bảng ứng với 12 tháng (số lượng cột của từng bảng phụ thuộc vào tên của từng tháng). Ta chỉ cần tạo 1 bảng, có số lượng cột ngày tối đa là 31 cột. Khi tên tháng thay đổi , mà có số ngày < 31ngày (ví dụ tháng 2,4,6...),lúc đó sẽ ẩn bớt (1 hoặc 3) cột
Cách thức:
Sử dụng VBA tạo bảng xác định thời gian bảo vệ:
Sheet1 (Xác định thời gian bảo vệ) : Tạo Vùng List 12 tháng và sử dụng Datavalidation gán cho 1 ô trong bảng tính
1/Gán điều kiện cho sự thay đổi của 1 ô, nếu ô này thay đổi giá trị thì bảng tính sẽ tự động cập nhập
Mã:
'Gan dieu kien neu M3 thay doi gia tri
If Not Intersect(Range("M3"), Target) Is Nothing Then
Mã:
'Refress Xoa du lieu C6:AH10, Unhide cac cot tu A:AK
Range("c6:Ah10").ClearContents
Columns("A:AK").Select
Range("AK1").Activate
Selection.EntireColumn.Hidden = False+điền ngày bảo vệ từ ngày 1 đến ngày 31,
+điền thời gian bảo vệ theo quy tắc cơ bản cho từng ngày (14h và 10 h)
+điền công thức tính tổng cho từng ngày (24h cho 1 ngày)
Mã:
For i = 3 To 33 Step 1
Cells(6, i).Value = i - 2 ' Dien so ngay tu 1 - 31 bat dau tu (C6) den (AG6)
Cells(7, i).Value = 14 ' Gan gia tri 14 bat dau tu (C7) den (AG7)
Cells(8, i).Value = 10 ' Gan gia tri 10 bat dau tu (C8) den (AG8)
Cells(10, i).FormulaR1C1 = "=R[-3]C+R[-2]C+R[-1]C" ' Gan cong thuc tu (C10) den (AG10)
Next i
Mã:
Range("AH6").Value = "T" & ChrW(7893) & "ng s" & ChrW(7889) & " gi" & ChrW(7901) 'Viet noi dung cho AH6'
Range("AH7").Formula = "=sum(C7:AG7)" 'Gan cong thuc sum cho AH7
Range("AH8").Formula = "=sum(C8:AG8)" 'Gan cong thuc sum cho AH8
Range("AH9").Formula = "=sum(C9:AG9)" 'Gan cong thuc sum cho AH9
Range("AH10").Formula = "=sum(C10:AG10)" 'Gan cong thuc sum cho AH10
Mã:
'Xac dinh cot hide
a = Range("AM1").Value
b = Range("AN1").Value
'Xoa du lieu va Hide cac cot khai bao bien a, b
Range(a & "6:" & b & "10").Select
Selection.ClearContents
Columns(a & ":" & b).Hidden = TrueXác định chỉ số trên cùng; dưới cùng của trang cần in
Gán biến cho các chỉ số
Đưa các biến vào Marco in
Mã:
Dim t1 As Integer
Dim t2 As Integer
'Code in trang theo khai bao bien t1,t2
t1 = Range("I1").Value 'o dau tien cua trang can in
t2 = Range("I2").Value 'o cuoi cung cua trang can in
'Gan bien t1, t2 vao macro Print cua VBA
Range(Cells(t1, 1), Cells(t2, 7)).PrintOut , Copies:=3, Preview:=True, Collate _
:=TrueNhư vậy là với VBA khi áp dụng cho công việc này; mỗi khi đến kỳ cần chốt số với khách hàng thao tác của mình sẽ đơn giản gồm những cú Click chuột, 4 bước tuần tự:
1/ Tại ô M3. Lựa chọn kỳ cần xác định thời gian bảo vệ
2/Điều chỉnh lại thời gian các ngày cho phù hợp với thực tế và các ngày nghỉ lễ
(ví dụ 30/04, 01/05 , 02/09 v...v thời gian nhân đôi)
3/ Sang sheet Thong bao, tại ô A3 Lựa chọn kỳ theo đúng kỳ của sheet 2019
4/ Nếu Cần in Click nút In (3 bản)
Đây là giao diện của 2 sheet tương ứng với bảng thời gian bảo vệ và bảng thông báo số tiền
Bạn cần đăng nhập để thấy link
Bạn cần đăng nhập để thấy link
Mình gửi link file ở đây:
Bạn cần đăng nhập để thấy link
Mình tạo bài viết này hoàn toàn với mục đích chia sẻ và cũng rất mong nhận được thêm góp ý từ các thành viên.(Trong lúc làm việc với VBA trên bảng tính này, do mình cũng chưa hiểu lắm nên có 1 đoạn code mình bị mất kha khá thời gian để xử lý, nhưng Do thời gian có hạn, mình sẽ chia sẻ ý này sau)
Chúc các anh, các chị, các bạn cuối tuần vui vẻ
Sửa lần cuối:
