Skip i'r prif gynnwys

Sut i ddefnyddio blwch gwirio i guddio / dadorchuddio rhesi neu golofnau yn Excel?

Gan dybio bod angen i chi ddefnyddio blwch gwirio i guddio neu agor rhai rhesi neu golofnau. Er enghraifft, pan fydd y blwch gwirio Rheoli X Gweithredol yn cael ei wirio, mae'r rhesi neu'r colofnau penodol yn cael eu harddangos, fel arall, byddant yn cael eu cuddio. Mae'r erthygl hon yn dangos i chi'r dull o ddefnyddio blwch gwirio i guddio / dadorchuddio rhesi neu golofnau yn Excel gyda manylion.

Defnyddiwch flwch gwirio i guddio / dadorchuddio rhesi neu golofnau gyda chod VBA

Defnyddiwch flwch gwirio i guddio / dadorchuddio rhesi neu golofnau gyda chod VBA

Gall y cod VBA canlynol eich helpu i guddio / cuddio rhai rhesi neu golofnau gyda blwch gwirio.

1. Ar ôl mewnosod blwch gwirio Rheoli X Gweithredol ar y daflen waith, cliciwch ar y dde ar y blwch gwirio ac yna dewiswch Gweld y Cod o'r ddewislen clicio ar y dde. Gweler y screenshot:

2. Yn y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr, copïo a gludo'r cod VBA canlynol i mewn i ffenestr y Cod.

Cod VBA: Defnyddiwch flwch gwirio i guddio / dadorchuddio rhesi neu golofnau

Private Sub CheckBox1_Click()
    [C:D].EntireColumn.Hidden = Not CheckBox1
End Sub

Nodiadau:

1. Yn y cod VBA, [C: D]. EntireColumn yn golygu y bydd y golofn C a D yn cuddio neu'n cuddio trwy wirio neu ddad-wirio'r blwch gwirio cyfatebol.

2. Ar gyfer cuddio neu arddangos rhai rhesi fel rhes 6: 9, newidiwch y [C: D]. EntireColumn i [6: 9]. EntireRow yn y cod VBA.

3. Gwasgwch y Alt + Q allweddi ar yr un pryd i gau'r Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

4. Nawr trowch y Modd Dylunio O dan y Datblygwr tab fel y dangosir isod screenshot:

O hyn ymlaen, pan fydd y blwch gwirio yn cael ei wirio, mae'r rhesi neu'r colofnau penodedig yn cael eu harddangos. Pan fydd heb ei wirio, mae'r rhesi neu'r colofnau penodedig wedi'u cuddio.

Erthyglau cysylltiedig:

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
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 ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer 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...

Disgrifiad

Mae 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 (26)
No ratings yet. Be the first to rate!
Rose
Rose
This comment was minimized by the moderator on the site
Bei mir werden die Zellen nicht ausgeblendet, wenn das Kontrollkästchen wieder in aktiv ist.

Mein Ziel ist es mit dem aktiveren des Kontrollkästchen die Zeile 7 einzublenden und wenn ich den Haken wieder entferne sollte Zeile 47 wieder ausgeblendet sein.
Hilfe....
Reply
0
0
crystal
crystal     Rose
This comment was minimized by the moderator on the site
Hi Rose,
The following VBA code can help.
Private Sub CheckBox1_Click()
[47:47].EntireRow.Hidden = Not CheckBox1
End Sub
Reply
Report
0
0
Welington Mesquita
Welington Mesquita
This comment was minimized by the moderator on the site
E para ocultar colunas não sequências?
Exemplo: A e C

Esta situação seria aplicado de que forma para A e C ?

"Sub CheckBox1_Click privada ()
[C:D].EntireColumn.Hidden = Não CheckBox1
End Sub"
Reply
0
0
crystal
crystal     Welington Mesquita
This comment was minimized by the moderator on the site
Hi,
The following VBA code can do you a favor. Please give it a try.
Private Sub CheckBox1_Click()
'Updated by Extendoffice 20220810
    [C:C, A:A].EntireColumn.Hidden = Not CheckBox1
End Sub
Reply
Report
0
0
Welington Mesquita
Welington Mesquita
This comment was minimized by the moderator on the site
Bom dia!
Espero que todos estejam bem!

Tenho uma situação onde preciso ocultar / reexibir a coluna utilizando o Control ActiveX quando solecionado o item.

Exemplo: Tenho coluna com os nomes: Planejado, Realizado, Análise Vertical, Análise Vertical, Variação (R$) e Variação (%), no entanto, que poder selecionar individual as colunas intercalando se necessário. Selecionar somente a coluna Realizado e Variação, mesmo não estando na sequencia.

Desde já agradeço,
Welington
Reply
0
0
crystal
crystal     Welington Mesquita
This comment was minimized by the moderator on the site
Hi,
Sorry I don't understand what you mean. Would you mind providing a screenshot of your data?
You may need to attach a screenshot or a sample file to describe the problem you encountered more clearly.
Reply
Report
0
0
Welington Mesquita
Welington Mesquita
This comment was minimized by the moderator on the site
Esse código oculta, mas não executa a reexibição quando desmarcar.

Alguém pode complementar por favor? Preciso de um código para ocultar e reexibir colunas indicadas.

Private Sub CheckBox1_Click()
[C:D].EntireColumn.Hidden = Not CheckBox1
End Sub

Obrigado
Reply
0
0
crystal
crystal     Welington Mesquita
This comment was minimized by the moderator on the site
Hi Welington Mesquita，
This VBA code can acheive:
When the checkbox1 is checked, the column C:D are displayed;
When it is unchecked, the column C:D are hidden.
The code works well in my case. Which Excel version are you using?
Reply
Report
0
0
etb1025
etb1025
This comment was minimized by the moderator on the site
What if i wanted to use 3 different checkboxes and wanted them to each toggle hiding different groups of rows? I have tried and looked all over. When I try to modify this code the checkboxes seem to interfere with each other.
Reply
Report
0
0
Eric
Eric
This comment was minimized by the moderator on the site
What if i wanted to use 3 different checkboxes and wanted them to each toggle hiding different groups of rows? I have tried and looked all over. When I try to modify this code the checkboxes seem to interfere with each other.
Reply
0
0
Maggie
Maggie
This comment was minimized by the moderator on the site
Very useful, but how can I use it to hide just a part of the spreadsheet? I mean for example cells B2:B11, how to combine those two functions? Thanks.
Reply
0
0
angela
angela
This comment was minimized by the moderator on the site
It worked, but then when I saved the file, and reopen it doesn't work. I tried saving it as a Macro excel sheet and as a regular workbook. What have I done wrong? I want to give it to someone else to use and don't want them to be confused.
Reply
0
0
crystal
crystal     angela
This comment was minimized by the moderator on the site
Hi angela,After adding the code into workbook, please click File > Save as, choose where to save the file, in the Save As dialog box, choose Excel Macro-Enabled Workbook (*.xlsm) from the Save as type drop-down, and finally click Save. Then send this Excel Macro-Enabled workbook to others.
Reply
Report
0
0
May
May
This comment was minimized by the moderator on the site
I was try to unhide the first three role but it was not work.How can I do it ,I know it the person who send me that file wanna know my skill I can solve it or not.
Reply
0
0
crystal
crystal     May
This comment was minimized by the moderator on the site
Hi May,
Sorry I didn't get your point.
Reply
Report
0
0
Therese
Therese
This comment was minimized by the moderator on the site
Hello,

Is there a way to hide only one row. If I put 6:9 all rows are hidden, but if I only put 6, it comes out as error. Would appreciate your help, thank you!
Reply
0
0
crystal
crystal     Therese
This comment was minimized by the moderator on the site
Hi,
Please change the second line in the code to:
[6:6].EntireRow.Hidden = Not CheckBox1.
Thank you for your comment.
Reply
Report
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