hackman
Junior Member | Редактировать | Профиль | Сообщение | ICQ | Цитировать | Сообщить модератору
подскажите как сделать, чтоб в легенде не появлялся ряд4, і как упростить такой макрос.
Код:
Sub build_ser_rik()
Application.ScreenUpdating = False
t = UserForm1.ComboBox1.Value
name = UserForm1.ComboBox1.Value
t = CStr(t)
If t = "РАЗОМ" Then colz = 2: scalval = 230
If t = "ВХ" Then colz = 4: scalval = 110
If t = "ЛХЗ" Then colz = 5: scalval = 44
If t = "Заморозка" Then colz = 6: scalval = 0.3
If t = "ГХ" Then colz = 7: scalval = 17
If t = "БХ" Then colz = 8: scalval = 10
If t = "МП" Then colz = 9: scalval = 2
If t = "ЧГ" Then colz = 10: scalval = 6
If t = "КХ" Then colz = 11: scalval = 3
If t = "СХ" Then colz = 12: scalval = 6
If t = "РХ" Then colz = 13: scalval = 10
If t = "ЖХ" Then colz = 14: scalval = 0
If t = "КМХ" Then colz = 15: scalval = 1
If t = "ШП" Then colz = 16: scalval = 5
If t = "КЗХ" Then colz = 17: scalval = 6
xvls = Range(Cells(43, 1), Cells(54, 1)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
fakt = Range(Cells(43, colz), Cells(52, colz)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
plan = Range(Cells(58, colz), Cells(69, colz)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
fakt6 = Range(Cells(73, colz), Cells(84, colz)).Address(RowAbsolute:=False, ColumnAbsolute:=False)
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
'дані для графіка планової реалізації
ActiveChart.SeriesCollection(1).XValues = Worksheets("Динаміка").Range("" & (xvls) & "")
ActiveChart.SeriesCollection(1).Values = Worksheets("Динаміка").Range("" & (plan) & "")
ActiveChart.SeriesCollection(1).name = "=""Планова реалізація"""
'дані для графіка фактичної реалізації 2007
ActiveChart.SeriesCollection(2).XValues = Worksheets("Динаміка").Range("" & (xvls) & "")
ActiveChart.SeriesCollection(2).Values = Worksheets("Динаміка").Range("" & (fakt) & "")
ActiveChart.SeriesCollection(2).name = "=""Факт. реалізація 2007"""
'дані для графіка фактичної реалізації 2007
ActiveChart.SeriesCollection(3).XValues = Worksheets("Динаміка").Range("" & (xvls) & "")
ActiveChart.SeriesCollection(3).Values = Worksheets("Динаміка").Range("" & (fakt6) & "")
ActiveChart.SeriesCollection(3).name = "=""Факт. реалізація 2006"""
' ActiveChart.Location Where:=xlLocationAsNewSheet, name:= _
"Середньоденні_план_факт_" & (name) & ""
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = _
"Середньоденна реалізація в тоннах " & (name) & " 2007 р."
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "тонн"
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlRight
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.FontStyle = "обычный"
.Size = 8
End With
' If ActiveChart.Legend.LegendEntries(4) = True Then
' ActiveChart.Legend.LegendEntries(4).Select
' Selection.Delete
' End If
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.FontStyle = "обычный"
Size = 8
End With
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlDownward
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 11
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 11
.MarkerForegroundColorIndex = 11
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 27
.Weight = xlThick
.LineStyle = xlDash
End With
With Selection
.MarkerStyle = xlNone
.Smooth = False
.Shadow = False
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Fill.PresetGradient Style:=msoGradientHorizontal, Variant:=1, _
PresetGradientType:=msoGradientDaybreak
Selection.Fill.Visible = True
'підписи факт
'
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels AutoText:=True
ActiveChart.SeriesCollection(2).DataLabels.Select
Selection.AutoScaleFont = True
With Selection.Font
.Size = 8
End With
ActiveChart.ChartArea.Select
'3 графік обробка
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 26
.Weight = xlThick
'.LineStyle = xlGray50
End With
With Selection
.MarkerBackgroundColorIndex = 26
.MarkerForegroundColorIndex = 26
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 7
.Shadow = True
End With
'поправити 2 графік факт 2007
ActiveChart.SeriesCollection(2).Select
' ActiveChart.SeriesCollection(2).Points(5).Select
With Selection.Border
.ColorIndex = 11
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 49
.MarkerForegroundColorIndex = 49
.MarkerStyle = xlSquare
.MarkerSize = 7
.Shadow = False
End With
ActiveChart.SeriesCollection(3).ApplyDataLabels AutoText:=True
ActiveChart.SeriesCollection(3).DataLabels.Select
Selection.AutoScaleFont = True
With Selection.Font
.Size = 8
End With
'легенда
ActiveChart.Legend.Select
Selection.Left = 568
Selection.Width = 149
With Selection.Font
.Size = 8
End With
ActiveChart.PlotArea.Select
' ActiveChart.SeriesCollection(4).Delete
'шкала
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = scalval
End With
'лінія тренду
ActiveChart.SeriesCollection(2).Trendlines.Add Type:=xlLinear, name:="Лінія тренду"
'ActiveChart.Legend.Select
' ActiveChart.Legend.LegendEntries(4).Select
' Selection.Delete
ActiveSheet.Select
ActiveSheet.name = "Cередньоденна_" & (name) & ""
ActiveSheet.Move
End Sub
|
|
Всего записей: 185 | Зарегистр. 22-09-2003 | Отправлено: 12:25 08-11-2007 | Исправлено: hackman, 12:26 08-11-2007 |
|
