Private Sub Worksheet_...() Ý nghĩa,Tác dụng và cách dùng (2 người xem)

boyxin · 28/8/10

Chào các bác!

Em muốn hỏi về: Private Sub Worksheet_...() Ý nghĩa,Tác dụng và cách dùng

PHP:

1) Private Sub Worksheet_Activate()
            Chọn Sheet
End Sub
--------------------------------------------------
2) Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub
--------------------------------------------------
3) Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

End Sub
--------------------------------------------------
4) Private Sub Worksheet_Calculate()

End Sub
--------------------------------------------------
5) Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
--------------------------------------------------
6) Private Sub Worksheet_Deactivate()

End Sub
--------------------------------------------------
7) Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

End Sub
--------------------------------------------------
8) Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub
--------------------------------------------------
9) Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
--------------------------------------------------

10) Còn Private Sub Worksheet_...() nào nữa không ?

Nói ra thì thật là xấu hổ, sau bao nhiêu ngày viết CODE, trợ giúp không ít thành viên (viết, chỉnh sửa CODE) nhưng toàn là chế biến từ sản phầm ;;;;;;;;;;;

Tools\Macro\Record New Macro...

Trong các Private Sub Worksheet_...() em nêu ở trên, em chỉ võ vẽ biết chút sơ sơ chút ít thôi, nên em muốn hỏi, Nhờ các bác giúp đỡ để em có thể vận dụng chúng linh hoạt trong các trường hợp cụ thể.

Một lần nữa rất mong các bác chỉ bảo tận tình. Em xin chân thành cảm ơn!

ndu96081631 · 29/8/10

boyxin đã viết:
Chào các bác!

Em muốn hỏi về: Private Sub Worksheet_...() Ý nghĩa,Tác dụng và cách dùng

PHP:

1) Private Sub Worksheet_Activate() Chọn Sheet End Sub -------------------------------------------------- 2) Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) End Sub -------------------------------------------------- 3) Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) End Sub -------------------------------------------------- 4) Private Sub Worksheet_Calculate() End Sub -------------------------------------------------- 5) Private Sub Worksheet_Change(ByVal Target As Range) End Sub -------------------------------------------------- 6) Private Sub Worksheet_Deactivate() End Sub -------------------------------------------------- 7) Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) End Sub -------------------------------------------------- 8) Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) End Sub -------------------------------------------------- 9) Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub -------------------------------------------------- 10) Còn Private Sub Worksheet_...() nào nữa không ?

Nói ra thì thật là xấu hổ, sau bao nhiêu ngày viết CODE, trợ giúp không ít thành viên (viết, chỉnh sửa CODE) nhưng toàn là chế biến từ sản phầm Tools\Macro\Record New Macro...

Trong các Private Sub Worksheet_...() em nêu ở trên, em chỉ võ vẽ biết chút sơ sơ chút ít thôi, nên em muốn hỏi, Nhờ các bác giúp đỡ để em có thể vận dụng chúng linh hoạt trong các trường hợp cụ thể.

Một lần nữa rất mong các bác chỉ bảo tận tình. Em xin chân thành cảm ơn!

Mấy cái này người ta gợi là SỰ KIỆN
Worksheet_Activate là sự kiện Activate ---> Khi ta chọn vào sheet này thì code trong nó sẽ hoạt động
Worksheet_BeforeDoubleClick là sự kiện DoubleClick chuột (click đôi) ---> Khi ta double click vào 1 cell (Target) thì code trong nó sẽ hoạt động
Worksheet_BeforeRightClick là sự kiện click phải chuột ---> Khi ta click phải chuột vào 1 cell (Target) thì code trong nó sẽ hoạt động
Worksheet_Calculate là sự kiện tính toán ---> Khi sheet tính toán lại giá trị các công thức thì code trong nó sẽ hoạt động
Worksheet_Change là sự kiện thay đổi ---> Khi ta thay đổi giá trị của 1 cell (Target) thì code trong nó sẽ hoạt động
vân vân ....
Nói chung cái tên tiếng Anh của nó cũng nói lên được ý nghĩa và tác dụng của nó rồi
------------------
Muốn biết trong sheet có những sự kiện nào thì... xem hình:

ptm0412 · 29/8/10

Không ngờ Boyxin lại hỏi vấn đề này? Thấy viết code ầm ầm mà?

Nói từng mục một nhé! À không, trước tiên phải nói 1 cái gì chung chung chứ nhỉ.

Khi viết code VBA ứng dụng cho 1 việc gì đó, ta muốn rằng chỉ khi nào đáp ứng 1 điều kiện nào đó mới chạy code. Thí dụ
- Khi cho dữ liệu vào chỗ này, chạy code để nhét 1 hoặc nhiều dữ liệu tương ứng vào chỗ kia
- Khi vừa chuyển từ sheet đang đứng sang sheet A (đã xác định) thì chạy code
- Chỉ cần click chuột hoặc dùng phím mũi tên thay đổi ô hiện hành là chạy code
- Không cho người dùng nhấn chuột phải, hoặc khi người dùng nhấn chuột phải sẽ chạy code
- Double-Click 1 ô nào đó sẽ chạy code
- Khi xoá dữ liệu, hoặc xoá dòng, cột, thì chạy code
- Khi nhấn vào 1 ô chứa hyperlink đến sheet khác, file khác, ... thì chạy code

Với mỗi yêu cầu như vậy, ta phải chọn 1 trong các sự kiện thích hợp để đưa code cần chạy vào. Thí dụ:
- Các điều kiện chạy code liên quan đến bất cứ thay đổi nào trên sheet như thêm dữ liệu, xoá dữ liệu, thêm dòng, xoá dòng, ... Cho vào sự kiện Worksheet_Change
- Các điều kiện chạy code liên quan đến thay đổi ô chọn như click chuột vào vùng này, hoặc dùng phím mũi tên di chuyển ô chọn vao vùng kia, cho vào sự kiện Worksheet_SelectionChange
- Code chỉ chạy khi chuyển đến sheet A, thì đưa code vào sự kiện Worksheet_Activate của sheet A
- Code chạy trước khi chuyển từ sheet A đến sheet khác, thì đưa vào sự kiện Worksheet_Deactivate của sheet A
- ....

Phân nhóm các sự kiện:

1. Nhóm sự kiện chỉ có chạy code mà không liên quan đến tham chiếu hoặc vùng tác động:

- Sự kiện Activate và Deactivate Worksheet: Hễ thay đổi sheet chọn (selection) liên quan đến sheet A là chạy.
- Sự kiện tính toán của anh Bill trên sheet (Worksheet_Calculate), hễ anh Bill tính lại, là chạy code

2. Nhóm sự kiện liên quan đến tham chiếu hoặc vùng tác động: Có tham chiếu ByVal Target as Range:
Target là ô đang được tác động. Với các sự kiện này ta có thể đặt điều kiện chỉ khi tác động đến các ô hoặc 1 vùng cụ thể nào đó trên sheet mới chạy code, ngoài vùng đó thì không. Vậy ta đặt điều kiện cho Target thí dụ như:

PHP:

If Target.Column = 2 Then
'Chỉ khi có tác động đến cột B mới chạy code'

Nhóm này gồm có: Worksheet_Change, Worksheet_SelectionChange,Worksheet_FollowHyperlink, Worksheet_PivotTableUpdate

3. Nhóm sự kiện không chỉ cần tác động lên 1 ô, mà còn có thể huỷ lệnh mặc định của Excel tương ứng tác động đó: Có cả ByVal Target as Range và Cancel As Boolean
- Gồm có Worksheet_BeforeDoubleClick và Worksheet_BeforeRightClick
- Nếu muốn huỷ lệnh thì gán Cancel = True, sẽ huỷ lệnh. (Mặc định Cancel = False)

Thí dụ Right click 1 ô sẽ hiện 1 menu con để chọn lệnh trong menu. Đoạn code sau không cho hiện menu RightClick để format cell hay làm bất cứ cái gì trong menu con khi RightClick ô trong vùng A1:B10:

PHP:

Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
    MsgBox "Muon lam gi do? Không cho lam dau!"
    Cancel = True
End If
End Sub

Thí dụ 2: Khi doubleClick lên 1 ô, có 2 trường hợp xảy ra:
- Nếu trong Option đánh dấu chọn Edit Directly in Cell, sẽ có dấu nháy trong ô để edit cell đó
- Nếu trong Option bỏ, không đánh dấu chọn Edit Directly in Cell, sẽ nhảy đến ô/ các ô nguồn dùng tính toán cho ô, nếu ô chứa công thức. Chuyện này bảo đảm ít người biết.

Nhưng nếu ta dùng đoạn code sau, khi doubleClick trong vùng A1:B10, sẽ thực thi 1 công việc mà ta muốn, mà không thực thi 1 trong 2 việc trên:

PHP:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1:B10")) Is Nothing Then
    MsgBox "Muon lam gi do?"
    Cancel = True
End If
End Sub

vnlinhtinh · 23/11/17

Mình không biết gì về VBA hay code gì cả, nhưng do công việc nên mình tìm được đoạn code này dùng để ẩn dòng trong excel.
Đọc ở trên thì thấy nó là loại mà phải chuyển đến sheet nó mới chạy. Giờ nhờ các bác sửa sao cho khi gõ số vào ô N6 xong thì nó tự chạy.

Private Sub Worksheet_Activate()
Dim rng As Range
Application.ScreenUpdating = False
For Each rng In [O6:O134]
If rng.Value = "A" Or rng.Value = 0 Then
rng.EntireRow.Hidden = True
Else
rng.EntireRow.Hidden = False
End If
Next rng
Application.ScreenUpdating = True
End Sub

Cám ơn nhiều

phulien1902 · 23/11/17

vnlinhtinh đã viết:
Mình không biết gì về VBA hay code gì cả, nhưng do công việc nên mình tìm được đoạn code này dùng để ẩn dòng trong excel.
Đọc ở trên thì thấy nó là loại mà phải chuyển đến sheet nó mới chạy. Giờ nhờ các bác sửa sao cho khi gõ số vào ô N6 xong thì nó tự chạy.

Private Sub Worksheet_Activate()
Dim rng As Range
Application.ScreenUpdating = False
For Each rng In [O6:O134]
If rng.Value = "A" Or rng.Value = 0 Then
rng.EntireRow.Hidden = True
Else
rng.EntireRow.Hidden = False
End If
Next rng
Application.ScreenUpdating = True
End Sub

Cám ơn nhiều

Thay đổi 1 chút là được:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Application.ScreenUpdating = False
    If Target.Address = "$N$6" Then
        For Each rng In [O6:O134]
            If rng.Value = "A" Or rng.Value = 0 Then
                rng.EntireRow.Hidden = True
            Else
                rng.EntireRow.Hidden = False
            End If
        Next rng
    End If
    Application.ScreenUpdating = True
End Sub

Theo tôi bạn nên đặt 1 Cell khác N6, bạn biết vì sao không?

vnlinhtinh · 23/11/17

phulien1902 đã viết:
Thay đổi 1 chút là được:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Application.ScreenUpdating = False If Target.Address = "$N$6" Then For Each rng In [O6:O134] If rng.Value = "A" Or rng.Value = 0 Then rng.EntireRow.Hidden = True Else rng.EntireRow.Hidden = False End If Next rng End If Application.ScreenUpdating = True End Sub

Theo tôi bạn nên đặt 1 Cell khác N6, bạn biết vì sao không?

Cám ơn phulien1902 nhiều nhiều!
Câu hỏi trên chắc không liên quan đến code

.
Có lẽ bạn thấy khi chạy có thể ẩn luôn cả cái ô N6 do cùng hàng với tụi kia hử?

vnlinhtinh · 30/11/17

[NHỜ VẢ TIẾP] -0-0-0-

Nhờ bạn đưa đoạn code dãn dòng dưới đây chạy chung với cái code N6 bên trên giùm!
Cám ơn nhiều.

Sub Macro1()
Columns("P:W").Select
Selection.Rows.AutoFit
ActiveSheet.Range("$O$5:$O$134").AutoFilter Field:=1, Criteria1:="H"
End Sub

Hoặc có cách nào khác để dãn một số dòng thuộc cột P:W

phulien1902 · 30/11/17

vnlinhtinh đã viết:
[NHỜ VẢ TIẾP]
Nhờ bạn đưa đoạn code dãn dòng dưới đây chạy chung với cái code N6 bên trên giùm!
Cám ơn nhiều.

Sub Macro1()
Columns("P:W").Select
Selection.Rows.AutoFit
ActiveSheet.Range("$O$5:$O$134").AutoFilter Field:=1, Criteria1:="H"
End Sub

Hoặc có cách nào khác để dãn một số dòng thuộc cột P:W

Bạn thử:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Application.ScreenUpdating = False
    If Target.Address = "$N$6" Then
        For Each rng In [O6:O134]
            If rng.Value = "A" Or rng.Value = 0 Then
                rng.EntireRow.Hidden = True
            Else
                rng.EntireRow.Hidden = False
            End If
        Next rng
    End If
    Columns("P:W").Rows.AutoFit
   Range("$O$5:$O$134").AutoFilter Field:=1, Criteria1:="H"
    Application.ScreenUpdating = True
End Sub

vnlinhtinh · 1/12/17

Cảm ơn phulien1902 đã giúp.
Đoạn code trên mình lắp vào chạy tốt, đáp ứng được đề bài.
Theo ý hiểu của Mình thì khi ô N6 biến đổi thì code sẽ chạy. Mình gán cho ô N6 đó nút Spin button nhưng khi bấm nút Spin button để đổi số ở ô N6 thì hình như code không chạy mà nó thực thi lệnh cũ (lệnh này mình tạo bằng record Macro).
Hi hi hi diễn đạt khó quá ......

thôi mình up cái file đó nhờ phulien1902 kiểm tra giùm.
Mục tiêu (co dãn dòng và ẩn dòng) là khi bấm Spin button hay gõ số ở ô N6 thì nó thực thi cùng một chương trình để tránh xung đột (nếu có).
P/s: Mình ko biết gì về VBA nên tạo file này theo kiểu "nông dân" mong bạn đừng cười chê. :.,

Nếu cứ để vậy thì chương trình đã đạt như mình mong muốn nhưng mình e ngại có thể có xung đột vì hai đoạn code có lúc thực thi cùng 1 nhiệm vụ dẫn đến kết quả không như mong muốn.
Với lại sao khi gõ số nó chạy lâu thế nhỉ, trong khi bấm nút Spin button nó lại đáp ứng tức thì !$@!!

PacificPR · 1/12/17

vnlinhtinh đã viết:
Cảm ơn phulien1902 đã giúp.
Đoạn code trên mình lắp vào chạy tốt, đáp ứng được đề bài.
Theo ý hiểu của Mình thì khi ô N6 biến đổi thì code sẽ chạy. Mình gán cho ô N6 đó nút Spin button nhưng khi bấm nút Spin button để đổi số ở ô N6 thì hình như code không chạy mà nó thực thi lệnh cũ (lệnh này mình tạo bằng record Macro).
Hi hi hi diễn đạt khó quá ...... thôi mình up cái file đó nhờ phulien1902 kiểm tra giùm.
Mục tiêu (co dãn dòng và ẩn dòng) là khi bấm Spin button hay gõ số ở ô N6 thì nó thực thi cùng một chương trình để tránh xung đột (nếu có).
P/s: Mình ko biết gì về VBA nên tạo file này theo kiểu "nông dân" mong bạn đừng cười chê.
Nếu cứ để vậy thì chương trình đã đạt như mình mong muốn nhưng mình e ngại có thể có xung đột vì hai đoạn code có lúc thực thi cùng 1 nhiệm vụ dẫn đến kết quả không như mong muốn.
Với lại sao khi gõ số nó chạy lâu thế nhỉ, trong khi bấm nút Spin button nó lại đáp ứng tức thì

Sao còn cái chỗ đóng khung màu đỏ thấy vô duyên qua đi

vnlinhtinh · 1/12/17

PacificPR đã viết:
Sao còn cái chỗ đóng khung màu đỏ thấy vô duyên qua đi

View attachment 187530

Ặc ..... thánh soi
Khắc phục rồi nhé $-.,\;$

PacificPR · 1/12/17

vnlinhtinh đã viết:
Ặc ..... thánh soi
Khắc phục rồi nhé $-.,\;$

Em nói thất Bác đừng có giận nha. Đây là mẫu BBNT xấu nhất là em từng đã đọc qua

vnlinhtinh · 1/12/17

Hê hê ... in ra giấy rất là đẹp nhé.
Ngoài ra còn link qua Word hay merge mail nhìn cũng tử tế chẳng kém ai đâu nhé.
Không có nghề office mần đc thế ta thấy cũng nể ta lắm rồi :AS:L<

vnlinhtinh · 4/12/17

Tôi muốn đồng bộ giữa 2 ô thuộc 2 sheet khác nhau thì làm thế nào hả mấy bác?
(Ví dụ: Có 2 ô bằng nhau thuộc 2 sheet khác nhau: gõ ô A2 sheet1 thì ô B3 sheet2 biến đổi và ngược lại gõ ô B3 sheet2->A2 sheet1 thay đổi).

vnlinhtinh · 4/12/17

PacificPR ơi, phulien1902 ơi, các CỤ ơi trợ giúp đi

phulien1902 · 4/12/17

vnlinhtinh đã viết:
Tôi muốn đồng bộ giữa 2 ô thuộc 2 sheet khác nhau thì làm thế nào hả mấy bác?
(Ví dụ: Có 2 ô bằng nhau thuộc 2 sheet khác nhau: gõ ô A2 sheet1 thì ô B3 sheet2 biến đổi và ngược lại gõ ô B3 sheet2->A2 sheet1 thay đổi).

+> Code cho Sheet1:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$2" Then
       Sheet2.Range("B3").Value = Target.Value
    End If
End Sub

+> Code cho Sheet2:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$B$3" Then
       Sheet1.Range("a2").Value = Target.Value
    End If
End Sub

TheThienChu · 4/12/17

vnlinhtinh đã viết:
Tôi muốn đồng bộ giữa 2 ô thuộc 2 sheet khác nhau thì làm thế nào hả mấy bác?
(Ví dụ: Có 2 ô bằng nhau thuộc 2 sheet khác nhau: gõ ô A2 sheet1 thì ô B3 sheet2 biến đổi và ngược lại gõ ô B3 sheet2->A2 sheet1 thay đổi).

Thông thường, liên kết là 1 chiều, ví dụ "gõ ô A2 sheet1 thì ô B3 sheet2 biến đổi" hoặc ngược lại.
Bạn muốn cả 2 chiều không biết dùng để làm gì

VetMini · 4/12/17

phulien1902 đã viết:
+> Code cho Sheet1:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Sheet2.Range("B3").Value = Target.Value End If End Sub

+> Code cho Sheet2:

PHP:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Sheet1.Range("a2").Value = Target.Value End If End Sub

Bạn đã chạy thử code này chưa?

vnlinhtinh · 5/12/17

Hí hí ... chuẩn rồi!
Thú thật là Tôi loay hoay cái nhiệm vụ này gần tháng rồi. Tìm nát mấy Forums Việt Nam không thấy.
Trước khi Pốt cái bài nhờ vả bên trên tôi cũng đã lọ mọ vào diễn đàn của bọn tây và cũng tìm thấy ở: https://www.reddit.com/r/excel/comments/3exvsp/sync_cells_across_pages/#ctjoph6
đoạn code tương tự của bạn phulien1902 nhưng lắp vào nó ko chạy mà báo lỗi như hình đính kèm dưới đây.
Nay bài toán được phulien1902 và các bác giải trong 1 phút 30 giây.
Xin chân thành cảm ơn phulien1902, Cảm ơn các bác! ''''''''''''''''''

TheThienChu · 5/12/17

vnlinhtinh đã viết:
Hí hí ... chuẩn rồi!
Thú thật là Tôi loay hoay cái nhiệm vụ này gần tháng rồi. Tìm nát mấy Forums Việt Nam không thấy.
Trước khi Pốt cái bài nhờ vả bên trên tôi cũng đã lọ mọ vào diễn đàn của bọn tây và cũng tìm thấy ở: https://www.reddit.com/r/excel/comments/3exvsp/sync_cells_across_pages/#ctjoph6
đoạn code tương tự của bạn phulien1902 nhưng lắp vào nó ko chạy mà báo lỗi như hình đính kèm dưới đây.
Nay bài toán được phulien1902 và các bác giải trong 1 phút 30 giây.
Xin chân thành cảm ơn phulien1902, Cảm ơn các bác!

Bạn chạy 2 code trên có bị "out of stack space" hay không

Private Sub Worksheet_...() Ý nghĩa,Tác dụng và cách dùng (2 người xem)

Người dùng đang xem chủ đề này

Members actively

Huyền thoại GPE

Bad Excel Member

Thành viên mới

GPE - My love

Thành viên mới

Thành viên mới

GPE - My love

Thành viên mới

File đính kèm

Thành viên mới

Thành viên mới

File đính kèm

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

GPE - My love

Thành viên tiêu biểu

Ăn cùng góc phố

Thành viên mới

File đính kèm

Thành viên tiêu biểu

Đếm ngược thời gian