Sujet Précédent Sujet Suivant

Probleme dans graphique dynamique et mise en forme

Fermé
vanhoa - 23 janv. 2014 à 03:02
 vanhoa - 23 janv. 2014 à 05:47
Bonjour,

J'ai cree un graphique dynamique/interactif. Ce graphique affiche des courbes en fonction des regions choisies. Par exemple, si je mets ma souris sur la ville A, le graphique affiche les donnees de la ville A, sur la ville B, celles de la ville B etc...
J'ai voulu rendre le graphique plus joli en codant la mise en forme egalement.
Mon probleme est que dans ma mise en forme, j'insere un remplissage cellule et des bordures qui entourent la cellule ou il y a le nom de la ville.
Quand je pointe ma souris sur une autre ville, c'est cense supprimer la precedente mise en forme pour l'appliquer a la nouvelle plage de donnee.
En ce qui concerne le remplissage couleur, ca marche, mais pour les bordures, elles restent toujours malgre le fait de bien preciser de supprimer les bordures.

Note: ma cellule ou il y a le nom de la ville A/B/C/D, la cellule juste en dessous contient une formule personnalisee. C'est en passant la souris sur cette cellule (celle qui contient la formule) que mon graphique reagit en changeant les donnees.
La formule est:

=IFERROR(HYPERLINK(CCCchart(R19)),"click")
"CCCchart() est la fonction personnalisee ci-dessous et R19 ma cellule ou il y a le nom de la ville A par exemple. Ainsi, j'ai cette formule dans 3 autres cellules (pour la ville B, C et D)

Voici mon code: 

Public Function CCCchart(nllplage As Range)

'cette partie permet de rendre ma plage de donnee dynamique, cela marche, je ne pense pas que le probleme vienne de la
    Dim xater As Integer, xbter As Integer, xcter As Integer, var2ter As String, plagefinaleCCC As Range

        xater = 0
        xbter = 1
        
        Do
            If Worksheets("Asia Pacific WC Broken Details").Cells(3, xbter) = nllplage.Value And Worksheets("Asia Pacific WC Broken Details").Cells(4, xbter) = Worksheets("Sheet3").Cells(7, 4) Then
                    xater = xbter
                Else
                    xbter = xbter + 1
            End If
        Loop While xater = 0
        
    var2ter = Worksheets("Sheet3").Range("D6")
        
        For i = 0 To 11
                    Select Case var2ter
                        Case Is = Worksheets("Sheet3").Cells(i + 1, 2).Value
                            xcter = i
                    End Select
        Next i
    
    Set plagefinaleCCC = Range(Worksheets("Asia Pacific WC Broken Details").Cells(105, xater), Worksheets("Asia Pacific WC Broken Details").Cells(105, xater + xcter))
    ThisWorkbook.Names.Add Name:="plgref3", RefersTo:=plagefinaleCCC
    

'cette partie est censee supprimer mes bordures, mais ca ne marche pas

        With Range(Cells(18, 18), Cells(20, 21))
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
        End With

'Cette partie cree la mise en forme en fonction de la ou on pointe notre souris    
    
        Select Case nllplage
            Case Is = Cells(19, 18)
                Range(Cells(18, 18), Cells(20, 18)).Interior.Color = RGB(241, 245, 249)
                Range(Cells(18, 19), Cells(20, 21)).Interior.Color = RGB(255, 255, 255)
                
                With Range(Cells(18, 18), Cells(20, 18)).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(18, 18), Cells(20, 18)).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                Selection.Borders(xlEdgeBottom).LineStyle = xlNone
                With Range(Cells(18, 18), Cells(20, 18)).Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With

                Range(Cells(18, 19), Cells(20, 21)).Borders(xlEdgeTop).LineStyle = xlNone
                
                
            Case Is = Cells(19, 19)
                Range(Cells(18, 19), Cells(20, 19)).Interior.Color = RGB(241, 245, 249)
                Range(Cells(18, 18), Cells(20, 18)).Interior.Color = RGB(255, 255, 255)
                Range(Cells(18, 20), Cells(20, 21)).Interior.Color = RGB(255, 255, 255)
                
                With Range(Cells(18, 19), Cells(20, 19)).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(18, 19), Cells(20, 19)).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                Selection.Borders(xlEdgeBottom).LineStyle = xlNone
                With Range(Cells(18, 19), Cells(20, 19)).Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                
                
            Case Is = Cells(19, 20)
                Range(Cells(18, 20), Cells(20, 20)).Interior.Color = RGB(241, 245, 249)
                Range(Cells(18, 18), Cells(20, 19)).Interior.Color = RGB(255, 255, 255)
                Range(Cells(18, 21), Cells(20, 21)).Interior.Color = RGB(255, 255, 255)
                
                With Range(Cells(18, 20), Cells(20, 20)).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(18, 20), Cells(20, 20)).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                Selection.Borders(xlEdgeBottom).LineStyle = xlNone
                With Range(Cells(18, 20), Cells(20, 20)).Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                
                
            Case Is = Cells(19, 21)
                Range(Cells(18, 21), Cells(20, 21)).Interior.Color = RGB(241, 245, 249)
                Range(Cells(18, 18), Cells(20, 20)).Interior.Color = RGB(255, 255, 255)
                
                With Range(Cells(18, 21), Cells(20, 21)).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(18, 21), Cells(20, 21)).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                Selection.Borders(xlEdgeBottom).LineStyle = xlNone
                With Range(Cells(18, 21), Cells(20, 21)).Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                
                
        End Select
    


End Function



J'ai pense pour resoudre ce probleme colorer les bordures que je souhaite voir disparaitre ou lieu de les supprimer, mais ce serait trop long, et surtout, je ne comprend pas pourquoi ca ne marche pas, car le code pour supprimer marche si je l'applique dans une procedure a part.

J'ai egalement tente de supprimer ma mise en forme et de garder seulement la suppressione des bordures dans ma fonction personnalisee, mais ca ne marche pas non plus...

Si vous voulez + d'explications, dites le moi :-)

Merci beaucoup de votre aide!

vanhoa



1 réponse

Réponse 1 / 1
vanhoa
23 janv. 2014 à 05:47
J'ai trouve une parade en colorant au lieu de supprimer et en rendant le graphique encore plus joli :-)
mais je ne comprends toujours pas pourquoi le fait de supprimer les bordures ne marche pas :-(

voila mon code: 


Public Function CCCchart(nllplage As Range)
 
'dynamisation de la plage de donnee
    Dim xater As Integer, xbter As Integer, xcter As Integer, var2ter As String, plagefinaleCCC As Range
 
        xater = 0
        xbter = 1
 
        Do
            If Worksheets("Asia Pacific WC Broken Details").Cells(3, xbter) = nllplage.Value And Worksheets("Asia Pacific WC Broken Details").Cells(4, xbter) = Worksheets("Sheet3").Cells(7, 4) Then
                    xater = xbter
                Else
                    xbter = xbter + 1
            End If
        Loop While xater = 0
 
    var2ter = Worksheets("Sheet3").Range("D6")
 
        For i = 0 To 11
                    Select Case var2ter
                        Case Is = Worksheets("Sheet3").Cells(i + 1, 2).Value
                            xcter = i
                    End Select
        Next i
 
    Set plagefinaleCCC = Range(Worksheets("Asia Pacific WC Broken Details").Cells(105, xater), Worksheets("Asia Pacific WC Broken Details").Cells(105, xater + xcter))
    ThisWorkbook.Names.Add Name:="plgref3", RefersTo:=plagefinaleCCC
 
 
'coloration au lieu de suppression
 
        With Range(Cells(18, 18), Cells(20, 21))
            With .Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .ThemeColor = 1
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ThemeColor = 1
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ThemeColor = 1
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Color = -395791
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .ThemeColor = 1
                .TintAndShade = 0
                .Weight = xlThin
            End With
        End With
 
'rajout des coloration pour les "onglets" du graph non actifs par rapport a l'ancien code    
 
        Select Case nllplage
 
            Case Is = Cells(19, 18)
                Range(Cells(18, 18), Cells(20, 18)).Interior.Color = RGB(241, 245, 249)
                Range(Cells(18, 19), Cells(18, 21)).Interior.Color = RGB(255, 255, 255)
                Range(Cells(19, 19), Cells(19, 21)).Interior.Color = RGB(242, 242, 242)
 
                With Range(Cells(18, 18), Cells(20, 18)).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(18, 18), Cells(20, 18)).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                Selection.Borders(xlEdgeBottom).LineStyle = xlNone
                With Range(Cells(18, 18), Cells(20, 18)).Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(20, 19), Cells(20, 21)).Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
 
 
                Range(Cells(18, 19), Cells(20, 21)).Borders(xlEdgeTop).LineStyle = xlNone
 
 
            Case Is = Cells(19, 19)
                Range(Cells(18, 19), Cells(20, 19)).Interior.Color = RGB(241, 245, 249)
                Range(Cells(18, 18), Cells(18, 18)).Interior.Color = RGB(255, 255, 255)
                Range(Cells(18, 20), Cells(18, 21)).Interior.Color = RGB(255, 255, 255)
                Range(Cells(19, 18), Cells(20, 18)).Interior.Color = RGB(242, 242, 242)
                Range(Cells(19, 20), Cells(20, 21)).Interior.Color = RGB(242, 242, 242)
 
                With Range(Cells(18, 19), Cells(20, 19)).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(18, 19), Cells(20, 19)).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                Selection.Borders(xlEdgeBottom).LineStyle = xlNone
                With Range(Cells(18, 19), Cells(20, 19)).Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(20, 18), Cells(20, 18)).Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(20, 20), Cells(20, 21)).Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
 
            Case Is = Cells(19, 20)
                Range(Cells(18, 20), Cells(20, 20)).Interior.Color = RGB(241, 245, 249)
                Range(Cells(18, 18), Cells(18, 19)).Interior.Color = RGB(255, 255, 255)
                Range(Cells(18, 21), Cells(18, 21)).Interior.Color = RGB(255, 255, 255)
                Range(Cells(19, 18), Cells(20, 19)).Interior.Color = RGB(242, 242, 242)
                Range(Cells(19, 21), Cells(20, 21)).Interior.Color = RGB(242, 242, 242)
 
                With Range(Cells(18, 20), Cells(20, 20)).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(18, 20), Cells(20, 20)).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                Selection.Borders(xlEdgeBottom).LineStyle = xlNone
                With Range(Cells(18, 20), Cells(20, 20)).Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(20, 18), Cells(20, 19)).Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(20, 21), Cells(20, 21)).Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
 
            Case Is = Cells(19, 21)
                Range(Cells(18, 21), Cells(20, 21)).Interior.Color = RGB(241, 245, 249)
                Range(Cells(18, 18), Cells(18, 20)).Interior.Color = RGB(255, 255, 255)
                Range(Cells(19, 18), Cells(20, 20)).Interior.Color = RGB(242, 242, 242)
 
                With Range(Cells(18, 21), Cells(20, 21)).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(18, 21), Cells(20, 21)).Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                Selection.Borders(xlEdgeBottom).LineStyle = xlNone
                With Range(Cells(18, 21), Cells(20, 21)).Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
                With Range(Cells(20, 18), Cells(20, 20)).Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ThemeColor = 5
                    .TintAndShade = -0.249946592608417
                    .Weight = xlThin
                End With
 
        End Select
 
 
 
End Function
0

Discussions similaires

Boucle sur la première diapositive d'une présentation
ND83 -
ndubau -

3 réponses
Table des matières dont les titres n'apparaissent pas..
Synua -
ggg -

10 réponses
Encadrement d'un Titre intégré au cadre
Ced -
Ced -

2 réponses
Nuages de point avec abscisses différentes
LCFloor -
LCFLoor -

6 réponses
Nom de champs du tableau croisé dynamique non valide
Kazooie123 -
SajDri -

6 réponses