XL 2013 Supprimer des sauts de ligne vierge d'une cellule qui contient une formule

[ANTONY34200]

Bonjour,
j'ai créé un planning sous excel, pour ma femme qui reprend une PME en sécurité privée, ou je pense avoir déjà bien bossé dessus.
Je ne suis pas un expert, mais j'ai créé ce planning en consultant pas mal de forum. Le planning est fonctionnel, MAIS, car il y a un mais, j'aimerais y apporter quelques améliorations.
Le fichier joint, comporte 3 onglets (paramètre, général et agt 5)

L'onglet général, est en fait, le planning général, et c'est la que toutes les informations sont saisies.

L'onglet AGT 5, est le planning individuel de l'agent. Des cellules D8 à D39 ont une formule qui appelle le nom du site SI l'agent est planifié dans le planning général, pareil pour les heures de début (F8 à F39) et les heures de fin (G8 à G39).

Mon soucis est que j'aimerais, dans les cellules D8 à D39, F8 à F39, et G8 à G39, quand l'agent est planifié, les sauts de lignes inutiles ou vierges (dans les cellules)soient supprimées, sinon les cellules sont bien trop grande.

J'aimerais aussi savoir s'il est possible de classer par ordre chronologique d'heure de début (dans la ligne) et que les sites et les horaires correspondent.

Je ne suis pas expert, mais je pense toucher un peu, mais la... je bloque. j'ai cherché un peu partout une piste, mais je n'ai rien trouvé.
Si quelqu'un pouvait m'aider un peu ... se serai avec plaisir.
A bientôt, et merci d'avance.

 

[ANTONY34200]

Je pense que oui. la je teste en réel ... je reviendrais vers vous en cas de besoin ...
Encore merci pour aide formidable

 

[ANTONY34200]

Bonjour Sylvanu,

voila 4 jours que le fichier est mis en place et au réel ... et elle le trouve super et très bien amélioré.
elle aimerais toute fois pouvoir y apporter d'autre petit point de confort et sécurité.

1- est-il possible de laisser le UserForm mémo ouvert tout en modifiant dans les onglets AGT, G ...

2- là, c'est moi qui demande, est-il possible de "verrouiller" ou "protéger" les cellules qui ne doivent pas être touché ... sur tous les onglets ou sinon, à chaque suppression, faire ouvrir une fenêtre écrit "voulez vous vraiment supprimer cette saisie."
ex concret de cette après midi, elle a supprimé les dates dans le G, par inadvertance ... et a fait bugger Excel, elle a fermé puis ouvert et refermé puis ré-ouvert après les bug, donc la sauvegarde était avec les bugs.

Le fichier étant mis en place avec des données confidentiel, je ne paux pas vous le faire passer.
S'il y a des codes a ajouter, pourriez vous me dire comment faire pour les intégrer dans son fichier actuel.
a bientôt de vous lire.

 

[sylvanu]

Bonsoir,
Dans un logiciel il y a toujours une phase de test puis un déploiement sur le terrain.
Et il est rare que cela se passe sans anicroches. Tout simplement parce que l'utilisateur peut faire des chose qu'on a pas imaginé à la conception.
1- Je regarde demain.
2- Elle n'a pas compris la stratégie de sauvegarde.
Lorsqu'on fait une erreur, on sort mais on ne ré-rentre pas. Il suffit de prendre le fichier Z-Ouverture, ce fichier était bon. Le fait de ré-ouvrir le fichier courant écrase le fichier d’entrée.
Peut être faut il mettre une confirmation d'écriture en sortant ?
3- On peut évidemment verrouiller des cellules. C'est simple je vous montrerais comment faire.
Le gros souci c'est si vous voulez verrouiller des cellules qui devront être modifiées par le VBA.
Il va falloir déverrouiller ces cellules, les modifier puis les reverrouiller. Pas mal de modifs dans le VBA.
Par contre verrouiller les dates de G c'est très simple. Je vous ferais un tuto demain ainsi vous maîtriserez.

 

[ANTONY34200]

Oui je savais très bien qu'il y aurait quelques soucis, mais là, c'est des bases ...Et autre point, je savais que le bouton rafraîchir dans le G allait la bloquer au début et lui faire avoir des frayeurs ... mais ça commence à rentrer ... elle s'adapte difficilement, mais commence a comprendre le but de ce bouton.

Non, effectivement, malgré une petite formation de 1h45, elle n'a pas compris le système de sauvegarde, pourtant j'avais pris le temps d'expliquer ...

concernant le verrouillage, il faudrait éviter de déverrouiller ... si ce n'est pas faisable, faire ouvrir une fenêtre de sécurité en validant la suppression demandé ... je ne sais pas qu elle sécurité y ajouter, la plus simple soit-elle, pour éviter les suppressions par mégarde.

Pour info, actuellement, elle utilise 38 sites, plein au 3/4 chacun, 25 agents.
le temps de rafraîchissement du G est de 3300 ms environ
le temps de rafraîchissement de Pré-Paye est de 4890 ms environ
le temps d'ouverture d'un onglet AGT xx est de 530 ms environ
Tous ces temps sont acceptable, me dit-elle, vu le travail que fait le logiciel (car je suis rentrer dans quelque détail pour expliquer ce que fait le logiciel

Elle a beaucoup apprécié la façon d'exporter les pré-paye (choix des agents, l'ordre alphabétique ...), la façon d'imprimer le planning général et les cellules de l'onglet AGT (avec mise en forme automatique et le tri par ordre chronologique) d’ailleurs petit bug sur la ligne pour le 31/05 dans AGT, la cellule ne se met pas en forme automatiquement.

Merci de ce nouveau coup de main ...

 

[sylvanu]

Sur G, le verrouillage sera simple sans impact sur le VBA.
Sur PréPaye aussi, car tout se reconstruit donc il suffit de verrouiller que le premier AGT.
Le problème va être pour les feuille AGT, il faut réfléchir un peu.

 

[ANTONY34200]

je me suis mal exprimer. prepaye rien a toucher c est deja verrouillé. c est dans G le plus important pour les calcul des heures en face du site et dans agt ce n ai pas si grave dans l absolu si ce n ai pas verrouillé. c est juste pour ne paq toucher les formules dans les calcul des montants brut et net. j avais laisser en rouge les seul cellule a modifier mais certaine ont deja ete supprimées

 

[sylvanu]

Donc vous savez verrouiller les cellules ?

 

[ANTONY34200]

oui. j ai selectioné les cellules a verrouiller pouis j ai protéger la feuille mais en créant un nouvel agent via le bouton dans parametre, le nouvel agent ne c est pas créer

 

[sylvanu]

Voir PJ pour protection G.
Pour AGT le VBA va écrire dans D5.
Sinon il utilise la feuille REFAGT qui est masquée, donc on devrait pouvoir cérer des AGT sans problème.
Il va aussi écrire dans plein de cellules lors de la selection de feuilles.Il faudra donc rajouter quelques lignes de VBA pour dévérouiller puis revérouiller.
Je regarde demain.

 

[ANTONY34200]

ca marche. merci beaucoup

 

[sylvanu]

Pour l'userform toujours visible, dans ThisWorkbook et dans BoutonUseFormMémo,
changez Mémo.Show vbModal en Mémo.Show 0
Ça devrait marcher.
Un point de résolu !

 

[sylvanu]

Bonjour,
Si on protège G Colonne D (Dates) il faut empêcher la macro de mise en forme de mettre en jaune ces cellules.
Donc dans Reperage_Chevauchement il faut supprimer les lignes :

Ligne 68 :  Cells(i, 4).Interior.Color = vbYellow ' Colonne D
Ligne 69 :  Cells(i, 4).Font.Color = vbRed        ' Colonne D

 

[sylvanu]

Re,
Dans chaque feuille AGT , Déprotéger cellules X9:AA17 ( commentaires )
( Le faire aussi et surtout dans REF_AGT qui est masquée )
Dans VBA Prog_UpdateSheet/Sub UpdateSheet() :

Au début après la décalration Sub :
Sub UpdateSheet()
' Supprimer mot de passe
    ActiveSheet.Unprotect ("AGT1234")

et à la fin avant EndSub

' Remettre mot de passe
ActiveSheet.Protect Password:="AGT1234", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Dans A_BlocNotes rajouter : ' Mot de passe AGTxx : AGT1234

NB : J'ai pris le parti de ne pas protéger la feuille AGT à la création. A la première selection elle sera protéger automatiquement, ce qui limite les modifs VBA.

Testez. Normalement toute feuille AGT est verrouillée mais devrait se comporter normalement pour les mises à jour.

 

[sylvanu]

Re,
Comme on a travaillé au fil de l'eau, à la fin on s'aperçoit qu'on pouvait simplifier.
Par ex dans G je regarde chaque feuille pour avoir le nom de l'agent, et j'utilise ces feuilles pour calculer les temps.
Or maintenant les agents sont dans Paramètre puisque maintenant c'est paramètre qui crée les feuilles. de plus j'ai l'index AGT dans la colonne J pour créer les liens hypertexte.
Donc j'ai pu optimiser le temps d'accès à G. J'obtiens un gain de temps de l'ordre de 60%. Soit pour Madame un accès à G qui passerait de 3300ms à 1400ms. Ce qui est non négligeable.
De plus la modif VBA est très simple pour vous :

1- Prendre votre fichier et le dupliquer, puis le renommer.
2- Accéder à feuille G, et noter le temps.
3- Aller dans le VBA Fonction_et_SousProgrammes
4- Supprimer de Sub CumulTempsMensuel() ( ligne 8 ) à EndSub ( ligne 55 )
5- Remplacer par la macro ci dessous :

Sub CumulTempsMensuel()
t0 = Timer
' Accélération par inhib events
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
' Effacement liste agents et temps
Sheets("G").Range("B6:B100").ClearContents
' Construction liste agents
Nagents = Application.WorksheetFunction.CountA(Sheets("Parametre").Range("H4:H100"))
For N = 1 To Nagents
    NouveauNom = Sheets("Parametre").Range("H" & 3 + N)
    IndexAGT = Sheets("Parametre").Range("J" & 3 + N)
    Sheets("G").Range("B" & 5 + N).Select
    Cible = "'" & "AGT " & IndexAGT & "'!$D$5"
    ' Insertion lien hypertexte
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
        SubAddress:=Cible, TextToDisplay:=NouveauNom
Next N
' Tri des agents par ordre alpha
TriDesAgents
' transfert tableau planning dans array
tablo = Range("D6:QL36")
' Calcul des temps par agent
For Agent = 1 To Nagents                                            ' Pour tous les agents
    NomAgent = LCase(Cells(Agent + 5, 2))
    Durée = 0
    For Jour = 1 To 31                                              ' Pour tous les jours
        For Sites = 1 To 90                                         ' Pour tous les sites
            Nom = tablo(Jour, 5 * Sites - 3)                        ' Colonne du nom
            If LCase(Nom) = NomAgent Then                           ' Si c'est l'agent concerné
                Tdeb = tablo(Jour, 5 * Sites - 3 + 2)               ' Recup temps de début
                Tfin = tablo(Jour, 5 * Sites - 3 + 4)               ' Recup temps de fin
                If Tfin <= Tdeb Then Tfin = Tfin + 1                 ' Si fin < début on rajoute 24H
                Durée = Durée + Tfin - Tdeb                         ' Ajout du temps à la durée
            End If
        Next Sites
    Next Jour
    If Durée * 24 > 0 Then
        Cells(Agent + 5, 3) = Durée * 24                            ' on affiche que s'il y a un temps
    End If
Next Agent
' Retour events
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.StatusBar = "Temps de calcul page G : " & Round(1000 * (Timer - t0), 0) & "ms"
End Sub

6- Enregistrer le fichier, sortir puis le ré ouvrir
7- Accéder à la feuille G, noter le temps. Y a t-il un gain ?
8- Si ça marche et que le gain est non négligeable, vous êtes bon pour un re test complet !

 

[ANTONY34200]

Bonjour,
Merci pour les infos et modifs.
je m'occupe de ça se soir, car en journée je ne peux pas intervenir dessus, elle en a besoin.
je vais tester dans un premier temps sur le fichier test.
encore merci