Skip i'r prif gynnwys

Sut i gloi neu ddatgloi celloedd yn seiliedig ar werthoedd mewn cell arall yn Excel?

Mewn rhai achosion, efallai y bydd angen i chi gloi neu ddatgloi celloedd yn seiliedig ar werthoedd mewn cell arall. Er enghraifft, mae angen datgloi ystod B1: B4 os yw cell A1 yn cynnwys gwerth “Derbyn”; ac i'w gloi os yw cell A1 yn cynnwys gwerth “Gwrthod”. Sut allwch chi ei wneud i'w gyflawni? Gall yr erthygl hon eich helpu chi.

Cloi neu ddatgloi celloedd yn seiliedig ar werthoedd mewn cell arall gyda chod VBA

Cloi neu ddatgloi celloedd yn seiliedig ar werthoedd mewn cell arall gyda chod VBA

Gall y cod VBA canlynol eich helpu i gloi neu ddatgloi celloedd yn seiliedig ar werth mewn cell arall yn Excel.

1. Cliciwch ar y dde ar y tab dalen (y ddalen gyda chelloedd y mae angen i chi ei chloi neu ei datgloi yn seiliedig ar werthoedd mewn cell arall), a chlicio Gweld y Cod o'r ddewislen clicio ar y dde.

2. Yna copïwch a gludwch y cod VBA canlynol i mewn i ffenestr y Cod.

Cod VBA: Clowch neu ddatgloi celloedd yn seiliedig ar werthoedd mewn cell arall

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. Gwasgwch y Alt + Q allweddi ar yr un pryd i gau'r Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

O hyn ymlaen, pan fyddwch yn nodi gwerth “Derbyn” yng nghell A1, mae'r ystod B1: B4 wedi'i datgloi.

Wrth nodi gwerth “Gwrthod” i gell A1, mae'r ystod benodol B1: B4 wedi'i chloi'n awtomatig.

Erthyglau perthnasol:

Offer Cynhyrchiant Swyddfa Gorau

Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools for Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed o'r blaen. Kutools for Excel Yn cynnig dros 300 o nodweddion uwch i hybu cynhyrchiant ac arbed amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

tab kte 201905

Office Tab Yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (55)
No ratings yet. Be the first to rate!
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
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
satya
satya
This comment was minimized by the moderator on the site
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
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