Lier des noms d'onglet avec des cellules

Bonjour à tous ,

Je suis depuis quelques temps sur un fichier excel qui a pour objectif d'être utilisé en entreprise. Ce classeur Excel doit servir à :

• indexer les plannings de nos équipes
• créer les plannings
• automatiser des feuilles d'heures par salarié

Tout cela en essayant d'être le plus lisible et le plus simple à utiliser possible. Je ne pense pas du tout être une experte du logiciel mais ceux qui l'utiliseront le sont encore moins, il est donc capital que ce soit simple d'utilisation.

Je commence donc par une première question concernant les onglets.

Un de mes onglets est l'onglet "Calendrier", dans celui-ci sont inscrits les 12 mois avec tous les lundis de ces mois. Tous cela est automatisé par des formules. Par exemple dans la colonne de "janvier", dessous apparaissent "02 janvier", "09 janvier", 16 janvier"... Je souhaite que ces cellules, prenons celle du "02 janvier" qui est la cellule B4 pour exemple, puisse être liée à un onglet qui porterai le nom de B4. Mieux si c'est possible, mais peut être que j'en demande un peu trop, que cette cellule puisse automatiquement créer un onglet avec un planning de la semaine vierge à l'interieur si ce planning n'a pas déjà été créé bien sur. Et j'aimerai aussi que cette cellule serve de bouton, que lorsqu'on clique dessus, on soit directement dirigé vers l'onglet en question.

Pour ce qui est de mon niveau en Excel, pour tout ce qui est fonction je n'ai pas de problèmes à comprendre la logique mais c'est une tout autre chose pour le VBA. Je ne m'y suis penchée qu'aujourd'hui car mon problème à l'air de pouvoir se résoudre grâce à la VBA... C'est vous dire mon niveau novice!

Je vous remercie d'avance du temps que vous prendrez pour m'aider. Je joint mon fichier si cela peut vous aider

Bien cordialement

Audrey.

Bonjour Audrey, bonjour le forum,

Pourrais-tu nous fournir un exemple de planning de la semaine avec les formules qui vont bien (moi ça serait plutôt au niveau des formules que je suis novice...). Ensuite on pourra coder pour la création et/ou l'accès au planning en double-cliquant sur la cellule de l'onglet Calendrier.

Re,

ooops ! Je n'avais pas bien regardé. Le planning hebdo existait déjà. En pièce jointe ton fichier modifié avec le code événementiel ci-dessous. Double clique dans une date du calendrier pour générer / atteindre le planning correspondant...

le code :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'au double-clic dans l'onglet
Dim V As String 'déclare la variable V (Valeur)
Dim D As Long 'déclare la variable D (Date)
Dim NO As String 'déclare la variable NO (Nom de l'Onget)
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim OP As Worksheet 'déclare la variable OP (Onglet Planning)

If Application.Intersect(Range("B4:M8"), Target) Is Nothing Then Exit Sub 'si le double-clic a lieu ailleurs que dans la plage B4:M8, sort de la procédure
Cancel = True 'évite le mode [Édition] lié au double-clic
V = Target.Value 'définit la valeur V
D = CLng(DateSerial(Year(V), Month(V), Day(V))) 'définit la date D (en entier long)
NO = "Planing " & Replace(V, "/", "_") 'définit le nom de l'onglet NO
For Each O In Sheets 'boucle sur tous les onglets du classeur
    On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligne suivante)
    Set OP = Worksheets(NO) 'définit l'onglet OP (génère une erreur si cet onglet n'existe pas)
    If Err <> 0 Then 'condition : si une erreur a été générée
        Err.Clear 'supprime l'erreur
        Worksheets("Plannings").Copy after:=Sheets(Sheets.Count) 'copie l'onglet "Plannings"  en dernière position
        Set OP = ActiveSheet 'définit l'onglet OP
        OP.Name = NO 'renomme l'onglet OP
        OP.Range("C2").Value = D 'renvoie la date en C2 de l'onglet OP
    End If 'fin de la condition
    OP.Select 'sélectionne l'onglet OP
    OP.Range("A4").Select 'sélectionne la cellule A4 de l'onglet OP
    Exit For 'sort de la boucle
Next O 'prochaine onglet de la boucle
End Sub

le fichier :

WAOUH!!!!

Merci beaucoup!! Je ne pensai pas que ce serai faisable et surtout pas aussi rapide!!

Du coup je continue sur ma lancée! Le planning qui devait être généré automatiquement était l'onglet "Edition de planning" , mais je n'ai pas répondu assez rapidement à votre question. J'ai donc corriger cela dans la version que je joins.

Dans ce classeur il y a une personne qui édite le planning avec l'onglet "Edition de planning", une autre qui le vérifie sur cet onglet et enfin il est lié à l'onglet planning pour pouvoir être exporté et distribué aux équipes. S'il y a 2 plannings différents c'est car sur l'onglet édition de planning, une case rouge apparait à côté si l'une de ces règles n'est pas respectée :

• un service est >= 3h
• l'amplitude horaire ne peut excéder 13h
• le total d'heures journalières ne peut être supérieur à 10h
• il doit y avoir au moins 11h entre deux jours consécutifs

Si ce planning est fait ainsi c'est car nous ne voulons pas perdre en lisibilité sur les plannings distribués aux équipes.

Que faut-il ajouter au code pour créer ces 2 onglets en même temps?

Petite question à part, ce format excel est-il lisible par toutes les versions Excel et par Open office?

Re,

Pas tous compris de tes explications (moi et les plannings ça fait 2...).

Essaie comme ça :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'au double-clic dans l'onglet
Dim V As String 'déclare la variable V (Valeur)
Dim D As Long 'déclare la variable D (Date)
Dim NO1 As String 'déclare la variable NO (Nom de l'Onget)
Dim NO2 As String 'déclare la variable NO (Nom de l'Onget)
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim OP1 As Worksheet 'déclare la variable OP1 (Onglet Planning 1)
Dim OP2 As Worksheet 'déclare la variable OP2 (Onglet Planning 2)

If Application.Intersect(Range("B4:M8"), Target) Is Nothing Then Exit Sub 'si le double-clic a lieu ailleurs que dans la plage B4:M8, sort de la procédure
Cancel = True 'évite le mode [Édition] lié au double-clic
V = Target.Value 'définit la valeur V
D = CLng(DateSerial(Year(V), Month(V), Day(V))) 'définit la date D (en entier long)
NO1 = "Édition de planing " & Replace(V, "/", "_") 'définit le nom NO1 de l'onglet
NO2 = "Planing " & Replace(V, "/", "_") 'définit le nom NO2 de l'onglet
For Each O In Sheets 'boucle sur tous les onglets du classeur
    On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligne suivante)
    Set OP1 = Worksheets(NO1) 'définit l'onglet OP (génère une erreur si cet onglet n'existe pas)
    If Err <> 0 Then 'condition : si une erreur a été générée
        Err.Clear 'supprime l'erreur
        Worksheets("Edition de plannings").Copy after:=Sheets(Sheets.Count) 'copie l'onglet "Plannings"  en dernière position
        Set OP1 = ActiveSheet 'définit l'onglet OP1
        OP1.Name = NO1 'renomme l'onglet OP1
        OP1.Range("C2").Value = D 'renvoie la date en C2 de l'onglet OP1
        Worksheets("Plannings").Copy after:=Sheets(Sheets.Count) 'copie l'onglet "Plannings"  en dernière position
        Set OP2 = ActiveSheet 'définit l'onglet OP2
        OP2.Name = NO2 'renomme l'onglet OP2
        OP2.Range("C2").Value = D 'renvoie la date en C2 de l'onglet OP2
    End If 'fin de la condition
    OP1.Select 'sélectionne l'onglet OP
    OP1.Range("A4").Select 'sélectionne la cellule A4 de l'onglet OP
    Exit For 'sort de la boucle
Next O 'prochaine onglet de la boucle
End Sub

re!

Merci.

Enfaite la ça génère un nouvel onglet planning xx/xx/xx mais qui est généré à partir de l'onglet initial plannings. Ce que j'aimerai c'est que le planning xx/xx/xx soit comme dans le classeur actuel, c'est à dire qu'il n'est qu'un renvoi avec un "=" de certaines cellules de l'onglet généré "Edition de plannings xx/xx/xx"

Tu penses que c'est faisable?

Merci encore pour ton aide!

Audrey.

Bonjour Audrey, bonjour le forum,

Une nouveau code qui devrait convenir :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'au double-clic dans l'onglet
Dim V As String 'déclare la variable V (Valeur)
Dim D As Long 'déclare la variable D (Date)
Dim NO1 As String 'déclare la variable NO (Nom de l'Onget)
Dim NO2 As String 'déclare la variable NO (Nom de l'Onget)
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim OP1 As Worksheet 'déclare la variable OP1 (Onglet Planning 1)
Dim OP2 As Worksheet 'déclare la variable OP2 (Onglet Planning 2)

If Application.Intersect(Range("B4:M8"), Target) Is Nothing Then Exit Sub 'si le double-clic a lieu ailleurs que dans la plage B4:M8, sort de la procédure
Cancel = True 'évite le mode [Édition] lié au double-clic
V = Target.Value 'définit la valeur V
D = CLng(DateSerial(Year(V), Month(V), Day(V))) 'définit la date D (en entier long)
NO1 = "Edition de planings " & Replace(V, "/", "_") 'définit le nom NO1 de l'onglet
NO2 = "Planing " & Replace(V, "/", "_") 'définit le nom NO2 de l'onglet
For Each O In Sheets 'boucle sur tous les onglets du classeur
On Error Resume Next 'gestion des erreurs (en cas d'erreur passe à la ligne suivante)
Set OP1 = Worksheets(NO1) 'définit l'onglet OP (génère une erreur si cet onglet n'existe pas)
If Err <> 0 Then 'condition : si une erreur a été générée
    Err.Clear 'supprime l'erreur
    Worksheets("Edition de plannings").Copy after:=Sheets(Sheets.Count) 'copie l'onglet "Plannings"  en dernière position
    Set OP1 = ActiveSheet 'définit l'onglet OP1
    OP1.Name = NO1 'renomme l'onglet OP1
    OP1.Range("C2").Value = D 'renvoie la date en C2 de l'onglet OP1
    Worksheets("Plannings").Copy after:=Sheets(Sheets.Count) 'copie l'onglet "Plannings"  en dernière position
    Set OP2 = ActiveSheet 'définit l'onglet OP2
    OP2.Name = NO2 'renomme l'onglet OP2
    OP2.Range("C2").Value = D 'renvoie la date en C2 de l'onglet OP2
    'modifie les formules pour récupérer des données de l'onglet OP1
    OP2.UsedRange.SpecialCells(xlCellTypeFormulas).Replace What:="Edition de plannings", Replacement:=NO1, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    OP2.Range("C8").Select 'délectionne la cellule C8 de l'onglet OP2
End If 'fin de la condition
OP1.Select 'sélectionne l'onglet OP
OP1.Range("A4").Select 'sélectionne la cellule A4 de l'onglet OP
Exit For 'sort de la boucle
Next O 'prochaine onglet de la boucle
End Sub

C'est parfait! c'est exactement ce que je cherchais à faire! Véritable magicien de la VBA!!

Merci beaucoup pour ton aide! Je vais donc continuer à essayer de faire avancer tout ce petit monde pour arriver à ce que j'espère!

Du coup sais-tu si ce format excel est lisible par toutes les versions et par open office?

Re,

Aucune idée !... Mon collège pense que oui mais pas compatible à 100%. Faut tester...

Ok! Merci encore