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.

 

[sylvanu]

Ne touchez pas à son fichier tant que les modifs n'ont pas été validées sur le fichier test.
J'ai vu qu'on pouvait encore gagner 0.4s sur le temps G.
A chaque fois qu'on va sur G on copie la liste des agents et on la trie ( 400ms)
Si la liste dans Paramètre était déjà triée, on pourrait gagner ce temps.
Mais chaque chose en son temps.
Sur PréPaye raffraichir, je ne vois pas comment gagner du temps.

 

[ANTONY34200]

en V46
J'ai rajouter toutes les macros sauf celle ci : 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, car elle a besoin de cette aide de surbrillance dans la date surtout si elle intervient dans le site 30, ca lui évite de rechercher l'erreur.

 

[ANTONY34200]

Les premiers tests effectués ont l'air concluant

 

[ANTONY34200]

J'ai un soucis sur la création d'un nouvel agent.
dans G il m'infome que pomme à fait 2 heures, normal je l'ai planifié 2h
j'ai créé poire il m'informe que poire a fait 2 heures, alors que je ne l'ai pas planifié

 

[ANTONY34200]

es-je mal copier coller le code vba

 

[sylvanu]

1- Oups, petit bug de ma part. Dans CumulTempsMensuel, il faut évidemment effacer les 2 colonnes.

' Effacement liste agents et temps
Sheets("G").Range("B6:C100").ClearContents

En fait il ne trouver pas Poire=2, mais il restait l'ancien Pomme=2

2- C'est génant, ça. Car c'était justement la destruction des dates qui ont posées problème. Je pense qu'il y a peut être une solution de contournement.

 

[sylvanu]

J'ai trouvé pour la protection des dates.
Vous laissez la macro telle quelle.
Vous protéger la zone dates de G, mais quand vous faites Protéger la feuille, vous cocher la case Format de cellule.
En d'autre termes le VBA peut modifier le format de la cellule (Jaune et gras rouge) mais on ne peut pas toucher aux valeurs.

 

[sylvanu]

Voici la dernière modif qui est améliore beaucoup les choses quand on accède à G.
1- Prenez la macro ci dessous :

Sub TriAgentsParametre()
' Tri des agents par ordre alpha dans Paramètres
    Sheets("Parametre").Range("H3:K93").Select
    ActiveWorkbook.Worksheets("Parametre").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Parametre").Sort.SortFields.Add Key:=Range( _
        "H4:H93"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Parametre").Sort
        .SetRange Range("H3:K93")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("G4").Select
End Sub

2- La copier à la fin des macros dans CreationFeuilles
3- Dans la macro CreationFeuilles RechercheNouveauAgent, mettre à la fin TriAgentsParametre après le Next i.

    Next i
' Tri des agents en alpha.
    TriAgentsParametre
' Feuille invisible
Worksheets("REF_AGT").Visible = False
End Sub

4- Dans Fonction_et_SousProgrammes CumulTempsMensuel, supprimez les deux lignes :

' Tri des agents par ordre alpha
TriDesAgents

( ou mettez TriDesAgents en commentaires )
5- Enregistrez, sortez, entrez.
6- Allez sur G, regardez le temps de rafraîchissement.
C'est tout.

 

[ANTONY34200]

La je ne vous suis plus, je suis perdu... je bloque sur la feuille G, je n'arrive pas la verrouillé, même en sélectionnant format de cellule ... sinon j'ai une fenêtre d'erreur.
Je vous passe la V47, que vous puissiez jeter un œil.
le soucis de userform = réglé
la protection de AGTREF = réglé
le reste je patauge ...

 

[sylvanu]

Et voilà !
En fait certaines modifs sont liées. En plus les listes de gauche étaient verrouillées.
Donc pour remettre ça dans votre fichier il faut tout prendre dans l'ordre.
J'y ai aussi introduit la dernière modif de Paramètre. Au global l'accès à G sera rapide.
En fait je fait le tri agent lors de la création de feuille. La liste est triée dans Paramètre.
Ce qui fait qu'en accédant à G le tri devient inutile d'où le gain de temps.

 

[ANTONY34200]

Super ... j'ai bien pris le temps de tester ... pour le moment tout à l'air de fonctionner correctement.
juste un petit bug dans G en cliquant sur une des lignes de E1 à QL4, sa ouvre la fiche du dernier agent de la liste ...

 

[ANTONY34200]

et la date ne peux plus etre changé

 

[ANTONY34200]

enfin, si la date se change mais j'ai une fenetre d'erreur

 

[sylvanu]

1- Ce n'est pas un bug. Comme E1 QL4 sont protégé, il valide la dernière cellule cliquée. Donc si celle ci est sur un agent il l'ouvre.
2- C'est le problème quand on verrouille. Il y a toujours un cas auquel on n'a pas pensé.
Je pensais avoir tout vérifié ....
Je regarde.

 

[sylvanu]

Dans feuille G, modifiez Worksheet_Change pour une déprotection lors du repérage de chevauchement :

Sub Worksheet_Change(ByVal Target As Range)
    ' Si dans la zone de saisie de planning
    If Not Intersect(Target, Range("E6:QL37")) Is Nothing Then
        ' Si le contenu de cellule est numérique
        If IsNumeric(Target.Value) Then
            Sheets("G").Unprotect
            Reperer_Doublon
            Sheets("G").Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True
        End If
    End If
End Sub

dans Changement de date, deux modifs Protect et Unprotect :

' GENERAL
Application.StatusBar = "Traitement feuille G"
If Application.WorksheetFunction.CountA(Range("B6:B100")) < 28 Then             ' Si plus de 29 agants, on ne masque pas.
    Sheets("G").Unprotect

et

Next feuille
Sheets("G").Protect Password:="", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.StatusBar = ""
Bloque = 0
Calculate
End Sub

Je pense que vous allez trouver d'autres cas tordu à l'utilisation. C'est le problème des protections avec accession cellules par VBA. il faut penser à tout.
La V47V3 avec les modifs ci dessus.