Entrez l'heure sans deux points - Conseils Excel

Table des matières

La question Excel de cette semaine vient de John en poste à Okinawa.

Je construis une feuille de calcul Excel pour refléter les départs et les arrivées. Il y aura essentiellement trois cellules: heure réelle de départ, heure estimée en route et heure d'arrivée estimée. Je voudrais que la personne puisse simplement entrer (par exemple) 2345 et que la cellule formate automatiquement l'affichage pour afficher 23:45. Ce que j'obtiens à la place est 0:00, quelle que soit la formule ou la mise en forme. Et, le calcul n'affichera rien d'autre que 0:00 si l'utilisateur ne parvient pas à déplacer la clé et les deux points. Je sais que cela semble simple à faire, cependant, chaque petite seconde économisée compte, en particulier lors de la saisie de données similaires maintes et maintes fois dans Excel.

Pour que cela fonctionne, vous devez utiliser un gestionnaire d'événements. Les gestionnaires d'événements étaient nouveaux dans Excel 97 et ont été abordés dans Exécuter une macro chaque fois qu'une valeur de cellule change dans Excel. Cependant, dans cette astuce, le gestionnaire d'événements appliquait un format différent à certaines cellules. Cette application est légèrement différente, revisitons donc le gestionnaire d'événements.

Un gestionnaire d'événements est un petit morceau de code de macro qui est exécuté chaque fois qu'un certain événement se produit. Dans ce cas, nous voulons que la macro s'exécute chaque fois que vous modifiez une cellule. Pour configurer un gestionnaire d'événements, procédez comme suit:

  • Un gestionnaire d'événements est associé à une seule feuille de calcul. Commencez à partir de cette feuille de calcul et appuyez sur alt-F11 pour ouvrir l'éditeur VB.
  • Dans la fenêtre supérieure gauche (Projet - Projet VBA), double-cliquez sur le nom de votre feuille de calcul.
  • Dans le volet droit, cliquez sur le menu déroulant de gauche et remplacez le général par Feuille de calcul.
  • Dans le menu déroulant de droite, choisissez Modifier.

Cela obligera Excel à pré-entrer le shell de macro suivant pour vous:

Private Sub Worksheet_Change(ByVal Target As Range) UserInput = Target.Value If UserInput> 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End Sub

Chaque fois qu'une cellule est modifiée, la cellule qui a été modifiée est transmise à ce programme dans la variable appelée "Cible". Quand quelqu'un entre une heure avec un deux-points dans la feuille de calcul, il sera évalué à un nombre inférieur à un. Le bloc If s'assure de ne changer les cellules que si elles sont supérieures à un. J'utilise les fonctions gauche () et droite () pour diviser l'entrée de l'utilisateur en heures et minutes et insérer un deux-points entre les deux.

Chaque fois que l'utilisateur entre "2345", le programme change cette entrée en 23:45.

Améliorations possibles

Si vous souhaitez limiter le programme pour qu'il ne fonctionne que sur les colonnes A&B, vous pouvez vérifier la valeur de Target.Column et n'exécuter le bloc de code que si vous êtes dans les deux premières colonnes:

Private Sub Worksheet_Change(ByVal Target As Range) ThisColumn = Target.Column If ThisColumn 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End If End Sub

Si jamais vous souhaitez apporter des modifications à la feuille de calcul sans entrer les deux-points (par exemple, si vous devez ajouter des formules ou modifier les en-têtes, etc.), vous pouvez désactiver le gestionnaire d'événements avec cette courte macro:

Sub TurnEventHanderOff() Application.EnableEvents = False End Sub You can turn event handlers back on with this macro: Sub TurnEventHanderOff() Application.EnableEvents = True End Sub

Si vous prenez ce concept et le changez, il y a un concept important dont il faut être conscient. Lorsque la macro du gestionnaire d'événements affecte une nouvelle valeur à la cellule référencée par Target, Excel compte cela comme une modification de feuille de calcul. Si vous ne désactivez pas brièvement les gestionnaires d'événements, Excel commencera à appeler le gestionnaire d'événements de manière récursive et vous obtiendrez des résultats inattendus. Avant d'apporter une modification à une feuille de calcul dans un gestionnaire d'événements de modification, veillez à suspendre temporairement la gestion des événements avec la ligne Application.EnableEvents.

Articles intéressants...