Preskoči na glavno vsebino

Kako zakleniti ali odkleniti celice na podlagi vrednosti v drugi celici v Excelu?

V nekaterih primerih boste morda morali zakleniti ali odkleniti celice na podlagi vrednosti v drugi celici. Na primer, morate odkleniti obseg B1: B4, če celica A1 vsebuje vrednost »Sprejem«; in se zaklene, če celica A1 vsebuje vrednost »Zavrnitev«. Kako lahko to storite? Ta članek vam lahko pomaga.

Zaklepanje ali odklepanje celic na podlagi vrednosti v drugi celici s kodo VBA

Zaklepanje ali odklepanje celic na podlagi vrednosti v drugi celici s kodo VBA

Naslednja koda VBA vam lahko pomaga zakleniti ali odkleniti celice na podlagi vrednosti v drugi celici v Excelu.

1. Z desno miškino tipko kliknite zavihek lista (list s celicami, ki jih morate zakleniti ali odkleniti glede na vrednosti v drugi celici), in kliknite Ogled kode v meniju z desnim klikom.

2. Nato kopirajte in prilepite naslednjo kodo VBA v okno Code.

Koda VBA: zaklepanje ali odklepanje celic na podlagi vrednosti v drugi celici

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. Pritisnite druga + Q tipke hkrati, da zaprete tipko Microsoft Visual Basic za aplikacije okno.

Od zdaj naprej, ko v celico A1 vnesete vrednost »Accepting«, se obseg B1: B4 odklene.

Pri vnosu vrednosti »Zavrnitev« v celico A1 se določen obseg B1: B4 samodejno zaklene.

Sorodni članki:

Najboljša pisarniška orodja za produktivnost

🤖 Kutools AI Aide: Revolucionirajte analizo podatkov na podlagi: Inteligentna izvedba   |  Ustvari kodo  |  Ustvarite formule po meri  |  Analizirajte podatke in ustvarite grafikone  |  Prikličite funkcije Kutools...
Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup  |   Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule   |  Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic   |  Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij:  12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

Opis

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (57)
No ratings yet. Be the first to rate!
Dorte Larsen
Dorte Larsen
This comment was minimized by the moderator on the site
Hi
How to lock all cells with "O" in area AH4:AE370
Reply
0
0
Monteiro
Monteiro
This comment was minimized by the moderator on the site
How do I automatically protect a row based on a cell?
Example: if you enter a value in cell E1, it will automatically protect the cells to the left of the row, D1, C1, B1 and A1
And the same would apply to the following lines, if you enter a value in cell E2 it will automatically protect the cells to the left and so on.

Please help me.
Reply
0
0
Monteiro
Monteiro
This comment was minimized by the moderator on the site
I need an automatic command that whenever I put something in cell g3, g4, g5 .... it automatically protects the data in the cells on the left ...
example: if you insert data in cell g3, it will automatically protect cells F3, E3, D3, C3, B3, A3... could you help me?


Today I can only save this way by creating a button, and it is very manual and time-consuming...
Reply
0
0
Monteiro
Monteiro     Monteiro
This comment was minimized by the moderator on the site
there is this command that protects an entire line, but I can't think of anything to solve my case and automate the protection

If Target.Row = 1 And Target.Value <> "" Then
Reply
0
0
Stephanie
Stephanie
This comment was minimized by the moderator on the site
Hello, Is there any way to have this repeat down an entire sheet. I have this code entered:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("X3") = "N0" Then
Range("B3:W3").Locked = False
ElseIf Range("X3") = "Yes" Then
Range("B3:W3").Locked = True
End If
End Sub

This is working fine for that row but I would like it to apply to each row as the value "Yes" is selected in column X. Is this possible and if so how do I do that? 
Thanks! 

Reply
0
0
crystal
crystal     Stephanie
This comment was minimized by the moderator on the site
Hi Stephanie,
Do you mean that when Yes is selected in each cell of column X, the corresponding row will be locked? When switching to No, the corresponding row will be unlocked?Or just select Yes in any cell of column X to lock all rows in the current worksheet?
Reply
Report
0
0
Alif
Alif     crystal
This comment was minimized by the moderator on the site
Yes, do you know how?
Reply
0
0
Venkat
Venkat
This comment was minimized by the moderator on the site
I want to allow only 2 cells to edit as L after the two edits remaining cells should lock in that column can it possible ?
Reply
0
0
crystal
crystal     Venkat
This comment was minimized by the moderator on the site
Hi Venkat,You can see if there is an answer you need in this article.
https://www.extendoffice.com/documents/excel/3778-excel-lock-cell-after-data-entry-input.html
Reply
Report
0
0
Belcrai
Belcrai
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
Maz
Maz
This comment was minimized by the moderator on the site
The code does not do anything when I tried it. Does this work on MS Office Professional 2010?
Thanks.
Reply
0
0
crystal
crystal     Maz
This comment was minimized by the moderator on the site
Hi Maz,The code has been successfully tested in Excel 2010.
Reply
Report
0
0
Jeffrey Scott
Jeffrey Scott
This comment was minimized by the moderator on the site
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?
Reply
0
0
crystal
crystal     Jeffrey Scott
This comment was minimized by the moderator on the site
Good day,Sorry can't help you for that.
Reply
Report
0
0
Steffi
Steffi
This comment was minimized by the moderator on the site
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
Reply
0
0
crystal
crystal     Steffi
This comment was minimized by the moderator on the site
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.
Reply
Report
0
0
Mike
Mike     crystal
This comment was minimized by the moderator on the site
D90 is not in the range. It's 4 rows above C94
Reply
0
0
Kyle
Kyle
This comment was minimized by the moderator on the site
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?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations