PDA

View Full Version : Không cho xóa công thức trong bảng tính Excel



Duong gia
05-12-07, 04:06 PM
Các Bác cho em hỏi có đoạn code nào bảo vệ công thức trong bảng tính Excel như sau : trong bảng tính Excel, ta có thể thấy nhìn công thức, vẫn có thể xóa công thức nhưng khi di chuyển ra khỏi ô có công thức vừa xóa đó thì ngay lập tức nó điền lại công thức đó. (không phải dùng protect sheet).

ongtrungducmx25
05-12-07, 05:29 PM
Các Bác cho em hỏi có đoạn code nào bảo vệ công thức trong bảng tính Excel như sau : trong bảng tính Excel, ta có thể thấy nhìn công thức, vẫn có thể xóa công thức nhưng khi di chuyển ra khỏi ô có công thức vừa xóa đó thì ngay lập tức nó điền lại công thức đó. (không phải dùng protect sheet).

Bảo vệ công thức trong Microsoft Excel
http://www.echip.com.vn/echiproot/images/2003/so55/excel1.jpg
Một bảng tính Excel thông thường có hai phần: dữ liệu “thô” là số liệu thực tế và phần công thức để tính toán. Công thức để tính toán đôi khi khá phức tạp và đòi hỏi nhiều công sức, còn dữ liệu thô thì có thể ai nhập cũng được. Do đó, vấn đề sẽ không còn đơn giản nếu trong bảng tính có chỗ ta muốn bảo vệ mà có chỗ lại không.
Cách làm của tôi trong việc bảo vệ công thức là cứ để các ô bị locked theo mặc định, không cần phải thay đổi gì. Nhưng khi người dùng thay đổi (kể cả việc xóa) các ô có công thức (bắt đầu bằng dấu = ) thì việc khóa sẽ thực hiện (giống như vào menu Tools -> Protect Sheet). Còn khi người dùng thay đổi ô không phải là công thức thì việc thay đổi sẽ được chấp nhận (tương đương với việc chọn Tools\Unprotect Sheet)
Cách làm như sau:
Bước 1: Vào menu Tools -> Macro -> Visual Basic Editor
Bước 2: Cửa sổ soạn thảo Visual Basic hiện ra, khung bên trái là Project Explore (nếu chưa thấy thì chọn menu View -> Project Explorer). Nhấp kép chuột vào This WorkBook, gõ vào khung soạn thảo bên phải đoạn mã 1
Đoạn mã 1


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As Range
For Each rng In Target.Cells
If rng.HasFormula Then
ActiveSheet.Protect
Exit Sub
Else
ActiveSheet.Unprotect
End If
Next rng
End Sub

Bước 3: Đóng cửa sổ Visual Basic Editor, lưu và thoát khỏi Excel. Kể từ đây, các công thức sẽ được bảo vệ.
LƯU Ý:
http://www.echip.com.vn/echiproot/images/2003/so55/excel2.jpg
1) Một số dữ liệu dù bản chất không là công thức nhưng bạn muốn nó không bị thay đổi thì có thể nhập như dưới dạng công thức với dấu bằng để có thể được bảo vệ luôn, chẳng hạn =290.000 thay vì là một con số 290.000
2) Người dùng có thể “táy máy” chọn Tools\Unprotect Sheet để sửa đổi, vì vậy cần đặt thêm mật khẩu trong đoạn mã trên:
Ở câu lệnh ActiveSheet.Protect ta viết thành (giả sử mật khẩu là “123456”): ActiveSheet.Protect(“123456”)
Và do đó ở câu lệnh ActiveSheet.Unprotect phải sửa lại là: ActiveSheet.Unprotect(“123456”)
3) Nếu cẩn thận hơn, phòng người dùng vào Visual Basic Editor để “dòm” mật khẩu (giả sử tên book của bạn là bangtinh) thì mở cửa sổ Visual Basic Editor -> khung Project Explorer, nhấp phải vào VBAProject(bangtinh.xls) -> Properties -> thẻ Protection, chọn Lock project for viewing -> gõ mật khẩu - > lưu và thoát khỏi Excel.

chibi
05-12-07, 08:18 PM
Nhưng Duong gia lại muốn
vẫn có thể xóa công thức nhưng khi di chuyển ra khỏi ô có công thức vừa xóa đó thì ngay lập tức nó điền lại công thức đó

nguyennb
05-12-07, 09:23 PM
Cách của ongtrungducmx25 (http://www.giaiphapexcel.com/forum/member.php?u=4978) rất hay.
Còn nếu bạn muốn thế thì ở sự kiện Selection_chage, xác định địa chỉ của ô. Nếu đó không phải là ô chứa công thức và ô chứa công thức đã bị xóa thì cho cho điền lại công thức.

chibi
05-12-07, 09:33 PM
Còn nếu bạn muốn thế thì ở sự kiện Selection_chage, xác định địa chỉ của ô. Nếu đó không phải là ô chứa công thức và ô chứa công thức đã bị xóa thì cho cho điền lại công thức.
Bạn cho một ví dụ để cùng chia sẻ.

ongtrungducmx25
07-12-07, 08:41 AM
đúng rồi bạn thử cho vídu cụ thể đi.

Duong gia
07-12-07, 09:58 AM
Chỉ có Bác chibi là hiểu ý mình. Thanks

digita
07-12-07, 12:07 PM
Mời anh bạn xem cái file này đây nhá.

Các công thức 1 khi điền vô sheet 1 thì bị "dính cứng" luôn.

Mến

Duong gia
07-12-07, 03:12 PM
Mời anh bạn xem cái file này đây nhá.

Các công thức 1 khi điền vô sheet 1 thì bị "dính cứng" luôn.

Mến

Có phải virus không? Bạn làm mình chẳng dám download xuống để xem.

anhtuan1066
07-12-07, 03:24 PM
Gì mà ghê thế... nếu sợ virus thì cùng lắm Disable macro rồi vào xem thử code viết gì là biết liền... hi.. hi.. Nhát gan quá nhỉ !!!
Này nhé: Theo cách của tôi thì cho dù tôi chẳng biết file ấy có virus hay ko tôi cũng ko hề sợ.. nó chẳng làm gì dc bạn nếu bạn biết cách ngừa trước:
-Đóng toàn bộ Excel
-Mở file nghi ngờ lên
-Disable macro và xem code
-Nếu gan thì Enable macro luôn.. cùng lắm nó sẽ bắt đầu lây vào Book1 trong thư mục XLSTART... bạn vào thư mục này xóa file là virus cũng đi tong
Đơn giản vậy thôi... chỉ tại các bạn sơ ý, mở cùng lúc nhiều file, đến lúc gặp sự cố thì trở tay ko kịp
Riêng với file của bạn Digita thì ko có con sâu nào đâu! Yên chí... File rất hay... hợp với yêu cầu của bạn rồi đấy!
ANH TUẤN

Duong gia
07-12-07, 03:53 PM
Mời anh bạn xem cái file này đây nhá.

Các công thức 1 khi điền vô sheet 1 thì bị "dính cứng" luôn.

Mến

Đúng ý của mình rồi. Cám ơn bạn rất rất rất nhiều!

Thien
08-12-07, 09:51 AM
Mời anh bạn xem cái file này đây nhá.
Các công thức 1 khi điền vô sheet 1 thì bị "dính cứng" luôn.
Mến

Trời, nếu muốn sữa lại công thức thì sao đây????. (nếu không biết VBA).


Thân

anhtuan1066
08-12-07, 11:05 AM
Có lẽ khi bạn ấy thực hiện xong bảng tính thì bạn ấy mới chèn code này vào.. lúc đó người ta chỉ xem công thức mà ko sửa dc... Thuật toán của file thì hay nhưng chưa biết áp dụng vào cái gì... Cho xem ko cho sửa, vậy thôi protect có phải đơn giản hơn ko?

nhatthuc147
08-12-07, 02:07 PM
mình thấy rằng nếu disable macros thì đoạn code trên chẳng có tác dụng gì cả, lúc đó muốn xóa thì xóa. Vậy dùng VBA không phải lúc nào cũng tốt.

Duong gia
10-12-07, 08:29 AM
Mời anh bạn xem cái file này đây nhá.

Các công thức 1 khi điền vô sheet 1 thì bị "dính cứng" luôn.

Mến

To digita

Không được rồi bạn ơi, insert dòng và cột bị lỗi, copy công thức xuống cũng lỗi luôn . . . Bạn thử lại xem. Thanks

nokiano258vn
10-12-07, 12:06 PM
Tôi vẫn chèn được cột và dòng nhưng copy công thức thì lỗi như này (file gửi kèm)

Duong gia
10-12-07, 02:11 PM
Tôi vẫn chèn được cột và dòng nhưng copy công thức thì lỗi như này (file gửi kèm)

Nó lỗi quá, nó làm treo máy, mình phải nhấn nút reset.

pham van an
07-06-11, 10:33 AM
Code rất hay. Anh có thể chỉnh sửa cốt 1 chút để làm sao vẩn có thể copy các dòng và các ô. Vì trong 1 bảng tính khi người dùng cần nhiều dòng có thể chèn thêm.

minhcong.tckt
03-12-12, 08:46 AM
Bảo vệ công thức trong Microsoft Excel
http://www.echip.com.vn/echiproot/images/2003/so55/excel1.jpg
Một bảng tính Excel thông thường có hai phần: dữ liệu “thô” là số liệu thực tế và phần công thức để tính toán. Công thức để tính toán đôi khi khá phức tạp và đòi hỏi nhiều công sức, còn dữ liệu thô thì có thể ai nhập cũng được. Do đó, vấn đề sẽ không còn đơn giản nếu trong bảng tính có chỗ ta muốn bảo vệ mà có chỗ lại không.
Cách làm của tôi trong việc bảo vệ công thức là cứ để các ô bị locked theo mặc định, không cần phải thay đổi gì. Nhưng khi người dùng thay đổi (kể cả việc xóa) các ô có công thức (bắt đầu bằng dấu = ) thì việc khóa sẽ thực hiện (giống như vào menu Tools -> Protect Sheet). Còn khi người dùng thay đổi ô không phải là công thức thì việc thay đổi sẽ được chấp nhận (tương đương với việc chọn Tools\Unprotect Sheet)
Cách làm như sau:
Bước 1: Vào menu Tools -> Macro -> Visual Basic Editor
Bước 2: Cửa sổ soạn thảo Visual Basic hiện ra, khung bên trái là Project Explore (nếu chưa thấy thì chọn menu View -> Project Explorer). Nhấp kép chuột vào This WorkBook, gõ vào khung soạn thảo bên phải đoạn mã 1
Đoạn mã 1


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As Range
For Each rng In Target.Cells
If rng.HasFormula Then
ActiveSheet.Protect
Exit Sub
Else
ActiveSheet.Unprotect
End If
Next rng
End Sub

Bước 3: Đóng cửa sổ Visual Basic Editor, lưu và thoát khỏi Excel. Kể từ đây, các công thức sẽ được bảo vệ.
LƯU Ý:
http://www.echip.com.vn/echiproot/images/2003/so55/excel2.jpg
1) Một số dữ liệu dù bản chất không là công thức nhưng bạn muốn nó không bị thay đổi thì có thể nhập như dưới dạng công thức với dấu bằng để có thể được bảo vệ luôn, chẳng hạn =290.000 thay vì là một con số 290.000
2) Người dùng có thể “táy máy” chọn Tools\Unprotect Sheet để sửa đổi, vì vậy cần đặt thêm mật khẩu trong đoạn mã trên:
Ở câu lệnh ActiveSheet.Protect ta viết thành (giả sử mật khẩu là “123456”): ActiveSheet.Protect(“123456”)
Và do đó ở câu lệnh ActiveSheet.Unprotect phải sửa lại là: ActiveSheet.Unprotect(“123456”)
3) Nếu cẩn thận hơn, phòng người dùng vào Visual Basic Editor để “dòm” mật khẩu (giả sử tên book của bạn là bangtinh) thì mở cửa sổ Visual Basic Editor -> khung Project Explorer, nhấp phải vào VBAProject(bangtinh.xls) -> Properties -> thẻ Protection, chọn Lock project for viewing -> gõ mật khẩu - > lưu và thoát khỏi Excel.

Bài viết rất hữu ích cho em
Cảm ơn Bác nhiều

minhcong.tckt
03-12-12, 09:11 AM
Bác "ongtrungducmx25" cho em hỏi một chút
Giờ em muốn tất cả các sheet đều khóa như trên nhưng trừ sheet "TTDN" thì sửa như thế nào ạ (sheet đó em bảo vệ bằng 1 mật khẩu khác)

Mong bác giúp đỡ
Chân thành cảm ơn!!!