·
1 months ago
The code does not do anything when I tried it. Does this work on MS Office Professional 2010?
Thanks.
Excelの別のセルの値に基づいてセルをロックまたはロック解除するにはどうすればよいですか？
場合によっては、別のセルの値に基づいてセルをロックまたはロック解除する必要があります。 たとえば、セルA1に値「Accepting」が含まれている場合は、範囲B4：B1のロックを解除する必要があります。 セルA1に値「拒否」が含まれている場合はロックされます。 それを達成するためにどのようにできますか？ この記事はあなたを助けることができます。
VBAコードを使用して別のセルの値に基づいてセルをロックまたはロック解除します
VBAコードを使用して別のセルの値に基づいてセルをロックまたはロック解除します
次のVBAコードは、Excelの別のセルの値に基づいてセルをロックまたはロック解除するのに役立ちます。
1.シートタブ（別のセルの値に基づいてロックまたはロック解除する必要のあるセルを含むシート）を右クリックし、をクリックします コードを表示 右クリックメニューから。
2.次に、次のVBAコードをコピーして[コード]ウィンドウに貼り付けます。
VBAコード：別のセルの値に基づいてセルをロックまたはロック解除します
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") = "Accepting" Then
Range("B1:B4").Locked = False
ElseIf Range("A1") = "Refusing" Then
Range("B1:B4").Locked = True
End If
End Sub
3。 プレス 他の + Q キーを同時に閉じて Microsoft Visual Basic for Applications 窓。
これ以降、セルA1に値「Accepting」を入力すると、範囲B1：B4のロックが解除されます。
セルA1に値「拒否」を入力すると、指定した範囲B1：B4が自動的にロックされます。
関連記事:
- Excelで数式内のすべてのセル参照を一度にロックするにはどうすればよいですか？
- Excelでのデータ入力または入力後にセルをロックまたは保護するにはどうすればよいですか？
- 写真/画像をExcelのセルまたはセル内にロックする方法は？
最高のオフィス生産性ツール
Kutools for Excelはほとんどの問題を解決し、生産性を80％向上させます
- 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
- スーパーフォーミュラバー （複数行のテキストと数式を簡単に編集できます）; 読書レイアウト （多数のセルを簡単に読み取って編集する）; フィルター範囲に貼り付け...
- セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
- [複製]または[一意]を選択します 行; 空白行を選択 （すべてのセルは空です）; スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
- 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
- テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
- スーパーフィルター （フィルタースキームを保存して他のシートに適用します）; 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
- ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
- 300以上の強力な機能。 Office / Excel2007-2019および365をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 全機能30日間の無料トライアル。 60日間の返金保証。
Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作業をはるかに簡単にします
- Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
- 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
- 生産性が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.If my first column has strings can I lock that row specifically to the first column, where when I do a sort the full row will stay together on the sort?
-
To post as a guest, your comment is unpublished.Hello,
I need your favour. when i select No option from a cell i want the columns below to be locked/greyed out.
Is that possible? I tried this formula but dint work
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D90") = "Yes" Then
Range("C94:F104").Locked = False
ElseIf Range("D90") = "No" Then
Range("C94:F104").Locked = True
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.Hi please any one help me for VBA code .
if i select from drop down in cell Range(A1:A1000) "abc" then not lock Range(D1:D1000) to (F1:F1000)
if i select from drop down in cell Range(A1:A1000) "abc" then not lock Range(D1:D1000) to (F1:F1000)
if i select from drop down in cell Range(A1:A1000) "abc" then lock Range(D1:D1000) to (F1:F1000)
i.e respective cell A1 for D1 to F1
A2 for for D2 to F2 -
To post as a guest, your comment is unpublished.hi
if I need to lock a cell on sheet 2 (cell C4) based on a claim from sheet 1 (cell C1),
if "no" on sheet 1 C4; sheet 2 must be locked and it must transfer the value from sheet 1 C4,
to sheet 2 C4.
if "yes" on sheet 1, I must be able to type in the cell on sheet 2
Thanks Benthe
-
To post as a guest, your comment is unpublished.Hi
I have just tried using the code above
and it says type 13 error when I try to use it.
could you help me out with this?
thank you -
To post as a guest, your comment is unpublished.Hi, could you kindly check the reason why it doesn't work?
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A3:A37").Value <> "" Then
Range("B3:B37").Locked = True
ElseIf Range("A3:A37") = "" Then
Range("B3:B37").Locked = False
End If
If Range("B3:B37").Value <> "" Then
Range("A3:A37").Locked = True
ElseIf Range("B3:B37") = "" Then
Range("A3:A37").Locked = False
End If
End Sub
Thank you very much in advance!!! -
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.How would the code be if I wanted to lock a block of cells (Rows 6, 7, and 8/Letters D through U as well as cells F5 and J5) and have them unlock when I put an "X" in cell E5? Thanks in advance!
-
To post as a guest, your comment is unpublished.Hi MitchyII,
Do you mean the specified block of cells have been locked manually in advance and just want to unlock them by typing an "X" in cell E5?
If remove "X" from cell E5, you do want to lock the ranges again?
I need more details of the problem.
Thank you for your comment.-
To post as a guest, your comment is unpublished.Hi Mitchyll (or anyone), building off of Crystal. I have manually locked all cells and based on the information in column G, I would like them to remain locked or unlock. Example cells in column H should ONLY be unlocked if "c/p" is placed in the preceding cell in column G
-
-
-
To post as a guest, your comment is unpublished.Would you kindly advice me on how to correct this? Thank you in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A:A") = "SLOW MOVING" Then
Range("B:B").Locked = True
ElseIf Range("A:A") = "OVER STOCK" Then
Range("B:B").Locked = True
ElseIf Range("A:A") = "NORMAL" Then
Range("B:B").Locked = False
End If
End Sub-
To post as a guest, your comment is unpublished.
-
-
To post as a guest, your comment is unpublished.Can you guide me on what's wrong here please? Thank you in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("K:K") = "OVER STOCK" Then
Range("S:S").Locked = True
ElseIf Range("K:K") = "SLOW MOVING" Then
Range("S:S").Locked = True
ElseIf Range("K:K") = "NORMAL" Then
Range("S:S").Locked = False
ElseIf Range("K:K") = "SHORTAGE" Then
Range("S:S").Locked = False
End If
End Sub -
To post as a guest, your comment is unpublished.I prepare a warehouse stock management in excel template.To deliver a stock i have to issue a gate pass.I want to each gate pass,corresponding data will be updated in daily stock take page.With the change of gate pass serial no,the row will be locked and next will be filled up.
-
To post as a guest, your comment is unpublished.Hi,
I am really new to this.
I have been trying to put an invoicing system together in excel.
I created 3 sheets.
1. Invoice template (Invoice)- Just a generic invoice that is sent to my agents weekly.
2. A data sheet (Data sheet) to be exact - where the invoice can read the company name adress etc, so if anything changes the invoice will be automatically updated.
3. A calendar tab (Calendar 2018) to be exact - that is referenced in the invoice template, and puts the corresponding date and invoice number on the actual invoices.
What I want to do.
The calendar tab would be my main page, I added a status drop down cell for each week with options "Active" and "Closed". I would like to Lock the whole "Invoice" tab if the corresponding cell is set to "Closed".
I hope you guys understand what I am trying to do.
Thanks in advance.-
To post as a guest, your comment is unpublished.Dear Ando Veres.
The below VBA code can help you. Please place the code into the sheet code window of Calendar 2018 change A1 to your drop down cell. Thank you.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Target, Range("A1"))
If xRg Is Nothing Then Exit Sub
If Target.Validation.Type >= 0 Then
If Target.Value = "Closed" Then
Sheets("Data Sheet").Protect
ElseIf xRg.Value = "Active" Then
Sheets("Data Sheet").Unprotect
End If
End If
End Sub
-
-
To post as a guest, your comment is unpublished.Hi! Can someone help me? I have to lock/freeze a cell. That cell is linked to another one and has a value which is changing every minute. What I what to do is to keep the value for a certain minute/ hour. How can I do that without copying it and paste it as a value?
-
To post as a guest, your comment is unpublished.can some one correct this pls>>>
Private Sub Worksheet_Change(ByVal Target As Range)
For i = 7 To 100
If Range("Cells(D, i)") = "Loan" Then
Range("Cells(V, i):Cells(X, i)").Locked = True
ElseIf Range("Cells(D, i)") = "Savings" Then
Range("Cells(Q, i):Cells(U, i)").Locked = True
Range("Cells(W, i):Cells(X, i)").Locked = True
ElseIf Range("Cells(D, i)") = "ShareCap" Then
Range("Cells(Q, i):Cells(U, i)").Locked = True
Range("Cells(V, i)").Locked = True
End If
Next i
End Sub -
To post as a guest, your comment is unpublished.Hi! need some advise.
Is there a way not to allow a cell to be updated unless it has satisfied a condition on another cell?
Sample: if the cell A is not updated it will not allow me to change the value of cell B to complete.
Appreciate the feedback.
Thanks!-
To post as a guest, your comment is unpublished.Dear Margie,
Please try below VBA code.
Dim PreVal As String
Dim NextVal As String
Private Sub Worksheet_Activate()
PreVal = Range("A1")
NextVal = Range("A1")
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Count = 1) And (Target.Address = "$A$1") Then
NextVal = Range("A1")
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Address = "$A$1" Then
PreVal = Range("A1")
ElseIf (Target.Address = "$B$1") Then
If PreVal = NextVal Then
Application.EnableEvents = False
Range("A1").Select
Application.EnableEvents = True
End If
End If
End If
End Sub
-
-
To post as a guest, your comment is unpublished.WHAT WILL BE THE CODE IF I WANT TO LOCK CELL E1, E2, E3 .............. FOR SPECIFIC TEXT (LETS SAY "P") ON CELL B1, B2, B3.................RESPECTIVELY.
THANKS IN ADVANCE-
To post as a guest, your comment is unpublished.Good Day
Please try below VBA script.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Address = Range("A1").Address And Target.Value = "A" Then
Range("B1").Locked = True
ElseIf Target.Address = Range("A2").Address And Target.Value = "A" Then
Range("B2").Locked = True
ElseIf Target.Address = Range("A3").Address And Target.Value = "A" Then
Range("B3").Locked = True
End If
End If
End Sub
-
-
To post as a guest, your comment is unpublished.Hello,
I have tried your code and edit a little bit, but i can't work out what i do wrong here?
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A40") <> "" Then
Range("D40:E40").Locked = False
ElseIf Range("A40") = "" Then
Range("D40:E40").Locked = True
End If
End Sub
My though about it was if there is nothing in it (A40). Then i want to locked by VBA. If A40 contain something, then i want it to be unlocked. I hope you can see the sense of it.
Regards Kristoffer-
To post as a guest, your comment is unpublished.Good Day,
There is nothing wrong with your code. It works well for me.-
To post as a guest, your comment is unpublished.Hi. I too cannot get this code to work. It does absolutely nothing. As if the code isn't even there?? I'm very new to VBA and have a basic understanding on it. Is this code being run as-is, or does it have to have stuff added to it as well for it to run? Or turned into a Macro (which I don't really get why because that's a recording of instructions, on my understanding of them)
-
-
-
To post as a guest, your comment is unpublished.please can someone help me with the following.
I want to insert pictures of student in one sheet, appears in another sheet based on their names
To create a navigating plane to assist users
To assign a particular sheet(s) to a user
To create an interface for the workbook
To create a login page -
To post as a guest, your comment is unpublished.Hi, I'm trying to achieve this, but I get an error that VBA is unable to set the Locked property of the Range class if the sheet has been protected. Unprotecting the sheet will then negate the cell being locked.
How to get around this?
Thanks for any help.-
To post as a guest, your comment is unpublished.Dear Ant,
The below VBA code can help you solve this probem. Thank you for your comment.
Private Sub Worksheet_Activate()
If Not ActiveSheet.ProtectContents Then
Range("A1").Locked = False
Range("B1:B4").Locked = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range, xRgA As Range
On Error Resume Next
Application.EnableEvents = False
Set xRg = Range("B1:B4")
Set xRgA = Range("A1")
If Intersect(Target, xRg).Address <> Target.Address _
Or xRgA = "Accepting" Then
Application.EnableEvents = True
Exit Sub
ElseIf ActiveSheet.ProtectContents _
And Intersect(Target, xRg) = Target _
And xRgA.Value = "Refusing" Then
xRgA.Select
End If
Application.EnableEvents = True
End Sub-
To post as a guest, your comment is unpublished.Hi,
Is it possible to have this VBA lock one set of cells/unlock another based on this? For example Range B1:B4 is unlocked and C1:C4 is locked for "accepting" and then B1:B4 is locked and C1:C4 is unlocked for "refusing"?
Thanks,
Christian
-
-
To post as a guest, your comment is unpublished.You'll want to use the interface line in the workbook so when you open the file, it protects the sheets but allow macros to make changes anyway;
Private Sub Workbook_Open() 'This goes into "ThisWorkbook"
Worksheets("Order Tool").Protect Password:="Pwd", UserInterFaceOnly:=True
End Sub -
To post as a guest, your comment is unpublished.Did you resolved? I have the same problem
-
To post as a guest, your comment is unpublished.Dear Memo,
Please try the below VBA code.
Private Sub Worksheet_Activate()
If Not ActiveSheet.ProtectContents Then
Range("A1").Locked = False
Range("B1:B4").Locked = False
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xRg As Range, xRgA As Range
On Error Resume Next
Application.EnableEvents = False
Set xRg = Range("B1:B4")
Set xRgA = Range("A1")
If Intersect(Target, xRg).Address <> Target.Address _
Or xRgA = "Accepting" Then
Application.EnableEvents = True
Exit Sub
ElseIf ActiveSheet.ProtectContents _
And Intersect(Target, xRg) = Target _
And xRgA.Value = "Refusing" Then
xRgA.Select
End If
Application.EnableEvents = True
End Sub
-
-
-
To post as a guest, your comment is unpublished.How can lock/unlock a variable cell e.g. when cell is [=INDEX(A16:L35,MATCH(W5,A16:A35,0),MATCH("PAY",A16:L16,0))]
-
To post as a guest, your comment is unpublished.I want a simple VBA command which I am unable to figure out please help
If cell A1 is Balnk then Cell A2 is locked and If Cell A1 contains any Value then Cell A2 is unlocked
Similarly if cell A2 is blank then Cell A3 is locked and if Cell A2 contains any value then Cell A3 is unlocked
and so on as many cells as per requirement in any part of the sheet.
-