Xin giúp đỡ về Sự kiện Worksheet_Change

[trungvdb]

Giả sử tôi có đoạn Code sau

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$1" Then
Range("A1").CurrentRegion.Copy: Range("A10").PasteSpecial 3
End If
End Sub

Bây giờ tôi muốn lệnh Copy chỉ được thực hiện khi ô H1>100 thì lệnh Copy mới thực hiện, tôi sửa thành

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$1" And Target > 100 Then
Range("A1").CurrentRegion.Copy: Range("A10").PasteSpecial 3
End If
End Sub

thì nó lỗi, vậy xin hỏi mọi người chỉ cho cách viết đúng

 

[hoamattroicoi]

Worksheet_change là sự kiện bạn ạ. Không phải Phương thức (method) nhé!
và chỗ Target > 100 bạn sửa thành Target.value >100 mới chuẩn.
Chưa thấy file của bạn nên không biết bắt bệnh có đúng không? he he!

 

[trungvdb]

Xin gửi file đính kèm, khi thay đổi H1 là 300 thì nó báo lỗi 13

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$1" And Target.Value > 100 Then
Rows("10:100").ClearContents
Range("A1").CurrentRegion.Copy: Range("A10").PasteSpecial 3
End If
End Sub

Xin được nhờ giúp đỡ chỗ sai

 

[ndu96081631]

Xin gửi file đính kèm, khi thay đổi H1 là 300 thì nó báo lỗi 13

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$1" And Target.Value > 100 Then
Rows("10:100").ClearContents
Range("A1").CurrentRegion.Copy: Range("A10").PasteSpecial 3
End If
End Sub

Xin được nhờ giúp đỡ chỗ sai

Viết thế này mới chuẩn

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$H$1" Then
    If Target.Value > 100 Then
      Rows("10:100").ClearContents
      Range("A1").CurrentRegion.Copy: Range("A10").PasteSpecial 3
    End If
  End If
End Sub

Tức là: Xác định vùng hoạt động của Target trước sau đó mới là những điều kiện khác
Cẩn thận với sự kiện Change... cái vụ AND nhiều thứ dễ gây lỗi lắm

 

[ptm0412]

nếu chỉ sửa lỗi Type Mismatch thì sửa như vầy:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$H$1" Then
    If Target > 100 Then
        Rows("10:100").ClearContents
        Range("A1").CurrentRegion.Copy: Range("A10").PasteSpecial 3
    End If
End If
End Sub

Lý do:
- Khi xoá dòng 10:100, sự kiện chạy lần 2, Target.Address = $10$100, và Target.Value > 100 không tính được vì target là cả 1 range chứ không phải 1 cell, target.Value là 1 mảng các giá trị chứ không phải 1 giá trị, không so sánh với 1 giá trị đơn được. (Type MisMatch là chỗ này)

- Nếu bỏ qua dòng lệnh xoá, đến khi copy, và paste, sự kiện chạy lần 3, lỗi tương tự vì Target.Address = $A$10:$C$17

Sửa như trên, code vẫn xét điều kiện If lần 2 và lần 3, nhưng Target.Address <> "$H$1", code bên trong bỏ qua không chạy.

Muốn sửa triệt để và code khỏi chạy nhiều lần, thì sửa như sau:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$H$1" And Target > 100 Then
        Rows("10:100").ClearContents
        Range("A1").CurrentRegion.Copy: Range("A10").PasteSpecial 3
    End If
End Sub

Chắc ăn thì sửa cả 2 cách:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$H$1" Then
    If Target > 100 Then
        Rows("10:100").ClearContents
        Range("A1").CurrentRegion.Copy: Range("A10").PasteSpecial 3
    End If
End If
Application.EnableEvents = True
End Sub

Hoa còi:
Mặc định khi nói tới đối tượng Range thì tức là Value, khỏi cần .Value.

 

[trungvdb]

Các thày giới thiệu 1 chút về "vì target là cả 1 range chứ không phải 1 cell", cái này mới quá tôi chưa hiểu.

Tại sao Target chỉ đích danh là H1 rồi mà, nó liên quan gì đến dòng 10:17 nhỉ?

 

[SA_DQ]

Các thày giới thiệu 1 chút về target

Tại sao Target chỉ đích danh là H1 rồi mà, nó liên quan gì đến dòng 10:17 nhỉ?

/-)ó là ước muốn của bạn, chứ thực ra target nó bao quát hơn nhiều.

Để chứng minh điều vừa nói, mình đề nghị bạn lấy 1 trang tính trắng & fải chuốt lên SheetName & chọn dòng cuối (E2003) để viết thủ tục với chỉ 1 dòng lệnh như sau:

Private Sub Worksheet_Change(ByVal Target As Range)
 
 MsgBox Target.Cells.Count, , Target.Address
 
End Sub

Nhưng để thấy sự fong fú của Target, ta dùng chuột chọn 1 vùng nhiều ô bất kỳ trên trang tính;
Bấm chuột lên thanh công thức & nhập cụm từ nào đó, như 'GPE.COM'
Giữ fím {CTRL} & nhấn {ENTER} để xem VBA cho ta thông tin gì?
Lặp lại với vùng khác của trang tính để thêm lòng tin với sự kiện này hơn!

(húc xuân vui vẻ!

 

[Dauthivan]

Xin thày và mọi người cụ thể cho em một tình huống về "AND nhiều thứ dễ gây lỗi lắm" để em tránh trong những trường hợp đó.

 

[ptm0412]

Các thày giới thiệu 1 chút về "vì target là cả 1 range chứ không phải 1 cell", cái này mới quá tôi chưa hiểu.
Tại sao Target chỉ đích danh là H1 rồi mà, nó liên quan gì đến dòng 10:17 nhỉ?

Dùng sự kiện Change của Worksheet tức là bắt lấy tất cả những thay đổi về giá trị trong sheet, thay đổi range hoặc cell nào thì target là range hoặc cell đó. Nghĩa là Target = Range vừa mới thay đổi giá trị.

Như vậy:

Thực thi dòng lệnh xoá từ dòng 10 đến dòng 100, thì dòng 10:100 thay đổi giá trị, mà thay đổi giá trị tức là Change, vậy target là kí rì?

Thực thi câu lệnh copy, đến khi paste vào A10, thì nguyên vùng $A$10:$C$17 thay đổi giá trị, mà thay đổi giá trị tức là Change, vậy target là kí rì?

 

[trungvdb]

Thế thì tên H1 (Target.Address = "$H$1") nó có ý nghĩa nhất định về mặt nào nào hả thày?

 

[ChanhTQ@]

Cách đó cách nói tắc

Tức là: Xác định vùng hoạt động của Target trước sau đó mới là những điều kiện khác
Cẩn thận với sự kiện Change... cái vụ AND nhiều thứ dễ gây lỗi lắm

Với câu lệnh:

If Target.Address = "$H$1" And Target.Value > 100 Then

Ta dịch sang tiếng Việt như sau:

Nếu địa chỉ ô ta dụng vào là $H$1 & ô ta dụng vào có trị hơn 100 thì
(Làm những lệnh cho đến khi trước khi gặp lệnh End If tương ứng với nó)

Đó là 'nếu' suông sẻ, thì chả bao giờ có chuyện; Nhưng chuyện này xác suất chỉ 1 fần 2 triệu sẩy ra mà thôi!
Còn lại là cơ man hiểm nguy đang rình rập.
Như ta không đụng vô nó (H1 í) mà ta dụng vô ô khác, hay 1 lúc nhiều ô khác thì hỡi ôi.

Bỡi vậy ta thử đoạn mã này xem sao:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$H$1" And Target > 100 Then
        Rows("10:100").ClearContents
        Range("A1").CurrentRegion.Copy: Range("A10").PasteSpecial 3
Else
    MsgBox Target.Address,,Target.Cells.Count
End If
End Sub

Bạn có kết luận gì khi xài macro sự kiện này?

 

[trungvdb]

Tôi đang thử nghịch để tìm hiểu một số đối tượng, ý tôi muốn Copy ô A2 (Value) dán qua ô [A10] nhưng viết thế này không được

Private Sub Worksheet_Activate()
  [A2].Copy [A10].PasteSpecial 3
   End Sub

Vậy phải viết thế nào cho đúng?

 

[ptm0412]

Thế thì tên H1 (Target.Address = "$H$1") nó có ý nghĩa nhất định về mặt nào nào hả thày?

Target.Address = "$H$1" phải đặt trong câu lệnh If chứ? Trong vô vàn sự thay đổi của hàng triệu cell trong hàng núi sự kiện trong suốt thời gian làm việc, ta đặt điều kiện If iếc đó để chỉ khi nào target là cell H1 thì mới thực thi các dòng lệnh bên trong.

Target.Address = "$H$1" chỉ là 1 trong các cách để xác định Target có phải H1 hay không.

Một cách khác là

If Not Intersect(Target,Range("H1")) Is Nothing Then

 

[Hoàng Trọng Nghĩa]

Tôi đang thử nghịch để tìm hiểu một số đối tượng, ý tôi muốn Copy ô A2 (Value) dán qua ô [A10] nhưng viết thế này không được

Private Sub Worksheet_Activate()
  [A2].Copy [A10].PasteSpecial 3
   End Sub

Vậy phải viết thế nào cho đúng?

Bạn cũng có thể viết: [A10].Value = [A2].Value

Điều kiện để sự kiện này hoạt động là khi bạn đang ở sheet nào đó và chọn sheet có thủ tục này để nó kích hoạt sự kiện này mới hoạt động được.

 

[ptm0412]

Private Sub Worksheet_Activate()
  [A2].Copy [A10].PasteSpecial 3
   End Sub

Vậy phải viết thế nào cho đúng?

[A2].Copy và [A10].PasteSpecial 3 là 2 câu lệnh riêng rẽ, nên 1 là phải xuống dòng, 2 là phải có dấu phân cách ":"

Tuy nhiên, dùng cách của Nghĩa nhanh hơn nhất là đối với vùng dữ liệu cần copy lớn.

 

[trungvdb]

Cảm ơn mọi người, khi ghi Macro lại tôi thấy đó là 2 dòng riêng biệt, đúng như thày Ptm0412 nói nếu không muốn viết xuống dòng thì phải cho hai chấm.

Sở dĩ tôi thắc mắc thế là vì tôi thấy lệnh Copy của anh Nghĩa Phúc có làm tắt được (không có dấu nên làm thử thấy không ra

Nguyên văn, code của anh Nghĩa Phúc như sau:

Sub NoiBang()
    Dim i As Long
    On Error Resume Next
    i = Sheets("TongHop").Index 'Trả về vị trí của sheet TongHop'
    If i = 0 Then Sheets.Add.Name = "TongHop" 'Nếu chưa có sheet TongHop thì tạo mới'
    With Sheets("TongHop")
        .Move Before:=Sheets(1) 'Chuyển lên đầu'
        .Select
        Sheets(2).[1:1].Copy .[A1] 'Copy tiêu đề'
        .UsedRange.Offset(1).Clear 'Xóa dữ liệu hiện có'
        For i = 2 To Sheets.Count
            Sheets(i).UsedRange.Offset(1).Copy .[A65536].End(xlUp).Offset(1) 'Copy dữ liệu từ các sheet khác'
        Next
        .UsedRange.EntireColumn.AutoFit 'Căn chỉnh cột'
    End With
End Sub

Tôi đang tìm hiểu cái dòng Sheets(i).UsedRange.Offset(1).Copy .[A65536].End(xlUp).Offset(1).

 

[trungvdb]

Tôi tạm hiểu về chú Target như thế này có phải không, nếu không phải mong các thày chỉ tiếp cho

- Target bao gồm toàn bộ các ô trên màn hình (ví dụ đối với Excel 2003 Target là tập hợp gồm 256x65536 ô, tức nó cũng chứa 256x65536 giá trị; giá trị ô $H$1 từ lệnh Target.Address = "$H$1"chỉ là một trong số 256x65536 giá trị của nó thôi), nếu toàn bộ tập hơp phần tử trong nó chưa bị lỗi (đều có giá trị) thì nó lấy một giá trị tại ô $H$1 ra so sánh với 100 nếu lớn hơn thì thực hiện lệnh Copy.

- Ban đầu khi chưa thực hiện lệnh xóa gì cả thì toàn bộ các giá trị trong hàng (10:1000) đều bằng 0, nhưng khi thực hiện lệnh xóa ClearContents thì nó không hiểu giá trị các ô trong hàng này là bao nhiêu (bản chất lỗi liệu có phải do ô xóa ClearContents thì chẳng có giá trị gì cả khác với ô để trống giá trị bằng 0?). Lúc này Target bị lỗi vì chỉ cần 1 ô của nó không xác định là nó bị lỗi luôn.


-----------------
Tóm lại câu lệnh được thực thi khi có 2 điều kiện:
1. Điều kiện cần: Tất cả các ô trên màn hình đều phải xác định (Target không lỗi)
2. Điều kiện đủ: Muốn thực hiện lệnh thì phải thỏa mãn lệnh If tức giá trị Target tại ô H1 (phải lớn hơn 100)

 

[ndu96081631]

Tôi tạm hiểu về chú Target như thế này có phải không, nếu không phải mong các thày chỉ tiếp cho

- Target bao gồm toàn bộ các ô trên màn hình

Nói chính xác thì Target là tất cả những cell có thay đổi (bằng cách gõ bằng tay hay paste vào)
Để giới hạn code chạy khi thay đổi cell H1, ta dùng câu lệnh IF Target.Address = "$H$1" then
Còn nếu viết
IF Target.Address = "$H$1" and Target.Value >100 then thì
- Khi gõ số 200 vào H1, code phân tích thấy:

đ/k Target.Address = "$H$1" ---> đúng
đ/k Target.Value >100 ---> đúng
nên code tiến hành câu lệnh Rows("10:100").ClearContents
​

- Khi câu lệnh Rows("10:100").ClearContents thực thi, tức có thay đổi, cũng tức là có sự kiện Change thì code lại tiến hành phân tích 2 đ/k trên (với Target = Rows("10:100")) như sau:

đ/k Target.Address = "$H$1" ---> sai
đ/k Target.Value >100 ---> không so sánh được 1 vùng với số 100 dẫn đến lỗi
​

​

- Nếu viết thành 2 IF riêng biết thì ngay từ câu IF đầu tiên, nếu phát hiện sai nó đã không làm gì cả nên sẽ không có lỗi nào xuất hiện
Thế thôi
----------------
Thế mới thấy cách viết

IF đ/k1 and đ/k2 then

sẽ tệ hơn rất nhiều so với các viết

IF đ/k1 then
  IF đ/k2 then

Với cách viết thứ nhất, dù đ/k1 có sai đi chăng thì "nó" cũng đi phân tích đ/k2 (thay vì thoát luôn)

 

[trungvdb]

Cảm ơn thày Ndu và thày Ptm cùng mọi người rất nhiều,tôi đã hoàn toàn hiểu về nó rồi.

Đơn giản là ô H1 là 1 ô, nếu chỉ cần thay đổi từ 2 ô bất kỳ trở lên thôi là sẽ lỗi ngay
(vì 1 ô H1 không thể so sánh được với nhiều ô thay đổi).
-------------------
Trước đây hiểu lỗi sau khi Rows("10:100").ClearContents nghĩa là các ô trong dòng 10 đến dòng 100 không tồn tại giá trị là chưa đúng.

Chốt lại bản chất vấn đề này thày Ndu giảng giải và kết luận: Target là tất cả những cell có thay đổi

 

[Hoàng Trọng Nghĩa]

Cảm ơn thày Ndu và thày Ptm cùng mọi người rất nhiều,tôi đã hoàn toàn hiểu về nó rồi.

Đơn giản là ô H1 là 1 ô, nếu chỉ cần thay đổi từ 2 ô bất kỳ trở lên thôi là sẽ lỗi ngay
(vì 1 ô H1 không thể so sánh được với nhiều ô thay đổi).
-------------------
Trước đây hiểu lỗi sau khi Rows("10:100").ClearContents nghĩa là các ô trong dòng 10 đến dòng 100 không tồn tại giá trị là chưa đúng.

Chốt lại bản chất vấn đề này thày Ndu giảng giải và kết luận: Target là tất cả những cell có thay đổi

Nói đúng hơn, Target là nơi mà con trỏ đặt vào và có thay đổi tại ô đó.