VBA to create a chart from selected data

[jeffwest1]

I have a series of scorecards type sheets that I want to chart the data from.

While I could do each one in turn, I can have up to 20 seperate sheets, with 20 measures on each sheet.

I have half found an answer to this but wondered if someone can help with the rest

I want to select a row using a tick box on the left hand side of the screen, then from that row only select specific data (i.e. A3, D3, F3 etc) then create on the fly a bar chart open this up using the data that i have chosen.

Either that, or have a dialog box with a drop down menu asking what measure uyou want to chart, then select the data that way.

I know how to create the chart bit (i think) it's getting the data selected that is the problem.

Any help would be appreciated.

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[electricpete]

Here's what I would do.

1 - Use Data/Validation to give a pulldown menu in a cell for choice of which data to plot.
2 - Create a user-defined function sw (like switch) that can be called from excel (see code below)
3 - Create a named formula myyformula:
=sw(choice1, range1,choice2,range2,choice3,range3...)
4 - Create a chart using your x data and one of the y data's.
5 - Edit the y series on the chart to be =myyformula

(this assumed you use the same x data throughout)

Now you have one chart and when you change the value in your pulldown cell, you get the data plotted that you are interested in.

Function sw(ParamArray invar())
    ' implement logic similar to switch as excel udf
    ' example call
    '  =switch(boolean1, value1, boolean2, value2, boolean3, value3....)
    '    returns the value corresponding to the first true boolean

    ' at least one of the boolean expressions must be true
    ' requires an even number of arguments
    ' the syntax is pretty much identical to vba switch, except that there is no explicit allowance for else value
    ' if you want an else clause, enter true for the next to last argument, followed by the associated value

    ' Note that indexing of invar starts at 0, regardless of Option Base statement

    ' Check to confirm even number of arguments (as required)
    If UBound(invar) Mod 2 <> 1 Then MsgBox "Error: Need even number of arguments for sw"
    Dim ctr As Long        ' loop counter
    Dim tempswitch As Variant        ' variable which will hold the output value

    ctr = 0        ' initialize counter
    Do While True        ' loop until broken by exit command
        ' Check for boolean input
        If VarType(invar(ctr)) <> vbBoolean Then MsgBox "Error 1st 3rd 5th etc arguments of sw must be boolean"
        If invar(ctr) Then        ' in this case have found a true value, assign function and exit
            tempswitch = invar(ctr + 1)
            sw = tempswitch
            Exit Do
        Else        ' Else have not found true yet, update counter and continue loop
            ctr = ctr + 2
        End If

        ' Check for reaching end of invar without having found true
        If ctr + 1 > UBound(invar) Then MsgBox "Error: sw needs at least one true boolean argument"

    Loop

End Function

 

[SkipVought]

Hi,

You have a workbook structure that is less than desirable. It is typical of novice spreadsheet users.

I would combine ALL the data into ONE SHEET into ONE TABLE, adding a column that defines each data group. Chart ALL the data. Then simply ues the AutoFilter to select the data for the requested chart.

This is how Excel is designed to most adequately analyze and report data: from ONE table.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jeffwest1]

SkipVought I am no novice at excel thank you, unfortunatly the make up of the sheets has been dictated by the needs and requirements of the business and what the scorecards have been designed to do, which is to allow for a scorercard/dashboard system of data, allowing users to only access their own data, which is managed via their network login (pulled through).
Yes it is not ideal, however I have managed to pull off many things with this 'novice' structure.

If i wanted to chart all the data then i can do that easily, but it isn't what i want to do, i want to chart only the required data, something that Excel is very good at doing no matter the structure of the data held with in it.

Thanks electricpete for the idea, i will add it to what i have already done and give it a go.

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

With those restrictions, I would design a dynamic query returning results to the Chart Data sheet. Pretty simple.

sSQL = "Select * From ['" & SelectedSheet & "'$]

Nothing in the chart needs to change.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jeffwest1]

Thanks for all the help I have now sorted this issue.

I created a template chart and have added a series of buttons on each page withthe following code behind the button.

Not pretty but it works.

Sub CreateNEETChart()
Sheets("Neet 16 - 18").Visible = True
Dim SelectChart As Chart

Set SelectChart = ThisWorkbook.Charts("Neet 16 - 18")

SelectChart.ChartWizard Gallery:=xlColumn, _
Format:=2, Categorylabels:=True, _
HasLegend:=False, Title:="NEET 16 - 18 " & ActiveSheet.Range("C4"), PlotBy:=xlColumns
'SelectChart.HasDataTable = True

SelectChart.SetSourceData Source:=ActiveSheet.Range("E13,I13,M13,Q13,U13,Y13,AC13,AG13,AK13,AO13,AS13,AW13,BA13")
   Sheets("Neet 16 - 18").Select
   With ActiveChart
   .HasAxis(xlCategory, xlPrimary) = True
   
   .Axes(xlCategory, xlPrimary).HasTitle = True
   .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ("Months")
  End With
  
  With ActiveChart
    With .SeriesCollection(1).Points(2)
        .HasDataLabel = True
        .DataLabel.Text = "April"
    End With
        With .SeriesCollection(1).Points(3)
        .HasDataLabel = True
        .DataLabel.Text = "May"
    End With
        With .SeriesCollection(1).Points(4)
        .HasDataLabel = True
        .DataLabel.Text = "June"
    End With
        With .SeriesCollection(1).Points(5)
        .HasDataLabel = True
        .DataLabel.Text = "Jul"
    End With
        With .SeriesCollection(1).Points(6)
        .HasDataLabel = True
        .DataLabel.Text = "Aug"
    End With
        With .SeriesCollection(1).Points(7)
        .HasDataLabel = True
        .DataLabel.Text = "Sept"
    End With
        With .SeriesCollection(1).Points(8)
        .HasDataLabel = True
        .DataLabel.Text = "Oct"
    End With
        With .SeriesCollection(1).Points(9)
        .HasDataLabel = True
        .DataLabel.Text = "Nov"
    End With
        With .SeriesCollection(1).Points(10)
        .HasDataLabel = True
        .DataLabel.Text = "Dec"
    End With
        With .SeriesCollection(1).Points(11)
        .HasDataLabel = True
        .DataLabel.Text = "Jan"
    End With
        With .SeriesCollection(1).Points(12)
        .HasDataLabel = True
        .DataLabel.Text = "Feb"
    End With
        With .SeriesCollection(1).Points(13)
        .HasDataLabel = True
        .DataLabel.Text = "Mar"
    End With
End With

Sheets("Neet 16 - 18").Select

End Sub

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.