Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Define a chart using VBA
Hi folks, I'm baffled- I'm writing a macro to pull a data table into an excel sheet & create a bargraph of those results. The number of records in the data table are variable (say between 2 and 50). I'm trying to define a dynamic bar graph using VBA. The X values are in col C and the text Y vlaues are in col A. (both start in row 3) The values in column B are necessary, but have nothing to do with the graph. I'm having trouble using variables to define the source data as two, non adjacent ranges. I've had problems just including the 50 cells in the data table as it includes blank spaces in the chart. The code below builds the graph based on the X values starting in cell C3. It just numbers them on the Y axis, not by the corresponding text values.... Any ideas? Thanks pim Dim sheetName as String 'name of the sheet where the data table Dim NoRec As Double 'Number of records returned in query Dim Graph As ChartObject 'Bar graph NoRec = Application.WorksheetFunction.CountA(Columns("A:A" )) Set Graph = ActiveSheet.ChartObjects.Add _ (Left:=285, Width:=548, Top:=40, Height:=825) Graph.Chart.SetSourceData Source:=Sheets(sheetName).Range(Cells(3, 3), Cells(NoRec, 3)) Graph.Chart.ChartType = xlBarClustered ActiveSheet.ChartObjects(1).Activate ActiveChart.HasLegend = False ActiveChart.HasTitle = False With ActiveChart.ChartGroups(1) .Overlap = 0 .GapWidth = 140 .HasSeriesLines = False End With With ActiveChart.ChartGroups(1) .Overlap = 0 .GapWidth = 140 .HasSeriesLines = False End With 'Y axis- text names format With ActiveChart.Axes(xlCategory) .CrossesAt = 1 .TickLabelSpacing = 1 .TickMarkSpacing = 1 .AxisBetweenCategories = True .ReversePlotOrder = True .MajorTickMark = xlOutside .MinorTickMark = xlNone .TickLabelPosition = xlLow End With 'X axis-PI values Format With ActiveChart.Axes(xlValue) .TickLabelPosition = xlHigh End With ActiveChart.PlotArea.Select With Selection.Border .ColorIndex = 16 .Weight = xlThin .LineStyle = xlContinuous End With -- Pim ------------------------------------------------------------------------ Pim's Profile: http://www.excelforum.com/member.php...o&userid=27565 View this thread: http://www.excelforum.com/showthread...hreadid=470956 |
#2
|
|||
|
|||
i got it- thanks guys! -- Pim ------------------------------------------------------------------------ Pim's Profile: http://www.excelforum.com/member.php...o&userid=27565 View this thread: http://www.excelforum.com/showthread...hreadid=470956 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activating a Chart object | Charts and Charting in Excel | |||
Pie chart on a pie chart (exploded)? | Excel Discussion (Misc queries) | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
Urgent Chart Questions | Excel Discussion (Misc queries) | |||
Urgent Chart Assistance Requested | Excel Discussion (Misc queries) |