Voorwaardelijke opmaak met tekst

[jwprograms]

In een bestand zou ik graag in plaats van voorwaardelijke opmaak met kleur een tekst venstertje met een melding krijgen. Ik gebruik nostalgisch Excel 2003. Wat precies de bedoeling is blijkt uit bijgaand voorbeeldbestandje.

Ik ben benieuwd of dit kan.

CorBekijk bijlage Uitgifte voorbeeld.xls

 

[HSV]

Zet onderstaande code achter de bladmodule in de Vb-editor.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "O7" Then
  If Target.Value > Target.Offset(, -2).Value Then 
     MsgBox "Maximaal uit te geven " & Target.Offset(, -2).Value, , "Oeps"
     target.clearcontents
  end if
End If
End Sub

 

[jwprograms]

Hallo HSV

Bedankt voor je antwoord. Dit is wat ik bedoelde, en het werkt goed, maar ik heb het geloof ik niet duidelijk genoeg omschreven. Ik heb geprobeerd dit te repeteren omdat het blad waarop het toegepast moet worden ongeveer 150 regels heeft, maar het lukt me niet het in de andere cellen de popup te laten verschijnen.
Is daar een oplossing voor?

Grt, Cor

 

[HSV]

Hallo Cor,

Daar is wel een oplossing voor.

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect(target,range("o7:o157")) is nothing Then
  If Target.Value > Target.Offset(, -2).Value Then 
     MsgBox "Maximaal uit te geven " & Target.Offset(, -2).Value, , "Oeps"
     target.clearcontents
  end if
End If
End Sub

 

[jwprograms]

Hallo Harry,

Ik ben bezig geweest de code in mijn bestand toe te passen maar loop toch tegen een probleempje aan.
De kolommen verschillen t.o.v. mijn voorbeeld. Ik heb al geprobeerd de code te wijzigen maar het geeft nog steeds niet het juiste resultaat. Ik heb voor het gemak het nieuwe bijgaande voorbeeldje precies in de juiste kolommen geplaats zoals ze in mijn bestand zitten. Kan je er a.u.b. nog een keer naar kijken. Ik kan dan ook proberen de code te vergelijken en te zien wat ik fout deed.

Alweer bedankt en alvast prettig weekend.

Cor


Bekijk bijlage Uitgifte voorbeeld .xls

 

[HSV]

Dag Cor,

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K5:K146")) Is Nothing Then
  If Target.Value > Target.Offset(, 1).Value Then
     MsgBox "Maximaal uit te geven " & Target.Offset(, 1).Value, , "Let op"
     Target.ClearContents
     Else
     Target.Offset(, 1) = Target.Offset(, 1) - Target.Value
  End If
End If
End Sub

 

[VenA]

Best wel een lastige. Een soort kip en ei verhaal. Als je in kolom K een waarde invoert, dan wordt eerst de berekening in kolom L uitgevoerd en vervolgens gaat de code lopen. Als je zoals in het voorbeeld een voorraad hebt van 5 en je gaat vervolgens 3 uit de voorraad halen is de voorraad nog maar 2. En omdat 3 groter is dan 2 krijg je dus de melding.

Met deze code wordt het berekenen even op handmatig gezet dus wijzigt er niets in kolom L. Als je een waarde wijzigt in kolom K of een willekeurige cel selecteert buiten kolom K wordt het berekenen weer op automatisch gezet. Met kolom K bedoel ik de Range("K5:K146")

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("K5:K146")) Is Nothing Then Application.Calculation = xlManual Else Application.Calculation = xlAutomatic
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K5:K146")) Is Nothing Then
    If Target.Value > Target.Offset(, 1).Value Then
        MsgBox "Maximaal uit te geven " & Target.Offset(, 1).Value, , "Let op"
        Target.ClearContents
    End If
End If
Application.Calculation = xlAutomatic
End Sub

 

[HSV]

Kolom 'ontvangen' ook maar meegenomen.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Union(Range("I5:I146"), Range("K5:K146"))) Is Nothing Then
 If Target.Column = 9 Then
   Target.Offset(, 3) = Target.Offset(, 3) + Target.Value
  ElseIf Target.Value > Target.Offset(, 1).Value Then
     MsgBox "Maximaal uit te geven " & Target.Offset(, 1).Value, , "Let op"
     Target.ClearContents
     Else
     Target.Offset(, 1) = Target.Offset(, 1) - Target.Value
  End If
End If
Application.EnableEvents = True
End Sub

 

[VenA]

Wat natuurlijk ook weer aan te passen is in

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Union(Range("H5:H146"), Range("I5:I146"), Range("K5:K146"))) Is Nothing Then
    Select Case Target.Column
        Case 8
            Target.Offset(, 1).Resize(1, 4) = Array(0, "", 0, Target.Value)
        Case 9
            Target.Offset(, 3) = Target.Offset(, 3) + Target.Value
        Case Else
            If Target.Value > Target.Offset(, 1).Value Then
                MsgBox "Maximaal uit te geven " & Target.Offset(, 1).Value, , "Let op"
                Target.ClearContents
            Else
                Target.Offset(, 1) = Target.Offset(, 1) - Target.Value
            End If
    End Select
End If
Application.EnableEvents = True
End Sub

Aangezien een telling altijd leidend is.

Eigenlijk zou ik even naar de OP moeten kijken of dit nog overeen komt met de vraag. Maar ben zelf weer wat wijzer geworden

 

[jwprograms]

Bedankt Harry en VenA. Ik was er net achter dat het nog niet helemaal werkte zoals het moest want als ik een getal invoerde in uitgave verdween de formule in Actuele voorraad en kon ik niet terug. Maar...........het werkt nu helemaal goed. Ik heb nu alleen nog maar de laatste code van Harry gebruikt maar ik ga zeker ook de andere nog proberen al is het alleen maar om te ontdekken wat het verschil is en hoe een en ander in elkaar zit voor zover ik het kan lezen. Ik ga het ook nog op andere bladen toe proberen te passen waar de kolommen anders liggen, maar dan heb ik in elk geval weer wat huiswerk. Als ik er niet uit kom dan meld ik me weer.

Hartelijk bedankt.

Cor

 

[jwprograms]

Ik meld me dus toch weer. In eerste instantie leek het er op dat het goed ging, maar het gaat maar goed tot actuele voorraad verschillende keren is afgeteld tot bv 1. Dan verdwijnt de formule uit de cel in kolom L. Alle drie de formules doen dat op verschillende manieren. Ik heb geprobeerd de code te begrijpen en aan te passen maar dat is een stap te ver omdat het mij aan die kennis ontbreekt. Zou het op te lossen zijn als ik de volgorde van de kolommen en/of de formules zou veranderen? De formule in kolom L zou moeten blijven ook al is het het saldo 0.
Is het niet mogelijk, tja, dan toch maar met voorwaardelijke opmaak. Ben benieuwd of er nog iets is aan te passen.

Cor

 

[HSV]

De formule kan in mijn optiek verwijderd worden daar de code de telling voor z'n rekening neemt.

 

[VenA]

Je haalt twee termen door elkaar. Een formule is een formule en het andere is code. Zowel de code uit #8 als uit #9 haalt de formule uit L weg. Met als reden wat ik in #7 heb beschreven. Dus wat je nu schetst als probleem bestaat niet. Wat en hoe je het hebt toegepast heb je er niet bijgezet.

Wijzig in het voorbeeldje iets onder 'Telling', 'Ontvangen' of 'Uitgifte' en je zal zien dat de 'Actuele voorraad' aangepast wordt. Je zal ook zien dat er nergens een formule staat aangezien alles via code wordt afgehandeld.

 

[jwprograms]

Je hebt helemaal gelijk. Ik heb in mijn bestand nog een formuletje staan in kolom L wat Telling en Ontvangen optelt.
Ik had niet door dat die er gewoon uit kon en alles via code werd afgehandeld. Om te weten hoe er mee gewerkt gaat worden:
De Actuele voorraad is de som van Telling en Ontvangen. Resultaat bv 10. Dan worden er 5 uitgegeven. Resultaat actuele voorraad 5. Daarna worden er 3 uitgegeven. Het totaal Uitgegeven wordt dan 8. Die 3 worden bij de 5 opgeteld als uitgegeven. Dat getal "Uitgegeven" wordt dus verhoogt naar 8 terwijl er nog maar 5 als actuele voorraad staan. Daar ligt volgens mij het probleem. Dan krijg je de MsgBox Max uit te geven 5.
Sorry als ik het niet duidelijk genoeg heb omschreven, maar sta open voor suggesties, vandaar dat ik vroeg hoe ik het misschien anders kon doen. Maar ik vind afhandelen via code natuurlijk veel mooier als met formules in cellen.

Cor

 

[HSV]

Test het zo eens.

 

[jwprograms]

Harry,

Ik heb het getest, en ziet er goed uit en is ook bijna goed, alleen Uitgifte zou het resultaat moeten zijn van Telling + Ontvangen dus 20 en nu is dat 10. Als dat nog aangepast zou kunnen worden dan is het helemaal super.

Cor.

 

[HSV]

Even voor de goede orde Cor,

Je typt in K5 bv. een 3.
K5 moet dan de som geven van H5 en I5 ?

 

[jwprograms]

De actuele voorraad L5 moet de som zijn van H5+I5. (Ontvangen wordt bij Telling opgeteld) In jou voorbeeld zou dat dus 20 moeten zijn i.p.v. 10.
Als ik in K5 nu b.v. 5 invoer moet de actuele voorraad met 5 verminderen en wordt dus 15.
Als uiteindelijk K5 in dit voorbeeld 20 is, dan is L5 dus 0.

Jou laatste voorbeeld werkt dus goed m.u.v. het getal in actuele voorraad.

Vr. grt, Cor

Ik heb nu Union Range in de code veranderd van (H5:H146) naar (H5:I146) en het schijnt te werken. Alleen als ik nu het getal Ontvangen verhoog kan ik er meer uitgeven als totaal van H5+I5. Dat kan dus niet, maar ik denk dat ik daarvoor maar de tekst in de MsgBox aanpas.

 

[HSV]

Test het zo maar eens weer Cor.

Klopt van de Union, die kolommen liggen aan elkaar en kan zo toegepast worden.
Toch nog Union gebruikt anders valt kolom J daar ook in (bereik h5:i146, bereik k5:k146).