kolommen verbergen door voorwaarde

[KoenVB]

Hey allemaal,

Ik zit met een excel bestand waar ik bepaalde kolommen op "Blad1" (die waar de "y" waarde in komt) automatisch zou willen laten verbergen. Deze kolommen hebben echter niet altijd een "y" waarde. Dit is afhankelijk van welke opties ze kiezen.

Nu zoek ik dus een manier dat wanneer de invoer van een bepaalde cel veranderd hij steeds opnieuw zal controleren, binnen de range van men kolommen waar de waarden zullen veranderen en meer of minder "y" waarden zullen komen, en de kolommen met een "y" waarde zal verbergen, en deze zonder "y" waarde terug zichtbaar zal maken.

Bekijk bijlage Kolom_verbergen_test.xlsx

Dit is normaal een voorbeeld excel bestandje dat ik gemaakt heb met de hoofd formules in.

Ik heb al redelijk wat gezocht hoe ik dit kan doen, en denk dat het met macro's wel mogelijk is (niet veel ervaring met macro's). Maar als er een manier is zonder macro's te gebruiken is dit nog beter.

Met vriendelijke groet,
Koen

 

[Cobbe]

Welke kolommen op welk blad moeten wanneer verborgen worden en weer tevoorschijn komen?

 

[KoenVB]

Ik heb het test bestand zo ingesteld dat enkel op Blad1 de kolom F6 moet verbergen of terug verschijnen (in origineel bestand zijn dit meerdere kolommen). Dit zou dan automatisch moeten gaan wanneer een andere selectie wordt gemaakt in B2 en D5.

Dus afhankelijk van je keuze in B2 en D5 zullen de waarden van E6 en F6 veranderen. (de kolom E6 zal nooit een y waarde bevatten). Als F6 een y waarde bevat moet de kolom automatisch verbergen, als ze geen y waarde bevat dus als je keuze veranderd moet ze weer verschijnen.

Hoop dat het zo al wat duidelijker is

 

[Cobbe]

Hang deze code dan eens achter het betreffende blad:

Private Sub Worksheet_Change(ByVal Target As Range)
For Each cl In UsedRange.Cells
   Columns(cl.Column).Hidden = cl = "y"
Next
End Sub

 

[KoenVB]

Hmmmm, denk dat dit in de goede richting is. Als ik het als macro toevoeg in blad 1 en ik verander een waarde dan zie ik hem denk ik wel verbergen, maar hij laat hem meteen weer verschijnen.

Bekijk bijlage Kolom_verbergen_test.xlsm

Heb normaal het bestandje met de code in toegevoegd zodanig je zelf eens kan kijken wat ik bedoel.

Mvg
KoenVB

 

[Cobbe]

Was wat kort door de bocht; sorry:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo oeps
With Application
 .EnableEvents = False
 Columns.Hidden = False
   For Each cl In UsedRange.Cells
   cl.Select
    If Columns(cl.Column).Hidden = False Then
       Columns(cl.Column).Hidden = cl = "y"
    End If
   Next
oeps:
 .EnableEvents = True
End With
End Sub

 

[KoenVB]

Bedankt Cobbe, deze code werkt goed.

Enige dat ik er mss nog zou willen aanpassen is dat hij niet over alle gebruikte cellen gaat zoeken, maar over slechts over 1 rij?

En als ik me niet vergis dan gaat hij nu per verandering dat in het werkblad gebeurt de hele controle uitvoeren op "y" waarden. Is het mogelijk dit ook voor veranderingen in slechts 1 cel of 2 cellen te laten werken ipv heel het blad?

Maar nogmaals, het ziet er al zeer goed uit. :d

 

[Cobbe]

Ja natuurlijk, dat is ook gebruikelijk en zeker wenselijk.
Maar ik kon nergens uit opmaken welk bereik er diende te voldoen aan de voorwaarde.
Je kan dat opvangen met als eerste regel vòòr de ON ERROR regel:

If intersect(target, range(bereik)) is nothing then exit sub

Het probleem is nu dat de code NIET reageert op een wijziging in een dropdown.
Dan moet je dit weer opvangen via een berekening te forceren ergens in een verborgen cel en de code achter een calculation_event te hangen.

 

[KoenVB]

Ik heb voorlopig "If Intersect(D5, Range(6:6)) Is Nothing Then Exit Sub" geprobeerd maar geeft een fout bij range (het ":" is fout). Het probleem van het dropdown menu denk ik wel dat ik kan fixen. Maar hoe ik precies de Range instel lijkt me niet te lukken. Ik zou het willen laten beginnen op cel E6 en eindigen bvb op R6.

 

[Cobbe]

Er is een verschil tussen het bereik dat de code activeert en de cellen die deel uitmaken van de
bewerking.
Nu loopt de code over het bereik E6:R6:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo oeps
With Application
 .EnableEvents = False
 Columns.Hidden = False
   For Each cl In [B][COLOR="#FF0000"]Range("E6:R6")[/COLOR][/B]
   cl.Select
    If Columns(cl.Column).Hidden = False Then
       Columns(cl.Column).Hidden = cl = "y"
    End If
   Next
oeps:
 .EnableEvents = True
End With
End Sub

 

[KoenVB]

Dit is al een heel stuk overzichtelijker indien nog extra gegevens in het document moeten gebracht worden.

Heb je ook eventueel ook nog een manier om de macro enkel te laten activeren indien cel D4 bvb van waarde veranderd?

Daarna denk ik dat het klaar is.

 

[Cobbe]

Ja dan komt weer die Intersectregel in aanmerking:

If intersect(target, range("D5")) is nothing then exit sub

Hoe wordt D5 gewijzigd?

 

[KoenVB]

Afhankelijk van welke matrix gekozen wordt in B2, komen in de I kolom de verschillende mogelijkheden te staan die deze matrix kan zijn, voor elke matrix zijn dit verschillende waarden en een verschillende hoeveelheid waarden. D5 is dan een dropdown menu'tje van deze kolom. De keuze die dan gemaakt wordt bij D5 veranderd de waarden in de 6de rij door soort van subwaarden die in die bij de keuze van D5 horen.

 

[KoenVB]

Ik heb de code er ingestoken en het lijkt volledig te werken. Ik zal dit morgen proberen te implementeren in mijn origineel groter bestand en dan kan dit gesloten worden.

Alvast bedankt voor de snelle respons en goede oplossing.

:thumb: