NBVAL qui ne fonctionne plus après VBA

[Gil_Se]

Bonjour à tous.
Pour compter les cellules non vides d'une zone nommé "NOM", j'ai utilisé dans une autre cellule la formule:
=NBVAL(nom) qui fonctionne très bien.

J'ai ajouté dans VBA pour le contenu des cellules soit en majuscule:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("NOM")) Is Nothing Then
Target = UCase(Target)
End If
End Sub

Et depuis la formule NVBAL ne fonctionne plus: le résultat ne fait qu'augmenter et ne diminue pas même si je supprime le contenu des cellules de la zone "NOM".

Merci d'avance.
Gilles

 

[kjin]

Re : NBVAL qui ne fonctionne plus après VBA

Bonsoir,
Le fichier avec qq données serait plus parlant
A+
kjin

 

[Gael]

Re : NBVAL qui ne fonctionne plus après VBA

Bonsoir Gilles, bonsoir à tous,

Je ne comprends pas le problème de NBVAL(), par contre, comme ta procédure est évènementielle, si tu modifies la source (Target), la procédure va se relancer. Pour éviter ça, il faut désactiver temporairement la gestion d'évènements sans oublier de la réactiver après:

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=red]Application.EnableEvents = False[/COLOR]
If Not Intersect(Target, Range("NOM")) Is Nothing Then
Target = UCase(Target)
End If
[COLOR=red]Application.EnableEvents = True[/COLOR]
End Sub

@+

Gael

Et salut Kjin, collision...

 

[Gil_Se]

Re : NBVAL qui ne fonctionne plus après VBA

Bonsoir.
Un fichier joint pour mieux comprendre mon souci:
Le résultat en F2 ne diminue pas tant que la macro est active.
Gilles

 

[ERIC S]

Re : NBVAL qui ne fonctionne plus après VBA

Bonjour

nbval fonctionne, tes cellules ne sont pas considérées comme vides (le Ucase doit te générer un caractère non affiché

essayer
If Target.Value = "" Then Exit Sub
juste après private sub

 

[kjin]

Re : NBVAL qui ne fonctionne plus après VBA

Bonsoir,
Quand ça veut pas...
A+
kjin

 

[Gil_Se]

Re : NBVAL qui ne fonctionne plus après VBA

C'était la ligne qui manquait. Merci et bonne soirée.
Gilles

 

[ROGER2327]

Re : NBVAL qui ne fonctionne plus après VBA

Bonsoir à tous

UCase() s'applique à une chaîne de caractères ou à une valeur interprétable comme une chaîne de caractères. Dés lors que la variable Target fait référence à plusieurs cellules elle ne peut plus être interprétée comme une chaîne de caractères, d'où l'erreur engendrée par le code.
Il faut alors traiter chaque cellule séparément. En tenant compte de la remarque de Gael, un code comme celui-ci

[COLOR="DarkSlateGray"]Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCel As Range
   If Not Intersect(Target, Range("NOM")) Is Nothing Then
      Application.EnableEvents = False
      For Each oCel In Intersect(Target, Range("NOM")).Cells
         oCel.Value = UCase(oCel.Value)
      Next oCel
      Application.EnableEvents = True
   End If
End Sub[/COLOR]

devrait faire l'affaire.​

ROGER2327

 

[Gil_Se]

Re : NBVAL qui ne fonctionne plus après VBA

Bonjour
En conclusion j'ai mis en pratique le conseil d' ERIC S qui fonctionne, pour mon cas, parfaitement.
Merci à tous pour vos aides.
Gilles

 

[Lii]

Re : NBVAL qui ne fonctionne plus après VBA

Bonjour,

Quel conseil ?
Eric a raison quand il parle d'un "contenu" invisible mais ...
il est présent dans les 4 premières cellules (C3:C6) de ton fichier joint (dés son chargement). Pourquoi, je ne saurais le dire (fichier épuré sans doute).
Donc UCase n'est pas en cause.
Quand on supprime le contenu de ces cellules, une par une, il n'y a aucun changement : elles ne se vident pas et c'est cela qui m'interpelle !
Par contre en sélectionnant les 4 cellules en même temps, leur suppression agit même si l'événementielle rouspète (voir l'explication de Roger en cas de sélection multiple).
Le problème s'en trouve réglé ainsi : on retrouve le zéro attendu ((même s'il n'est pas affiché) !
Pour la macro, elle fonctionne donc telle quelle.
Je rajouterai, pour ma part, les 2 lignes proposées par Gaël pour éviter un appel récursif.

Private Sub Worksheet_Change(ByVal Target As Range)
   'pour éviter l'erreur de sélection multiple
   If Target.Count > 1 Then Exit Sub
   If Not Intersect(Target, Range("NOM")) Is Nothing Then
    Application.EnableEvents = False
    'autre possibilité pour éviter l'erreur de sélection multiple
    'On Error Resume Next
    Target = UCase(Target)
    Application.EnableEvents = True
  End If
End Sub

Je préfère éviter la seconde possibilité quand je connais l'erreur.

 

[Gil_Se]

Re : NBVAL qui ne fonctionne plus après VBA

Je ne suis vraiment pas un "pro" d'excel.
J'ai juste rajouté cette ligne:
If Target.Value = "" Then Exit Sub
et depuis cela fonctionne très bien.
Et je suis bien incapable de savoir pourquoi.
Gilles

 

[ROGER2327]

Re : NBVAL qui ne fonctionne plus après VBA

Bonsoir à tous

L'explication de Lii n'est pas tout à fait exacte :

Quand on supprime le contenu de ces cellules, une par une, il n'y a aucun changement : elles ne se vident pas et c'est cela qui m'interpelle !
Par contre en sélectionnant les 4 cellules en même temps, leur suppression agit même si l'événementielle rouspète

La réalité est un peu différente. Dans les deux cas, les cellules sont effectivement vidées.
Mais dans le premier cas, la procédure

[COLOR="DarkSlateGray"]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("NOM")) Is Nothing Then
Target = UCase(Target)
End If
End Sub[/COLOR]

s'exécute correctement. Par conséquent, la cellule prend immédiatement la valeur de UCase(Target), c'est-à-dire "" (chaîne vide). Comme la cellule est au format Texte, la chaîne vide est considérée par Excel comme une valeur de texte et NBVAL la comptabilise.
Dans le deuxième cas (sélection multiple), la procédure plante et UCase(Target) n'est pas exécutée, donc aucune cellule ne prend la valeur "".
Si, au lieu de la procédure ci-dessus, vous utilisez celle que je propose, vous constaterez qu'en cas de sélection multiple, les cellules visées prennent toutes la valeurs "" et sont décomptées par NBVAL.
Si vous remplacez le format Texte par le format Standard, vous constaterez que les choses changent. UCase(Cellule) continue de placer une chaîne "" dans Cellule, mais le format n'étant plus le format Texte, Excel interprète la chaîne vide comme étant la valeur Empty.
C'est subtil, mais faites l'expérience et vous verrez tout cela.​

ROGER2327

 

[Lii]

Re : NBVAL qui ne fonctionne plus après VBA

Re

Merci Roger d'avoir répondu à mon interpellation et surtout d'y avoir apporté l'explication qui me manquait.
En fin de compte, il m'est difficile de travailler sur du vide quand je n'ai pas la "forme" standard !