Onibaku_Rave
05-23-2016, 11:35 AM
I’m currently using Microsoft Excel 2016. I have a userform with a start date picker and an end date picker, and I want to use it to select a data range to generate a chart. I have the chart section mostly done and the userform created, but I don't know how to write a code telling it to use the dates (from when to when) from the userform to select the data range in excel. I'm still pretty new to VBA and would love any suggestions!
Do to the sensitive nature of my work, I cannot disclose the document I’m using. However, I have created a pretty generic spreadsheet of what I’m working with. (A1:AE2) are the dates in the month, (A3:A24) are the tasks, (B3:AE24) are the hours worked and (B27:AE27) is a line on the graph that needs to be there.
I am also having trouble formatting the chart. I want the code to resize the current active graph to be more easily readable if I need to run the code again for a different range of dates. I’ve commented out the formatting part of the code for test purposes while I was writing it.
Code:
Sub testAllocation()
'
' testAllocation Macro
'
'
'Call the User Form
frmReportingPeriods.Show
'Select Range - NEEDS TO SELECT RANGE FROM USER FORM
Range("A3:AE24").Select
'Create Graph - NEEDS TO SELECT RANGE FROM USER FORM
ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
ActiveChart.SetSourceData Source:=Range("'Detailed Target'!$A$3:$AE$24")
'Format Graph Size - NEEDS FORMAT CURRENT ACTIVE CHART
'ActiveSheet.Shapes("Chart 7").ScaleWidth 3.5419923447, msoFalse, _
msoScaleFromTopLeft
'ActiveSheet.Shapes("Chart 7").ScaleHeight 3.6548392388, msoFalse, _
msoScaleFromBottomRight
'Create Threshold Series
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(23).Name = "=""Threshold"""
ActiveChart.FullSeriesCollection(23).Values = "='Detailed Target'!$B$27:$AE$27"
'Change Chart type to Combo Graph
ActiveChart.ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(1).AxisGroup = 1
ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).AxisGroup = 1
ActiveChart.FullSeriesCollection(3).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(3).AxisGroup = 1
ActiveChart.FullSeriesCollection(4).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(4).AxisGroup = 1
ActiveChart.FullSeriesCollection(5).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(5).AxisGroup = 1
ActiveChart.FullSeriesCollection(6).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(6).AxisGroup = 1
ActiveChart.FullSeriesCollection(7).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(7).AxisGroup = 1
ActiveChart.FullSeriesCollection(8).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(8).AxisGroup = 1
ActiveChart.FullSeriesCollection(9).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(9).AxisGroup = 1
ActiveChart.FullSeriesCollection(10).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(10).AxisGroup = 1
ActiveChart.FullSeriesCollection(11).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(11).AxisGroup = 1
ActiveChart.FullSeriesCollection(12).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(12).AxisGroup = 1
ActiveChart.FullSeriesCollection(13).ChartType = xlLine
ActiveChart.FullSeriesCollection(13).AxisGroup = 1
ActiveChart.FullSeriesCollection(14).ChartType = xlLine
ActiveChart.FullSeriesCollection(14).AxisGroup = 1
ActiveChart.FullSeriesCollection(15).ChartType = xlLine
ActiveChart.FullSeriesCollection(15).AxisGroup = 1
ActiveChart.FullSeriesCollection(16).ChartType = xlLine
ActiveChart.FullSeriesCollection(16).AxisGroup = 1
ActiveChart.FullSeriesCollection(17).ChartType = xlLine
ActiveChart.FullSeriesCollection(17).AxisGroup = 1
ActiveChart.FullSeriesCollection(18).ChartType = xlLine
ActiveChart.FullSeriesCollection(18).AxisGroup = 1
ActiveChart.FullSeriesCollection(19).ChartType = xlLine
ActiveChart.FullSeriesCollection(19).AxisGroup = 1
ActiveChart.FullSeriesCollection(20).ChartType = xlLine
ActiveChart.FullSeriesCollection(20).AxisGroup = 1
ActiveChart.FullSeriesCollection(21).ChartType = xlLine
ActiveChart.FullSeriesCollection(21).AxisGroup = 1
ActiveChart.FullSeriesCollection(22).ChartType = xlLine
ActiveChart.FullSeriesCollection(22).AxisGroup = 1
ActiveChart.FullSeriesCollection(23).ChartType = xlLine
ActiveChart.FullSeriesCollection(23).AxisGroup = 1
ActiveChart.FullSeriesCollection(22).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(21).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(20).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(19).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(18).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(17).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(16).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(15).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(14).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(13).ChartType = xlColumnStacked
'Format Threshold Line - NEEDS FORMAT CURRENT ACTIVE CHART
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.FullSeriesCollection(23).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineLongDashDot
End With
ActiveChart.ChartArea.Select
End Sub
Let me know if more information is needed, and thanks for the help!
Do to the sensitive nature of my work, I cannot disclose the document I’m using. However, I have created a pretty generic spreadsheet of what I’m working with. (A1:AE2) are the dates in the month, (A3:A24) are the tasks, (B3:AE24) are the hours worked and (B27:AE27) is a line on the graph that needs to be there.
I am also having trouble formatting the chart. I want the code to resize the current active graph to be more easily readable if I need to run the code again for a different range of dates. I’ve commented out the formatting part of the code for test purposes while I was writing it.
Code:
Sub testAllocation()
'
' testAllocation Macro
'
'
'Call the User Form
frmReportingPeriods.Show
'Select Range - NEEDS TO SELECT RANGE FROM USER FORM
Range("A3:AE24").Select
'Create Graph - NEEDS TO SELECT RANGE FROM USER FORM
ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
ActiveChart.SetSourceData Source:=Range("'Detailed Target'!$A$3:$AE$24")
'Format Graph Size - NEEDS FORMAT CURRENT ACTIVE CHART
'ActiveSheet.Shapes("Chart 7").ScaleWidth 3.5419923447, msoFalse, _
msoScaleFromTopLeft
'ActiveSheet.Shapes("Chart 7").ScaleHeight 3.6548392388, msoFalse, _
msoScaleFromBottomRight
'Create Threshold Series
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(23).Name = "=""Threshold"""
ActiveChart.FullSeriesCollection(23).Values = "='Detailed Target'!$B$27:$AE$27"
'Change Chart type to Combo Graph
ActiveChart.ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(1).AxisGroup = 1
ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).AxisGroup = 1
ActiveChart.FullSeriesCollection(3).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(3).AxisGroup = 1
ActiveChart.FullSeriesCollection(4).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(4).AxisGroup = 1
ActiveChart.FullSeriesCollection(5).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(5).AxisGroup = 1
ActiveChart.FullSeriesCollection(6).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(6).AxisGroup = 1
ActiveChart.FullSeriesCollection(7).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(7).AxisGroup = 1
ActiveChart.FullSeriesCollection(8).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(8).AxisGroup = 1
ActiveChart.FullSeriesCollection(9).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(9).AxisGroup = 1
ActiveChart.FullSeriesCollection(10).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(10).AxisGroup = 1
ActiveChart.FullSeriesCollection(11).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(11).AxisGroup = 1
ActiveChart.FullSeriesCollection(12).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(12).AxisGroup = 1
ActiveChart.FullSeriesCollection(13).ChartType = xlLine
ActiveChart.FullSeriesCollection(13).AxisGroup = 1
ActiveChart.FullSeriesCollection(14).ChartType = xlLine
ActiveChart.FullSeriesCollection(14).AxisGroup = 1
ActiveChart.FullSeriesCollection(15).ChartType = xlLine
ActiveChart.FullSeriesCollection(15).AxisGroup = 1
ActiveChart.FullSeriesCollection(16).ChartType = xlLine
ActiveChart.FullSeriesCollection(16).AxisGroup = 1
ActiveChart.FullSeriesCollection(17).ChartType = xlLine
ActiveChart.FullSeriesCollection(17).AxisGroup = 1
ActiveChart.FullSeriesCollection(18).ChartType = xlLine
ActiveChart.FullSeriesCollection(18).AxisGroup = 1
ActiveChart.FullSeriesCollection(19).ChartType = xlLine
ActiveChart.FullSeriesCollection(19).AxisGroup = 1
ActiveChart.FullSeriesCollection(20).ChartType = xlLine
ActiveChart.FullSeriesCollection(20).AxisGroup = 1
ActiveChart.FullSeriesCollection(21).ChartType = xlLine
ActiveChart.FullSeriesCollection(21).AxisGroup = 1
ActiveChart.FullSeriesCollection(22).ChartType = xlLine
ActiveChart.FullSeriesCollection(22).AxisGroup = 1
ActiveChart.FullSeriesCollection(23).ChartType = xlLine
ActiveChart.FullSeriesCollection(23).AxisGroup = 1
ActiveChart.FullSeriesCollection(22).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(21).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(20).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(19).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(18).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(17).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(16).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(15).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(14).ChartType = xlColumnStacked
ActiveChart.FullSeriesCollection(13).ChartType = xlColumnStacked
'Format Threshold Line - NEEDS FORMAT CURRENT ACTIVE CHART
ActiveSheet.ChartObjects("Chart 7").Activate
ActiveChart.FullSeriesCollection(23).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
End With
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineLongDashDot
End With
ActiveChart.ChartArea.Select
End Sub
Let me know if more information is needed, and thanks for the help!