[EXCEL] Rijen verbergen op basis van waarde van een cel

[Frituurman]

Mijn vraag: Ik ben aan het rommelen met een formuliertje in Excel. Ik wil dat, op basis van een waarde van een cel, een aantal regels worden verborgen. Op zich heb ik die met een private sub wel redelijk goed voor elkaar:

code:

If Not Application.Intersect(Range("G22"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "Ja":     Rows("32:34").EntireRow.Hidden = False
        Case Is = "Nee":    Rows("32:34").EntireRow.Hidden = True
        Case Is = "":       Rows("32:34").EntireRow.Hidden = True
        End Select
End If

Wanneer de Range een dropdown is, werkt dit als een zonnetje, maar wanneer de Range een formule is (met een tekst-uitkomst), dan werkt het niet:

code:

If Not Application.Intersect(Range("D39"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "Laag":       Rows("41:46").EntireRow.Hidden = False
        Case Is = "Middel":     Rows("41:46").EntireRow.Hidden = False
        Case Is = "Hoog":       Rows("41:46").EntireRow.Hidden = False
        Case Is = "Geen":       Rows("41:56").EntireRow.Hidden = True
        Case Is = "":           Rows("41:46").EntireRow.Hidden = True
        End Select
End If

Hierbij is D39 een cel met daarin een ALS-formule die standaard 'Leeg' is, maar op basis van een aantal andere velden/argumenten gevuld wordt met "Laag", "Middel" of "Hoog".

[dix-neuf]

Als het resultaat van D39 is gebaseerd op de inhoud van 1 of meer andere cellen, dan moet je die andere cel(len) als target beschouwen en niet D39.

[g0tanks]

Wat komt eruit als je de waarde van D39 bekijkt via VBA?

Wellicht dat je Target.Text kunt proberen in plaats van Target.Value, dan krijg je de uitkomst als een string.

[Frituurman]

dix-neuf schreef op woensdag 2 december 2020 @ 14:06:
Als het resultaat van D39 is gebaseerd op de inhoud van 1 of meer andere cellen, dan moet je die andere cel(len) als target beschouwen en niet D39.

Nee, de inhoud van D39 is afhankelijk van om en nabij 15 argumenten die samen één van 4 mogelijke uitkomsten hebben (naast 'leeg'). Met andere woorden, om dat van jou te doen, zou ik 15^4 argumenten moeten inbouwen in VBA, met allerlei afhankelijkheden. Dat wordt wel erg complex.

Ik zou graag willen dat de uitkomst die in D39 verschijnt n.a.v. de formule, door de macro als 'VALUE' wordt gezien.

[Frituurman]

g0tanks schreef op woensdag 2 december 2020 @ 14:13:
Wat komt eruit als je de waarde van D39 bekijkt via VBA?

Wellicht dat je Target.Text kunt proberen in plaats van Target.Value, dan krijg je de uitkomst als een string.

Ik heb dit geprobeerd, maar het biedt geen soelaas.

[TheFes]

Frituurman schreef op woensdag 2 december 2020 @ 14:19:
[...]


Nee, de inhoud van D39 is afhankelijk van om en nabij 15 argumenten die samen één van 4 mogelijke uitkomsten hebben (naast 'leeg'). Met andere woorden, om dat van jou te doen, zou ik 15^4 argumenten moeten inbouwen in VBA, met allerlei afhankelijkheden. Dat wordt wel erg complex.

Ik zou graag willen dat de uitkomst die in D39 verschijnt n.a.v. de formule, door de macro als 'VALUE' wordt gezien.

VBA is niet mijn expertise, en het kan vast makkelijker, maar je zou in je macro D39 als value kunnen plakken naar een andere lege cel, die gebruiken voor je macro, en dan weer leeg maken op het einde van de macro

[g0tanks]

Frituurman schreef op woensdag 2 december 2020 @ 14:22:
[...]


Ik heb dit geprobeerd, maar het biedt geen soelaas.

Ok en mijn eerste vraag? Als je D39 uitleest en het bijvoorbeeld in een MsgBox weergeeft, staat daar dan de tekst die je verwacht?

[HenkEisDS]

Vast heel nooberig, maar kun je niet een filter ("als niet leeg") instellen in een bepaalde kolom ("laat zien")?

[Frituurman]

g0tanks schreef op woensdag 2 december 2020 @ 14:36:
[...]


Ok en mijn eerste vraag? Als je D39 uitleest en het bijvoorbeeld in een MsgBox weergeeft, staat daar dan de tekst die je verwacht?

Dan zie ik gewoon de waarden die ik verwacht (Geen, Laag, Middel, Hoog). Ik doe dan zoiets:

code:

Sub Klikkerdeklikmetdetipvang0tanks()
MsgBox "De value van D39 is " & Range("D39").Value
End Sub

HenkEisDS schreef op woensdag 2 december 2020 @ 14:39:
Vast heel nooberig, maar kun je niet een filter ("als niet leeg") instellen in een bepaalde kolom ("laat zien")?

Dat gaat 'm niet worden

[ Voor 10% gewijzigd door Frituurman op 02-12-2020 14:50 ]

[g0tanks]

Frituurman schreef op woensdag 2 december 2020 @ 14:44:
[...]


Dan zie ik gewoon de waarden die ik verwacht (Geen, Laag, Middel, Hoog)

Ik vermoed dan dat het met Intersect niet kan. Die functie reageert alleen op bepaalde type veranderingen zoals het handmatig aanpassen van een cel. Als een cel van waarde verandert door een formule pikt Intersect het niet op.

[tritimee]

ik heb een macro die o.a. dit doet. maar dit specifieke stuk verbergt mijn regels

code:

Set Orga = Range("OrganogramZichtbaar")

For Each Cell In Orga
        If Cell.Value = False Then
        Cell.EntireRow.Hidden = True
        End If
        Next

Kijkend naar jouw code, mis je voor Rows wellicht de koppeling aan de juiste sheet.

[ Voor 13% gewijzigd door tritimee op 02-12-2020 14:53 ]

[Frituurman]

tritimee schreef op woensdag 2 december 2020 @ 14:51:
ik heb een macro die o.a. dit doet. maar dit specifieke stuk verbergt mijn regels

code:

Set Orga = Range("OrganogramZichtbaar")

For Each Cell In Orga
        If Cell.Value = False Then
        Cell.EntireRow.Hidden = True
        End If
        Next

Kijkend naar jouw code, mis je voor Rows wellicht de koppeling aan de juiste sheet.

Nope, want de macro werkt goed voor (handmatig of met een dropdown) andere aangepaste cellen.

g0tanks schreef op woensdag 2 december 2020 @ 14:51:
[...]


Ik vermoed dan dat het met Intersect niet kan. Die functie reageert alleen op bepaalde type veranderingen zoals het handmatig aanpassen van een cel. Als een cel van waarde verandert door een formule pikt Intersect het niet op.

Hmmm... zijn daar dan nog opties voor?

[dixet]

Paar vraagjes

• wat is de trigger voor je sub? Je wilt dat rijen verborgen worden op basis van de waarde van een cel, maar wanneer moet dat gebeuren? Heb je daar een knop voor gemaakt? Als je wilt dat het reageert op wijzigingen in de cel volstaat een private sub niet, maar moet je de code in het Worksheet.Change event opnemen
• Wordt het gedeelte binnen de IF wel uitgevoerd? Je controleert of er overlap is tussen D39 en een Target, maar bevat die Target de cel D39 wel?

De Case zelf zou goed moeten werken. Zeker niet .Text gebruiken zoals @g0tanks voorstelt. .Text geeft de waarde van een cel weer zoals die getoond wordt op het scherm. Dus als de waarde breder is dan de cel bevat Text " #####" in plaats van de echte waarde. Nog beter is .Value2 omdat .Value waarden zoals datums en currencies anders teruggeeft afhankelijk van de regional settings. Op een andere computer kan je daarmee dus ander resutaat krijgen (al zal dat in dit geval weinig uitmaken omdat je tekstwaardes vergelijkt)

[Logico]

Frituurman schreef op woensdag 2 december 2020 @ 14:44:
[...]


Dat gaat 'm niet worden

Als je de update al hebt gehad heb je nog een tussenmogelijkheid door m.b.v. een dynamic array een filter in een formule te gebruiken, zie de derde optie uit onderstaand gifje.

[Frituurman]

dixet schreef op woensdag 2 december 2020 @ 14:59:
Paar vraagjes

• wat is de trigger voor je sub? Je wilt dat rijen verborgen worden op basis van de waarde van een cel, maar wanneer moet dat gebeuren? Heb je daar een knop voor gemaakt? Als je wilt dat het reageert op wijzigingen in de cel volstaat een private sub niet, maar moet je de code in het Worksheet.Change event opnemen
• Wordt het gedeelte binnen de IF wel uitgevoerd? Je controleert of er overlap is tussen D39 en een Target, maar bevat die Target de cel D39 wel?

De Case zelf zou goed moeten werken. Zeker niet .Text gebruiken zoals @g0tanks voorstelt. .Text geeft de waarde van een cel weer zoals die getoond wordt op het scherm. Dus als de waarde breder is dan de cel bevat Text " #####" in plaats van de echte waarde. Nog beter is .Value2 omdat .Value waarden zoals datums en currencies anders teruggeeft afhankelijk van de regional settings. Op een andere computer kan je daarmee dus ander resutaat krijgen (al zal dat in dit geval weinig uitmaken omdat je tekstwaardes vergelijkt)

Ik vul waardes in (met dropdowns) in D36, D37 en D38. Op basis van een ALS-formule wordt D39 gevuld met (tenzij 1 of meerdere van D36:D38 leeg is, dan is de waarde ook leeg):

• Laag
• Middel
• Hoog
• Geen

Value die hij teruggeeft (getest via Msgbox) is ook daadwerkelijk een van de voorgenoemde opties.

code:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("D39"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "Laag":       Rows("41:46").EntireRow.Hidden = False
        Case Is = "Middel":     Rows("41:46").EntireRow.Hidden = False
        Case Is = "Hoog":       Rows("41:46").EntireRow.Hidden = False
        Case Is = "Geen":       Rows("41:46").EntireRow.Hidden = True
        Case Is = "":           Rows("41:46").EntireRow.Hidden = True
        End Select
End If
End Sub

De 'trigger' is dus het invullen van de dropdowns in D36:D38. Op basis hiervan wordt D39 gevuld met een waarde en op basis daarvan moeten de regels 41:46 wel of niet getoond worden.

[dix-neuf]

Frituurman schreef op woensdag 2 december 2020 @ 14:19:
Ik zou graag willen dat de uitkomst die in D39 verschijnt n.a.v. de formule, door de macro als 'VALUE' wordt gezien.

Dat zal niet gaan denk ik, zoals ik eerder al probeerde aan te geven en zoals ook g0tanks om 14.51 schreef.

[dixet]

Je kijkt nu naar de waarde van Target. Target is de cel die gewijzigd is, dat is dus de cel met de dropdown.
Een cel waarvan de waarde wijzigt door een formule triggert het Worksheet_Change event niet

This event does not occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation

(link)

Omdat je event getriggert wordt door het wijzigen van een dropdown kan je de logica prima gebruiken, alleen moet je controleren of D39 Laag, Middel, Hoog etc is en niet Target.Value

[Frituurman]

dixet schreef op woensdag 2 december 2020 @ 15:14:
Je kijkt nu naar de waarde van Target. Target is de cel die gewijzigd is, dat is dus de cel met de dropdown.
Een cel waarvan de waarde wijzigt door een formule triggert het Worksheet_Change event niet


[...]
(link)

Omdat je event getriggert wordt door het wijzigen van een dropdown kan je de logica prima gebruiken, alleen moet je controleren of D39 Laag, Middel, Hoog etc is en niet Target.Value

Nee, D39 is geen dropdown, D39 bepaalt (met een formule) op basis van D36:D38 wat de waarde wordt van D39. En op basis daarvan moeten dan ook regels al dan niet getoond worden. Maar wat ik dus begrijp is dat dit niet mogelijk is met een Worksheet_Change-event.

[dix-neuf]

Als je er geen probleem mee hebt dat bij ELKE wijziging in het werkblad (om het even waar) gekeken wordt naar de tekst in D39, dan kun je onderstaande macro gebruiken. Let er ook op dat je verschillende teksten waarbij hetzelfde moet gebeuren, kunt combineren. En natuurlijk kun je best uitgaan van zichtbare rijen, anders moet je bij elke wijziging eerst gaan kijken of er rijen verborgen of zichtbaar zijn. Ik ben ervan uitgegaan dat het bij zichtbaar/verborgen bij elke tekst om de rijen 41:46 gaat. Moet het bij "Geen" om de rijen 41:56 gaan (zoals in je voorbeeld), dan moet je dat even aanpassen.

code:

Private Sub Worksheet_Change(ByVal Target As Range)
Rows("41:56").EntireRow.Hidden = False
Select Case Range("D39").Value
Case "Laag", "Middel", "Hoog"
Rows("41:46").EntireRow.Hidden = False
Case "Geen", ""
Rows("41:46").EntireRow.Hidden = True
End Select
End Sub

[ Voor 20% gewijzigd door dix-neuf op 02-12-2020 15:33 ]

[dixet]

Frituurman schreef op woensdag 2 december 2020 @ 15:29:
[...]
Nee, D39 is geen dropdown, D39 bepaalt (met een formule) op basis van D36:D38 wat de waarde wordt van D39. En op basis daarvan moeten dan ook regels al dan niet getoond worden. Maar wat ik dus begrijp is dat dit niet mogelijk is met een Worksheet_Change-event.

Dat zeg ik ook niet. Je event triggert op basis van de dropdowns in de andere cellen. Op dat moment verandert ook de waarde in D39, maar die waarde is niet in "Target.Value" beschikbaar. Die zal je expliciet uit Range("D39").Value moeten halen.


Die Intersect zal ook zorgen dat je Case statements niet worden uitgevoerd: op het moment dat je de dropdown in D36 wijzigt zal je Target alleen D36 bevatten. De intersect tussen D36 en D39 is leeg. Als je de code niet overbodig vaak wilt uitvoeren kan je beter controleren op de de Intersect tussen Target en D36:D38

[Lustucru]

^^^^ Exact dat. Het worksheet_change() event wordt getriggerd door een wijziging in de 'brontekst' van een willekeurige cel. Strikt genomen wijzigt een cel die een formule bevat niet: de formule blijft gelijk, alleen de uitkomst van de functie is anders.

De intersect test is idd om je code niet onnodig te laten lopen. Omdat je de code wilt laten lopen bij elke wijziging in een van de broncellen (die op hun beurt weer tot gevolg hebben dat cel D39 van waarde verandert) controleer je dus of de targetrange overlapt met D36:D38. De waarde waar je iets mee wilt haal je direct uit D39.