Excel'deki başka bir hücredeki değerlere göre hücreleri nasıl kilitleyebilir veya kilidini açabilirim?
Bazı durumlarda, başka bir hücredeki değerlere bağlı olarak hücreleri kilitlemeniz veya kilidini açmanız gerekebilir. Örneğin, A1 hücresi “Kabul ediliyor” değerini içeriyorsa, B4: B1 aralığının kilidinin açılması gerekir; ve A1 hücresi "Reddetme" değerini içeriyorsa kilitlenecektir. Bunu başarmak için nasıl yapabilirsiniz? Bu makale size yardımcı olabilir.
VBA kodu ile başka bir hücredeki değerlere göre hücreleri kilitleyin veya kilidini açın
Aşağıdaki VBA kodu, Excel'deki başka bir hücredeki değere göre hücreleri kilitlemenize veya kilidini açmanıza yardımcı olabilir.
1. Sayfa sekmesine sağ tıklayın (başka bir hücredeki değerlere göre kilitlemeniz veya kilidini açmanız gereken hücrelerin bulunduğu sayfa) ve Kodu Görüntüle sağ tıklama menüsünden.
2. Ardından aşağıdaki VBA kodunu Kod penceresine kopyalayıp yapıştırın.
VBA kodu: Başka bir hücredeki değerlere göre hücreleri kilitleyin veya kilidini açın
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. Tuşuna basın. Ara Toplam + Q kapatmak için aynı anda tuşları Uygulamalar için Microsoft Visual Basic pencere.
Şu andan itibaren, A1 hücresine "Kabul" değerini girdiğinizde, B1: B4 aralığının kilidi açılır.
A1 hücresine "Reddediliyor" değeri girerken, belirtilen aralık B1: B4 otomatik olarak kilitlenir.
Kutools for Excel Sorunlarınızın Çoğunu Çözer ve Verimliliğinizi% 80 Artırır
Yeniden: Hızlıca yerleştirin karmaşık formüller, grafikler ve daha önce kullandığınız her şey; Hücreleri Şifrele şifre ile; Posta Listesi Oluşturun ve e-posta gönder ...
Süper Formül Çubuğu (birden çok metin ve formül satırını kolayca düzenleyin); Okuma Düzeni (çok sayıda hücreyi kolayca okuyun ve düzenleyin); Filtrelenmiş Aralığa Yapıştır...
Yinelenen veya Benzersiz'i seçin Satırlar; Boş Satırları Seçin (tüm hücreler boştur); Süper Bul ve Bulanık Bul Birçok Çalışma Kitabında; Rastgele Seçim ...
Tam kopya Formül referansını değiştirmeden Birden Çok Hücre; Otomatik Referans Oluştur Birden Çok Sayfaya; Madde İşaretleri Ekle, Onay Kutuları ve daha fazlası ...
Metni Çıkar, Metin Ekle, Konuma Göre Kaldır, Alanı Kaldır; Sayfalama Alt Toplamları Oluşturma ve Yazdırma; Hücre İçeriği ve Yorumları Arasında Dönüştür...
Süper Filtre (filtre şemalarını kaydedin ve diğer sayfalara uygulayın); Gelişmiş Sıralama ay / hafta / gün, sıklık ve daha fazlasına göre; Özel Filtre kalın, italik ...
Çalışma Kitaplarını ve Çalışma Sayfalarını Birleştirin; Tabloları anahtar sütunlara göre birleştirin; Verileri Birden Çok Sayfaya Bölme; Toplu dönüştürme xls, xlsx ve PDF...
300'den fazla güçlü özellik. Office / Excel 2007-2019 ve 365'i destekler. Tüm dilleri destekler. Kuruluşunuzda veya kuruluşunuzda kolay dağıtım. Tam özellikli 30 günlük ücretsiz deneme. 60 günlük para iade garantisi.
I'm using this fine to lock Cells B-D when the value in A is entered. I need this to be replicated for almost 500 rows in 8 sheets. is there a way to do this without having to type 400 lines on code? i.e. if the value in a = "accepted" then columns b-D are locked for that row only
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?
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
Hi Steffi, The code works well. If you want to lock the range of cells to prevent editing, you need to protect the worksheet manually after selecting No option in D90. And as D90 is in the range C94:F104, after protecting the worksheet, D90 can't be edited either.
I copied everything from above into a sheet. It worked for a second and now I'm getting the error 'Unable to set the Locked property of the Range class'. I even opened a whole new sheet and copied your example verbatim. Any idea whats going on?
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
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
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!
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.
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
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
Not being a VB expert i would say you have too many "Elseif" - if you change them all to just IF except for the last one then hopefully that will work.
Basically If X do this, If Y do this, if Z do this, if none of those - do this.
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
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.
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.
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
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?
Dear Mira,
Sorry can’t help with this, you can post your question in our forum: https://www.extendoffice.com/forum.html to get more Excel supports from our professional.
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
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.
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
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.
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
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.
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)
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
Dear leo,
Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
You will get more supports about Excel from our Excel professional.
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.
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
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"?
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
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.
