selection change trigger

[Gesus]

Hallo leden,

via een oude vraag op Help-mij heb ik de formule SOMCELKLEUR toegevoegd als module in VBA:

Function SOMCELKLEUR(rRange As Range, rColor As Integer)
Dim rCell As Range, vResult
For Each rCell In rRange
If rCell.Interior.ColorIndex = rColor Then vResult = WorksheetFunction.Sum(rCell, vResult)
Next rCell
SOMCELKLEUR = vResult
End Function

Deze formule werkt in mijn tabblad "Inhuur" goed behalve dat deze niet automatisch doorrekent. Omdat ik deze formule voor zo'n 50 regels gebruik is het niet logisch 50 x <edit> te doen en dan te enteren.

Nu las ik opmerkingen over een selection change trigger statement in VBA dat dit probleem zou oplossen.

Kan iemand mij de code geven zodat ik hem zo in excel kan plakken?

Thanx!!

groet Gesus

 

[edmoor]

Zorg dat je ergens in het werkblad de formule =Nu() hebt staan en gebruik dezelfde VBA functie met een kleine aanpassing:

Function SOMCELKLEUR(rRange As Range, rColor As Integer)
    [COLOR="#FF0000"]Application.Volatile[/COLOR]
    Dim rCell As Range, vResult
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = rColor Then vResult = WorksheetFunction.Sum(rCell, vResult)
    Next rCell
    SOMCELKLEUR = vResult
End Function

 

[Gesus]

Dank voor je snelle reactie, maar dan moet ik nog altijd <CALCC> (F9) indrukken (automatisch doorrekenen staat aan ) om dit te berekenen. Ik had gehoopt dat er een code is waarbij je dan bij wijziging van achtergrondkleur de waarde van SOMCELKLEUR automatisch zou worden berekend.

Heb je nog een optie?

alvast dank!

groet Gesus

 

[edmoor]

Plaats je document eens.

 

[snb]

welke cellen krijgen waarom dezelfde kleur ?

 

[edmoor]

@snb:
Het gaat erom dat alleen cellen met de opgegeven kleur bij elkaar worden opgeteld.

@Gesus:
Het is overigens niet zo dat het wijzigen van een kleur een event triggert.
Dan blijf je wat betreft de functie aan F9 gevonden.

 

[Gesus]

@ allen,

dank voor jullie reacties! indien het niet mogelijk is om een event te triggeren bij het wijzigen van een kleur, is de oplossing van Edmoor geweldig!
De optie met NU() in combinatie met "Application.Volatile" zorgt er nu voor dat je met <CALC> alles laat doorrekenen.

met vriendelijke groet Gesus

 

[snb]

@snb:
Het gaat erom dat alleen cellen met de opgegeven kleur bij elkaar worden opgeteld.

Die hebben niet zomaar een kleurtje gekregen.

 

[Gesus]

Klopt, ik kleur de cellen zelf geel . Hieronder een korte uitleg

Ik heb een range van 12 maanden, waarin ik een de inhuurkosten per maand van personeel neerzet. Een deel hiervan is werkelijkheid (bv januari tm juni) en de overige maanden zijn een verwachting. Indien je de werkelijkheid tm juni financieel wilt bepalen, maar de facturen over de maand juni zijn nog niet verwerkt, moet je het nog niet geboekte deel (dus factuur over de maand juni) als stelpost opnemen.

Hiertoe kleur ik de cellen januari tm mei geel en sommeer ze met de SOMCELKLEUR-formule. de verwachting over de maand juni is dan de stelpost voor het cumulatieve financiele resultaat tm Juni.

groet Gesus

 

[VenA]

Waarom gebruik je het 'Kwastje' (Opmaak kopiëren plakken) niet? Je weet dan zeker dat je dezelfde kleur hebt. En het geeft een change event.

Je kan de functie inkorten tot zoiets

Function Somcelkleur(rRange As Range, R1 As Range)
For Each cl In rRange
    If cl.Interior.ColorIndex = R1.Interior.ColorIndex Then
        Somcelkleur = Somcelkleur + cl
    End If
Next cl
End Function

 

[snb]

Met bijv. sumif is deze berekening zonder kleur prima uit te voeren.

 

[Gesus]

@SNB

kun je mij ook een voorbeeld sturen van de formule? ik heb alleen de nederlandse versie dus het zal dan wel SOM.ALS moeten zijn.
Als dit werkt, dan zou dit geweldig zijn!

groet Gesus

 

[snb]

In welk voorbeeldbestand van jou moet dat gezet worden ?

 

[Gesus]

Bekijk bijlage test Inhuur 2015.xlsm@ SNB

Hierbij het file, waarbij het gaat om de formule in cel T4 (nu nog de formule via VBA, waarbij de verandering van achtergrondkleur (handmatig door mij) niet een trigger event is).

Dank alvast!

met vriendelijke groet,
Gesus

 

[Gesus]

@ SNB

Mocht je nog meer info willen om te zien of het probleem buiten VBA om is op te lossen (ben het nog niet tegengekomen op het web ), dan hoor ik het graag. Suc6!

Met vriendelijke groet,
Gesus

 

[snb]

in T4:

=SUM(OFFSET(E4;0;0;;MONTH(DATEVALUE("01-"&H1&"2015"))-1))

@edmoor
De voorwaarde voor het kleuren van de cellen E4 t/m P4 is blijkbaar: voorafgaand aan de maand van H1.
Die voorwaarde kun je gewoon in de formule opnemen. De kleuring van de cellen is daarmee overbodig.

 

[Gesus]

@snb

Nee helaas, het voorbeeld is maar één regel. Het volledige file bestaat ui 100 + regels, waarbij soms alleen tm maart is betaald, dan voor een andere regel tm mei en soms helemaal niet. Dat is dan ook de reden dat ik de cellen geel arceer. In jouw voorbeeld ga je ervan uit dat er altijd één maand mist.. Helaas dus.

Heb je nog een oplossing zonder VBA? Suc6! En alvast dank voor de moeite...

Met vriendelijke groet, Gesus

 

[VenA]

#10 blijkbaar gemist. En een voorbeeldje plaatsen met één regel? Dan kan er toch geen formule op los gelaten worden die voor jouw hele bestand gaat werken. Dus plaats een representatief voorbeeld.

 

[pixcel]

Wijzig de maandlabels in maandnummers 1..12.
Neem in de tabel een kolom "Betaald tem" op en vul die per stelpost met de maandnummer.
Gebruik de maandnummers en het nieuwe veld in je som.als criterium met "<="

 

[Gesus]

Bekijk bijlage test Inhuur 2015.xlsm

Goedemorgen allen,

Hierbij een voorbeeldfile, waarin hopelijk alle hierboven gemaakte en nog niet beantwoorde vragen duidelijk worden.
Ik wacht graag jullie reactie af over een formule zonder VBA, waarbij ik dus geen trigger in VBA nodig heb.

dank alvast.

met vriendelijke groet,
Gesus