Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Applications Microsoft Office Excel Ajuster PlotArea d'un graphique

Ajuster PlotArea d'un graphique

1 réponse
Avatar
ionel.sbiera
Bonjour le forum,
j'ai ecrit un code VBA qui fait un graphique (XYscater)et ensuite
ajoute un control textbox sur le graphique. J'utilise la textbox pour
aficher certaines informations lorsque l'utilisateur se balade dans le
tableau source de graphique.
Le probleme:
La textBox est suprapos=E9e sur un partie du graphique, plus precisement
sur l'objet PlotArea du graphic. J'ai parametr=E9 la longeur et la
hauteur du text box de tel facon que la superficie du textbox soit 2/3
de la superficie de PlotArea.Pour le moment tu va bien, mais lorsque
j'essaie d'ajuster la plot area avec le code suivant:

ActiveSheet.ChartObjects(1).Activate
ActiveSheet.ChartObjects(1).Chart.PlotArea.Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.PlotArea.Select
Selection.Width =3D 554
Selection.Left =3D 216
de facon a avoir la text box dans la partie gauche du graphique et le
"graphique" (PlotArea) dans la partie droite, l'ajustement n'est pas
fait.
Est-ce que quelq'un peut m'expliquer pourquoi ce souci et me conseiller
pour pouvoir ajuster cette maudite PlotArea?
Merci d'avance!!

Mon code entier est ci joint:

Sub DrawFrontier()
Dim GraphFrontier As Chart

Windows(ActiveWorkbook.Name).Activate
Charts.Add

Set GraphFrontier =3D ActiveChart
With GraphFrontier
.ChartType =3D xlXYScatterSmoothNoMarkers
.SetSourceData Source:=3DSheets("Sheet1").Range("A1")
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues =3D "=3DPortfolio!R27C3:R27C13"
.SeriesCollection(1).Values =3D "=3DPortfolio!R26C3:R26C13"
.SeriesCollection(1).Name =3D "=3D""Serie1"""
.SeriesCollection(2).XValues =3D "=3DPortfolio!R27C3"
.SeriesCollection(2).Values =3D "=3DPortfolio!R26C3"
.SeriesCollection(2).Name =3D "=3D""Serie2"""
.SeriesCollection(3).XValues =3D "=3DPortfolio!R27C2"
.SeriesCollection(3).Values =3D "=3DPortfolio!R26C2"
.SeriesCollection(3).Name =3D "=3D""Serie3"""
.Location Where:=3DxlLocationAsObject, Name:=3D"Sheet1"
End With
With ActiveChart
.HasTitle =3D True
.ChartTitle.Characters.Text =3D "My graph"
.Axes(xlCategory, xlPrimary).HasTitle =3D True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =3D "x"
.Axes(xlValue, xlPrimary).HasTitle =3D True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =3D "y"
End With
ActiveChart.HasLegend =3D True
ActiveChart.Legend.Select
Selection.Position =3D xlBottom

ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont =3D True
With Selection.TickLabels.Font
.Name =3D "Arial"
.FontStyle =3D "Normal"
.Size =3D 8
.Strikethrough =3D False
.Superscript =3D False
.Subscript =3D False
.OutlineFont =3D False
.Shadow =3D False
.Underline =3D xlUnderlineStyleNone
.ColorIndex =3D xlAutomatic
.Background =3D xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont =3D True
With Selection.TickLabels.Font
.Name =3D "Arial"
.FontStyle =3D "Normal"
.Size =3D 8
.Strikethrough =3D False
.Superscript =3D False
.Subscript =3D False
.OutlineFont =3D False
.Shadow =3D False
.Underline =3D xlUnderlineStyleNone
.ColorIndex =3D xlAutomatic
.Background =3D xlAutomatic
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont =3D True
With Selection.Font
.Name =3D "Arial"
.Size =3D 10
.Strikethrough =3D False
.Superscript =3D False
.Subscript =3D False
.OutlineFont =3D False
.Shadow =3D False
.Underline =3D xlUnderlineStyleNone
.ColorIndex =3D xlAutomatic
.Background =3D xlAutomatic
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont =3D True
With Selection.Font
.Name =3D "Arial"
.Size =3D 10
.Strikethrough =3D False
.Superscript =3D False
.Subscript =3D False
.OutlineFont =3D False
.Shadow =3D False
.Underline =3D xlUnderlineStyleNone
.ColorIndex =3D xlAutomatic
.Background =3D xlAutomatic
End With
Selection.Font.Bold =3D False
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Font.Bold =3D False
ActiveChart.ChartArea.Select
With Selection.Border
.Weight =3D 2
.LineStyle =3D -1
End With
With Selection.Interior
.ColorIndex =3D 0
.PatternColorIndex =3D 1
.Pattern =3D 1
End With
Sheets("Sheet1").DrawingObjects(1).RoundedCorners =3D True
Sheets("Sheet1").DrawingObjects(1).Shadow =3D True
With Sheets("Portfolio").DrawingObjects(1)
.Placement =3D xlFreeFloating
.PrintObject =3D True
End With
Sheets("Sheet1").DrawingObjects(1).Locked =3D True
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex =3D 16
.Weight =3D xlThin
.LineStyle =3D xlContinuous
End With
With Selection.Interior
.ColorIndex =3D 19
.PatternColorIndex =3D 1
.Pattern =3D xlSolid
End With
With ActiveSheet.ChartObjects
.Width =3D 780
.Height =3D 300
.Top =3D 1
.Left =3D 1
End With

ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex =3D 3
.Weight =3D xlMedium
.LineStyle =3D xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex =3D 3
.MarkerForegroundColorIndex =3D 3
.MarkerStyle =3D xlSquare
.Smooth =3D True
.MarkerSize =3D 3
.Shadow =3D False
End With

ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight =3D xlThin
.LineStyle =3D xlNone
End With
With Selection
.MarkerBackgroundColorIndex =3D 1
.MarkerForegroundColorIndex =3D 1
.MarkerStyle =3D xlSquare
.Smooth =3D True
.MarkerSize =3D 3
.Shadow =3D False
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight =3D xlThin
.LineStyle =3D xlNone
End With
With Selection
.MarkerBackgroundColorIndex =3D 5
.MarkerForegroundColorIndex =3D 5
.MarkerStyle =3D xlSquare
.Smooth =3D True
.MarkerSize =3D 3
.Shadow =3D False
End With
'--add textbox
ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 15, 45,
90, 105).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor =3D 65
Selection.Characters.Text =3D ""
Selection.AutoScaleFont =3D False
With Selection.Font
.Name =3D "Arial"
.FontStyle =3D "Bold"
.Size =3D 10
.Strikethrough =3D False
.Superscript =3D False
.Subscript =3D False
.OutlineFont =3D False
.Shadow =3D False
.Underline =3D xlUnderlineStyleNone
.ColorIndex =3D 5
End With

Selection.ShapeRange.Fill.Visible =3D msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor =3D 22
Selection.ShapeRange.Fill.Transparency =3D 0#
Selection.ShapeRange.Line.Weight =3D 0.75
Selection.ShapeRange.Line.DashStyle =3D msoLineSolid
Selection.ShapeRange.Line.Style =3D msoLineSingle
Selection.ShapeRange.Line.Transparency =3D 0#
Selection.ShapeRange.Line.Visible =3D msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor =3D 64
Selection.ShapeRange.Line.BackColor.RGB =3D RGB(255, 255, 255)

ActiveSheet.ChartObjects(1).Activate
ActiveSheet.ChartObjects(1).Chart.PlotArea.Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.PlotArea.Select
Selection.Width =3D 554
Selection.Left =3D 216
Windows(ActiveWorkbook.Name).ActiveSheet.Range("A1").Select

End Sub
Signaler un problème avec ce contenu

1 réponse

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
bourby
bonjour

ton code est confus, il est recommandé de simplifier ce qu'indique
l'enregistreur de macros.
Remplace les 6 lignes en bas (avant windows(Activeworkbookname........))
par:

With ActiveSheet.ChartObjects(1).Chart.PlotArea
.Width = 554
.Left = 216
End With

Cordialement

Bourby







wrote:
Bonjour le forum,
j'ai ecrit un code VBA qui fait un graphique (XYscater)et ensuite
ajoute un control textbox sur le graphique. J'utilise la textbox pour
aficher certaines informations lorsque l'utilisateur se balade dans le
tableau source de graphique.
Le probleme:
La textBox est supraposée sur un partie du graphique, plus precisement
sur l'objet PlotArea du graphic. J'ai parametré la longeur et la
hauteur du text box de tel facon que la superficie du textbox soit 2/3
de la superficie de PlotArea.Pour le moment tu va bien, mais lorsque
j'essaie d'ajuster la plot area avec le code suivant:

ActiveSheet.ChartObjects(1).Activate
ActiveSheet.ChartObjects(1).Chart.PlotArea.Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.PlotArea.Select
Selection.Width = 554
Selection.Left = 216
de facon a avoir la text box dans la partie gauche du graphique et le
"graphique" (PlotArea) dans la partie droite, l'ajustement n'est pas
fait.
Est-ce que quelq'un peut m'expliquer pourquoi ce souci et me conseiller
pour pouvoir ajuster cette maudite PlotArea?
Merci d'avance!!

Mon code entier est ci joint:

Sub DrawFrontier()
Dim GraphFrontier As Chart

Windows(ActiveWorkbook.Name).Activate
Charts.Add

Set GraphFrontier = ActiveChart
With GraphFrontier
.ChartType = xlXYScatterSmoothNoMarkers
.SetSourceData Source:=Sheets("Sheet1").Range("A1")
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = "=Portfolio!R27C3:R27C13"
.SeriesCollection(1).Values = "=Portfolio!R26C3:R26C13"
.SeriesCollection(1).Name = "=""Serie1"""
.SeriesCollection(2).XValues = "=Portfolio!R27C3"
.SeriesCollection(2).Values = "=Portfolio!R26C3"
.SeriesCollection(2).Name = "=""Serie2"""
.SeriesCollection(3).XValues = "=Portfolio!R27C2"
.SeriesCollection(3).Values = "=Portfolio!R26C2"
.SeriesCollection(3).Name = "=""Serie3"""
.Location Where:=xlLocationAsObject, Name:="Sheet1"
End With
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "My graph"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "x"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "y"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom

ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Font.Bold = False
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Font.Bold = False
ActiveChart.ChartArea.Select
With Selection.Border
.Weight = 2
.LineStyle = -1
End With
With Selection.Interior
.ColorIndex = 0
.PatternColorIndex = 1
.Pattern = 1
End With
Sheets("Sheet1").DrawingObjects(1).RoundedCorners = True
Sheets("Sheet1").DrawingObjects(1).Shadow = True
With Sheets("Portfolio").DrawingObjects(1)
.Placement = xlFreeFloating
.PrintObject = True
End With
Sheets("Sheet1").DrawingObjects(1).Locked = True
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 19
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With ActiveSheet.ChartObjects
.Width = 780
.Height = 300
.Top = 1
.Left = 1
End With

ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 3
.MarkerStyle = xlSquare
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With

ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlSquare
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = 5
.MarkerForegroundColorIndex = 5
.MarkerStyle = xlSquare
.Smooth = True
.MarkerSize = 3
.Shadow = False
End With
'--add textbox
ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 15, 45,
90, 105).Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 65
Selection.Characters.Text = ""
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With

Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 22
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

ActiveSheet.ChartObjects(1).Activate
ActiveSheet.ChartObjects(1).Chart.PlotArea.Select
ActiveSheet.ChartObjects(1).Activate
ActiveChart.PlotArea.Select
Selection.Width = 554
Selection.Left = 216
Windows(ActiveWorkbook.Name).ActiveSheet.Range("A1").Select

End Sub