Waarde numberformat meenemen in formule

[Ibok]

Ik loop tegen het volgende probleem:

In de cellen D5, D6 en D7 zet ik een tekst neer, bijvoorbeeld: aan
In cel AJ5 staat een formule: =aantal.als die de som geeft van de gevonden waarden: aan. Dit werkt perfect!

Nu wil het geval dat in sommige cellen wel de waarde moet staan, maar deze niet meegeteld mag worden. Als ik de cel handmatig aanvul met een asterisk, wordt de cel niet meegenomen. Dit werkt ook goed, echter ik wil dit graag via een checkbox op een formulier aangeven.

Via de code:

Selection.NumberFormat = "@\*"

lukt mij dit ook en is in de cel de asterisk zichtbaar / onzichtbaar afhankelijk van checkbox. Echter in de formule wordt de asterisk niet gezien en telt hij alsnog de waarde mee!

Heeft iemand een idee hoe dit te realiseren?

 

[Ibok]

hmm, toch lastiger dan ik dacht?

Weet iemand mischien anders een oplossing om bij:

Private Sub Worksheet_Change(ByVal Target As range)

een code op te nemen die zoekt naar alle cellen met de numberformat @\* in het actieve werkblad en aan deze cellen een * toevoegd?

Ik dacht zoiets als:

select case numberformat

case "@\*"
activecell.value = activecell.value & "*"
case else
activecell.value 'en dan moet ie een eventueel nog aanwezige * verwijderen
end select

alvast bedankt voor het meedenken!

 

[HSV]

Ik denk dat als je een voorbeeldbestandje maakt, er meer kans is op een reactie.

 

[Ibok]

Dat is inderdaad geen slecht idee :thumb:

Wat de volledige bedoeling is:

Op het tabblad lessenverdeling-ob, kunnen afkortingen van medewerkers ingevoerd worden. Rechts is per leerjaar te zien hoevaak de medewerker per leerjaar is ingepland (er zijn in het orginele bestand zo nog een aantal tabbladen die de aantallen tellen van andere leerjaren).

Via het userform (op te roepen door op test te drukken) wil ik eventuele "clusters" aangeven tussen klassen. Deze klas krijgt de noemer cluster1 grijs gearceerd of cluster 2 blauw gearceerd (dit moet ik nog verder uitbouwen naar circa 10 clusters).

Voor een medewerker betekend dit echter, dat wanneer deze bijvoorbeeld is ingepland op 1a1 en 1a2 en deze klassen vormen een cluster (cluster1), dan moeten de uren van de medewerker bij de tweede klas (of derde, vierde, etc.) waar cluster1 voor staat, niet geteld worden in het totaal en voorzien worden van een *.
Dit kan met het "handje" door een * achter de naam te zetten, maar ik wil dit creeren door in het userform de checkbox aan / uit te vinken. Het lukt mij om via numberformat automatisch een * te krijgen, echter dan wordt deze alsnog meegenomen in de formule, wat dus niet de bedoeling is. Het lukt mij daarnaast ook nog niet dat de code zoekt naar cluster1 en dan de eerst gevonden klas (in het voorbeeld 1a1) de waarden telt en de tweede klas die gekoppeld is aan cluster1 (in het voorbeeld 1a2) de waarden niet telt.

Volgende probleem waar ik nog niet uit ben is dat dus alleen dubbele medewerkers van bijvoorbeeld cluster1 niet geteld moeten worden, het kan dus zo zijn dat in cluster1 op klas 1a1 een medewerker volledig is ingepland en voor de helft in klas 1a2. Zie ook het voorbeeld.

De medewerker "aan" zou dus bij klas 1a2 genoteerd moeten staan met een * en als totaal aantal uren in cel "G5" zou 5 moeten staan.

De medewerker "abn" zou bij klas 1a2 zonder * moeten staan en als totaal aantal uren in cel "G6" zoals nu ook het geval is 3 moeten hebben.

Ik hoop dat ik een beetje duidelijk ben en dat iemand mij kan helpen! Alvast bedankt!

poeh, het bestandje behoorlijk aangepast omdat het eerst 2,5mb was en ik maar 100kb kan uploaden! hoop dat het nog duidelijk is!

Bekijk bijlage Map4.xls

 

[Ibok]

Het bestandje werkte niet helemaal goed, nu een aangepaste versie

Bekijk bijlage Map4(1).xls

 

[HSV]

Waarom gebruik je een jokerteken?
Ook zet je het in de opmaak, dus zal er niets veranderen.

Probeer het zo eens.
Zo zet het er een tilde achter, en weer uit de cel.

Select Case CheckBox1.Value
''''''''
Case True
Cells.Find("cluster1").Offset(0, 2).Select
With Selection.Cells(Selection.Cells.Count)
Selection = Format(Selection) & "~"
'activecell.FormulaR1C1 = activecell.Value & Range("ah5").Value
Sheets("parameters").Range("BG4") = CheckBox1.Value
End With
    'activecell.FormulaR1C1 = activecell.Value & "*" '' Range("ah5").Value
Exit Sub
    Case False
Cells.Find("cluster1").Offset(0, 2).Select
With Selection.Cells(Selection.Cells.Count)
origineel = Mid(Selection, 1, Len(Selection) - 1)
    Selection = Format(origineel)
Sheets("parameters").Range("BG4") = CheckBox1.Value
    End With
    Exit Sub
    End Select

End Sub

 

[Ibok]

Jokerteken? Ik weet niet wat je bedoeld?

Als ik de code nu uitvoer krijg ik de melding dat een variabele

orgineel =

niet is gedefineerd. Wat doe ik fout?

 

[HSV]

Private Sub checkbox1_change()

Dim i, a As Integer
Dim ii, b As Integer
[COLOR="red"]Dim origineel As String[/COLOR]

 

[Ibok]

Tot laat bezig geweest, maar het geeft helaas toch nog niet het gewenste resultaat...
Als ik namelijk het userform opnieuw opstart, dan komt er weer een streepje bij te staan.

Ik zit toch meer te zoeken naar een oplossing in het werkblad zelf, via Private Sub Worksheet_Change(ByVal Target As range). Dus als er een cel wordt aangepast, moet gezocht worden binnen het werkblad naar cellen met het format @\* en moet er een ~ in komen. Wanneer het format normaal is, moet een evt. aanwezig ~ weggehaald worden.

Wanneer een gebruiker het vinkje heeft aangezet, moeten er namelijk een aantal cellen in het werkblad waar cluster1 aan gekoppeld is, een ~ krijgen. In mijn voorbeeld gaat het nog maar om 1 cel.

Ik heb nu het volgende staan:

Private Sub Worksheet_Change(ByVal Target As range)

 If Target.Cells.NumberFormat = "@" Or IsEmpty(Target) Then Exit Sub
      
    If Target.Address = "@\*" Then
       'Ensure target is a number before multiplying by 2
        If IsNumeric(Target) Then
            'Stop any possible runtime errors and halting code
            On Error Resume Next
                'Turn off ALL events so the Target does not put the code into a loop.
                Application.EnableEvents = False
                Target = Target & "~"
                'Turn events back on
                Application.EnableEvents = True
            'Allow run time errors again
            On Error GoTo 0
        End If
    End If

End Sub

als de code echter uitgevoerd word, gebeurt er niets. Heb je hier toevalig suggesties voor?

 

[Ibok]

Yeah! Na wat puzzelen gaat het met de volgende code voor een groot deel goed:

Private Sub Worksheet_Change(ByVal Target As range)

Select Case Target.Cells.NumberFormat

Case "@"
MaxCharc = 3

If Len(Target.Text) > MaxCharc Then
TempTxt = Left(Target.Text, MaxCharc)
Application.EnableEvents = False
Target.Activate
activecell = TempTxt
Application.EnableEvents = True
On Error GoTo 0
End If

Case "@\*"
If Target.Cells.NumberFormat = "@\*" Then
Application.EnableEvents = False
Target = Target & "~"
Application.EnableEvents = True
End If

MaxCharc = 4
If Len(Target.Text) > MaxCharc Then
TempTxt = Left(Target.Text, MaxCharc)
Application.EnableEvents = False
Target.Activate
activecell = TempTxt
Application.EnableEvents = True
On Error GoTo 0
End If

End Select

End Sub

Enige probleem is, dat ik nog handmatig in de cel moet gaan staan en vervolgens een enter moet geven, om de code te starten. Is het mogelijk om de code over alle cellen binnen bijvoorbeeld het bereik "D4 : D44" en "D48 : D77" uit te voeren zonder dat ik al deze cellen handmatig moet gaan "enteren"?

 

[Ibok]

Niemand een idee hoe bij een workbook change alle cellen te verversen zijn?

Anders zet ik de vraag op opgelost want watchdog = watching me