PDA

View Full Version : [SOLVED] Using Userform with Date Picker to Select Data Range in Excel



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!

p45cal
05-23-2016, 12:22 PM
It would be an idea to include some typical data, a chart as you want it, a userform and some code!
In the meantime, in the attached, one of many ways to translate DTPicker values to ranges.

Onibaku_Rave
05-23-2016, 12:31 PM
Thanks! Let me take a look and I'll update the post with additional info if need.

Onibaku_Rave
05-23-2016, 12:51 PM
It would be an idea to include some typical data, a chart as you want it, a userform and some code!
In the meantime, in the attached, one of many ways to translate DTPicker values to ranges.

Is it alright if I PM you some additional information?

p45cal
05-23-2016, 12:57 PM
Sure

Onibaku_Rave
05-23-2016, 01:28 PM
Sure

PM sent!

Onibaku_Rave
05-23-2016, 03:32 PM
Post updated with new sheet.

Onibaku_Rave
05-23-2016, 08:44 PM
Post updated with new sheet.

Please disregard the previous sample sheets above, this attachment has the correct code and form

p45cal
05-24-2016, 02:09 AM
see attached

Aussiebear
05-24-2016, 03:06 AM
PM'ing a responder doesn't really assist the other members of the community. After all we are all here to learn. If you start a thread and then fail to continue to publicily promote the thread to a conclusion..... Well I for one am dissapointed in you

Onibaku_Rave
05-24-2016, 05:27 AM
PM'ing a responder doesn't really assist the other members of the community. After all we are all here to learn. If you start a thread and then fail to continue to publicily promote the thread to a conclusion..... Well I for one am dissapointed in you

Please don't misunderstand, the intention was and still is to share the information for all to see. Because of the sensitivity of my work, I had to be sure it was scrubbed before publicly posting a sample. The first sample had no data, so I created a new sheet with all the code, form and data as shared in my most recent post. The PM was just making sure I don't accidentally reveal something I shouldn't have, and once it was cleared, I updated the post. No solution was shared between us in our PMs, but rather it was only posted right here so everything is available publicly. Forgive me if I've offended anyone.

Thanks once again,p45cal, for looking at my problem. I'll give it a look through and come back with questions if I have any or update the thread accordingly.