Note: The other languages of the website are Google-translated. Back to English
Vpiši se  \/ 
x
or
x
Registracija  \/ 
x

or

Kako uporabiti potrditveno polje za skrivanje / razkrivanje vrstic ali stolpcev v Excelu?

Recimo, da morate uporabiti potrditveno polje, da skrijete ali razkrijete določene vrstice ali stolpce. Ko je na primer označeno potrditveno polje Active X Control, so prikazane določene vrstice ali stolpci, sicer pa bodo skrite. V tem članku je prikazana metoda, kako uporabite potrditveno polje za skrivanje / razkrivanje vrstic ali stolpcev v Excelu s podrobnostmi.

Uporabite potrditveno polje za skrivanje / razkrivanje vrstic ali stolpcev s kodo VBA


Uporabite potrditveno polje za skrivanje / razkrivanje vrstic ali stolpcev s kodo VBA

Naslednja koda VBA vam lahko pomaga s skrivanjem / razkrivanjem določenih vrstic ali stolpcev.

1. Po vstavitvi potrditvenega polja Active X Control na delovni list z desno miškino tipko kliknite potrditveno polje in nato izberite Ogled kode v meniju z desnim klikom. Oglejte si posnetek zaslona:

2. V Ljubljani Microsoft Visual Basic za aplikacije okno, kopirajte in prilepite naslednjo kodo VBA v okno Code.

Koda VBA: Uporabite potrditveno polje za skrivanje / razkrivanje vrstic ali stolpcev

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

Opombe:

1. V kodi VBA: [C: D]. EntireColumn pomeni, da bosta stolpca C in D skrila ali razkrila tako, da potrdite ali počistite ustrezno polje.

2. Če želite skriti ali prikazati nekatere vrstice, kot je vrstica 6: 9, spremenite [C: D]. EntireColumn do [6: 9]. EntireRow v kodo VBA.

3. Pritisnite druga + Q tipke hkrati, da zaprete tipko Microsoft Visual Basic za aplikacije okno.

4. Zdaj izklopite Način oblikovanja pod Razvojni zavihek, kot je prikazano spodaj:

Od zdaj naprej, ko je potrditveno polje označeno, se prikažejo določene vrstice ali stolpci. Če ni potrjena, so navedene vrstice ali stolpci skrite.


Sorodni članki:


Najboljša orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča vašo produktivnost za 80%

  • Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2019 in 365. Podpira vse jezike. Preprosta namestitev v vašem podjetju ali organizaciji. Vse funkcije 30-dnevnega brezplačnega preskusa. 60-dnevno jamstvo za vračilo denarja.
zavihek kte 201905

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!
dno pisarniške mize
Say something here...
symbols left.
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.
    etb1025 · 5 months ago
    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.
  • To post as a guest, your comment is unpublished.
    Eric · 5 months ago
    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.

  • To post as a guest, your comment is unpublished.
    Maggie · 1 years ago
    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.
  • To post as a guest, your comment is unpublished.
    angela · 1 years ago
    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.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      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.
  • To post as a guest, your comment is unpublished.
    May · 1 years ago
    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.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi May,
      Sorry I didn't get your point.
  • To post as a guest, your comment is unpublished.
    Therese · 1 years ago
    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!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Please change the second line in the code to:
      [6:6].EntireRow.Hidden = Not CheckBox1.
      Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Lou · 2 years ago
    Hi,

    Hi, I've done the code, it's work good but when I try to make the same in more than one checkbox at the same sheet they work 'together', only hide or unhide the rows if all three checkboxes are selected.
    I am tryint to make these three checkboxes works independent one from another. And let the users mark only one box for at time.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Lou,
      Please make sure the checkboxes you created are ActiveX Controls checkboxes. These checkboxes works independently in your worksheet.
      Thanks for your comment.
  • To post as a guest, your comment is unpublished.
    Ed · 2 years ago
    Hello,

    I am trying to use this code to hide/unhide on a separate sheet from the checkbox. What do I have to add to make this work? Thanks in advance.

    Private Sub CheckBox1_Click()
    [C:D].EntireColumn.Hidden = Not CheckBox1
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Good day,
      As the following code shown, please replace the "Sheet4" with your separate sheet name.

      Private Sub CheckBox1_Click()
      [Sheet4!C:D].EntireColumn.Hidden = Not CheckBox1
      End Sub
  • To post as a guest, your comment is unpublished.
    Cole · 3 years ago
    It seems like this code will only go one direction, i.e. only hide and not unhide them when clicked. Is there a way to correct this?
    • To post as a guest, your comment is unpublished.
      Kentthegreet · 3 years ago
      We have same problem but find a way to make it work. Please use ActiveX Controls checkbox and not at the Form Control checkbox. The code perfectly works with ActiveX checkbox.


      Private Sub CheckBox1_Click()
      [C:D].EntireColumn.Hidden = Not CheckBox1
      End Sub
    • To post as a guest, your comment is unpublished.
      Kentthegreet · 3 years ago
      We have same problem but I found out we're using Form Controls and not ActiveX Controls. The code perfectly works in ActiveX Control Checkbox button. Just turn off the Design Mode so you can click check the Checkbox.
  • To post as a guest, your comment is unpublished.
    Pieter Ramaut · 3 years ago
    @Wayne:

    Private Sub CheckBox1_Click()
    ActiveSheet.Unprotect Password:="xxxxx"
    Rows("284:351").EntireRow.Hidden = Not CheckBox1
    ActiveSheet.Protect Password:="xxxx"
    End Sub
  • To post as a guest, your comment is unpublished.
    Wayne Bailey · 4 years ago
    Is there a way to use the "Use checkbox to hide/unhide rows or columns with VBA code" on a protected worksheet?