·
2 months ago
@daryll Hi,
Which Excel version are you using?
在Excel中輸入或輸入數據後如何鎖定或保護單元格？
假設您有一個工作表，並且只有一定範圍的空白單元格需要輸入數據，並且在完成輸入數據之後，您需要自動鎖定這些單元格以防止再次更改。 您如何才能做到這一點？ 本文可以為您提供幫助。
在數據輸入或使用VBA代碼輸入後鎖定或保護單元格
例如，空白單元格的特定範圍是A1：F8。 在Excel中輸入數據後，請執行以下操作以鎖定這些單元格。
1.請先解鎖此範圍，選擇單元格並單擊鼠標右鍵，然後選擇 單元格格式 在右鍵菜單中，然後在 單元格格式 對話框，取消選中 鎖定 下框 保護 標籤，最後點擊 OK 按鈕。 看截圖：
2。 點擊 回顧 > 保護工作表。 並指定密碼以保護此工作表。
3.右鍵單擊工作表標籤，選擇 查看代碼 從右鍵單擊菜單中。 然後將下面的VBA代碼複製並粘貼到“代碼”窗口中。 看截圖：
VBA代碼：數據輸入或輸入後鎖定或保護單元格
Dim mRg As Range
Dim mStr As String
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("A1:F8"), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range("A1:F8"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="123"
If xRg.Value <> mStr Then xRg.Locked = True
Target.Worksheet.Protect Password:="123"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A1:F8"), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub
備註：在代碼中，“ A1：F8”是您需要輸入數據的範圍； “ 123”是此受保護工作表的密碼。 請根據需要更改它們。
4。 按 其他 + Q 同時關閉按鍵 Microsoft Visual Basic for Applications 窗口。
將數據輸入到範圍A1：F8的單元格後，它們將被自動鎖定。 如果您嘗試更改此範圍的任何單元格內容，則會出現一個提示對話框。 看截圖：
相關文章:
最佳辦公效率工具
Kutools for Excel解決了您的大多數問題，並使您的生產率提高了80％
- 重用: 快速插入 複雜的公式，圖表 以及您以前使用過的任何東西； 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
- 超級公式欄 （輕鬆編輯多行文本和公式）； 閱讀版式 （輕鬆讀取和編輯大量單元格）； 粘貼到過濾範圍...
- 合併單元格/行/列 不會丟失數據； 拆分單元格內容； 合併重複的行/列...防止細胞重複； 比較範圍...
- 選擇重複或唯一 行; 選擇空白行 （所有單元格都是空的）； 超級查找和模糊查找 在許多工作簿中； 隨機選擇...
- 確切的副本 多個單元格，無需更改公式參考； 自動創建參考 到多張紙； 插入項目符號，複選框等...
- 提取文字，添加文本，按位置刪除， 刪除空間; 創建和打印分頁小計； 在單元格內容和註釋之間轉換...
- 超級濾鏡 （將過濾方案保存並應用於其他工作表）； 高級排序 按月/週/日，頻率及更多； 特殊過濾器 用粗體，斜體...
- 結合工作簿和工作表; 根據關鍵列合併表； 將數據分割成多個工作表; 批量轉換xls，xlsx和PDF...
- 超過300種強大功能。 支持Office / Excel 2007-2019和365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能30天免費試用。 60天退款保證。
Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆
- 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 每天將您的工作效率提高50％，並減少數百次鼠標單擊！
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment...
The comment will be refreshed after 00:00.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.@simon Hi simon,If you want to lock cells in D6:D36, H6:H35 and L6:L35 separately after finish entering data in each range. Please do as follows.1. Select these three ranges by holding the Ctrl key;2. Do as the post described in step 1 to unlock these three ranges;3. Protect your worksheet with a password (Here my password is 123. This password will be used in the below code);4. Right click the sheet tab and then paste the below VBA code into the Code editor, and then press Alt + Q keys to close the Microsoft Visual Basic for Applications window.Notes:1) In the code, you can change the ranges and password as you need;2）After pressing Alt + Q keys to close the code window, you need to shift to another worksheet and then go back to current sheet to make the code work. Otherwise, error will be occurred.Dim mRg As Range 'Updated by Extendoffice 20201030 Dim mStr As String Dim mStrAddress As String Dim mArr Private Sub Worksheet_Activate() On Error Resume Next Erase mArr() mStrAddress = "D6:D36,H6:H35,L6:L35" mArr = Split(mStrAddress, ",") End Sub Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim xI As Integer For xI = 0 To UBound(mArr) If Not Intersect(Range(mArr(xI)), Target) Is Nothing Then Set mRg = Target.Item(1) mStr = mRg.Value Exit For End If Next End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim xRg As Range Dim xI As Integer On Error Resume Next For xI = 0 To UBound(mArr) Set xRg = Null Set xRg = Intersect(Range(mArr(xI)), Target) If Not (xRg Is Nothing) Then Target.Worksheet.Unprotect Password:="123" If xRg.Value <> mStr Then xRg.Locked = True Target.Worksheet.Protect Password:="123" End If Next End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim xI As Integer On Error Resume Next For xI = 0 To UBound(mArr) If Not Intersect(Range(mArr(xI)), Target) Is Nothing Then Set mRg = Target.Item(1) mStr = mRg.Value End If Next End Sub
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Good afternoon ... thank you again for this great resource. I do have one question. We have a shared document that is used by multiple users for input purposes. We have noticed that if User A enters data in a given cell, User A cannot edit per the code above (which is exactly what we want) but User B who the document is also shared with can delete the data that User A entered. Is there a revision for the code above that could be included in a shared document that has multiple users that are entering data.
-
To post as a guest, your comment is unpublished.@crystal Thank you so much for this, it is really helpful!One of my colleagues is working on a notebook that isn't able to install Microsoft Excel but through Microsoft Teams she can access this workbook.Something we were wondering about for another sheet would be if the password were typed into cell AZ1 then all the other cells would unlock, but if the password is not currently in the cell the cells would switch back to locked. Is this feasible?
-
To post as a guest, your comment is unpublished.@crystal Thanks so much! I just got a chance to test this while setting up our logging sheet for next month and this works fantastic. One thing I did notice is that reading for "Complete" is case sensitive. Sometimes the logger will put in "complete" and the data limitation allows it. To include this, would I just copy that "If" to "End If" and put "complete"?
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.@Will Hi Will,
Try this VBA. When entering "Complete" in any cell in column E, the rest cells of that row will be locked.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For Each cell In Range("E1:E500")
If cell.Value = "Complete" Then
ActiveSheet.Unprotect "Password"
cell.EntireRow.Locked = True
ActiveSheet.Protect "Password"
End If
Next cell
End Sub
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.@John Stephen Hi John,
This can't be done in google sheet. Sorry for the inconvenience. -
To post as a guest, your comment is unpublished.Hi,Can this be done in google sheet, if yes please let me know the procedure.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hi,
Please, can you help with my needs?
I am looking for VBA code - Once I save the sheet then can't edit on used cells and only allow to enter in new cells. Save by clicking save symbol or close file.
I tried various options but I unable to get them correctly, sometimes works and sometimes fail to work. Please help. -
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.After implementing this code, I can no longer sign my signature in Excel's signature tool. What code can I add so that this won't occur? I can set it so that one can edit objects but as soon as I type something into one of my protected cells the options are reset and I can't use the signature box. Any help would be appreciated
-
To post as a guest, your comment is unpublished.@Ryan Bullock Good day,
Can't help you solving this problem yet. Thanks for your comment. -
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hello there, how can I use this so that the user inputs the data in a range and then must click a submit button/cell to lock the cells?
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.@M Hi,
If there are merged cells in the specified range, please try the following code.
Dim mRg As Range
Dim mStr As String
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("A1:F8"), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range("A1:F8"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="123"
If xRg.Value <> mStr Then xRg.MergeArea.Locked = True
Target.Worksheet.Protect Password:="123"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A1:F8"), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub -
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.@Jess Hi Jess,
The below VBA code can help you.
Note: When protecting the worksheet, please uncheck the "Select locked cells" option in the Protect sheet dialog box. Thank you for your comment.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim xRg As Range
On Error Resume Next
If ProtectContents Then
Set xRg = Intersect(Range("A1:D4"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="123"
Target = Environ("USERNAME") & " " & Now()
Target.Locked = True
Target.Worksheet.Protect Password:="123"
Cancel = True
End If
End Sub -
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.Hello, I'm trying to code so that a user can double-click and it will capture their username and timestamp. I was the cells to lock immediately after this is completed. I have the following in VBA, but it keeps debugging back to the "Target = Environ("USERNAME") & " " & Now()". I am VERY new and VERY inexperienced w/ VBA, so please bear with me if it's something small I'm doing wrong.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target = Environ("USERNAME") & " " & Now()
Cancel = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range("A1:D45"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="GENERAL"
xRg.Locked = True
Target.Worksheet.Protect Password:="GENERAL"
End Sub -
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.@max m. marcus Good day,
Welcome to post any question in our forum: https://www.extendoffice.com/forum.html.
You will get more Excel supports from our professional or other Excel fans. -
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.hello there need some help
im actually doing a working roster for a large group of people, and this sheet is accessible by all so that they can key in their requests for days off/ annual leaves etc. however i want to only limit a number of people on leave for each day (maximum 5 on leave) and after 5 leave requests are keyed in for the day, no body else can fill anymore requests for that particular date.
is there any code/function that will calculate the number of specific requests perday then when the quota is reached, then the other cells are blocked for the requests as to not exceed? thanks in advance -
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.@smith123451 Hi Smith,
The below VBA code can help you solving the problem. Please have a try and thank you for your comment.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Target.Worksheet.Unprotect Password:="123"
Target.Locked = True
Target.Worksheet.Protect Password:="123"
End Sub -
To post as a guest, your comment is unpublished.Hello,
Is there a way to lock only cells rather than the whole sheet? For example, if a user enters "Yes" in A2, then A2 would lock immediately as to not allow any changes. I would still like if others could still edit any other cell too. Thanks! -
To post as a guest, your comment is unpublished.@jacjas Dear Jackie,
The below VBA code can help you solve the problem.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
Dim xSRg As Range
On Error Resume Next
Set xSRg = Range("A1:F8")
Set xRg = Intersect(xSRg, Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="123"
xSRg.Locked = True
Target.Worksheet.Protect Password:="123"
End Sub -
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.