current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -3 down vote favorite

I'm looking for a code to create a button which upon clicking will change the pivot table chart from a bar chart to a line graph. I want the button to be at the bottom of the graph so the user can choose how they can see the data at the click of a button.

Does anyone know the code for this? Ideally a plug and play solution as I'm a beginner to VBA

Thanks

share|improve this question
    
Try recording a macro then adding the resulting code to a button. –  Skip Intro May 15 '13 at 9:58
    
Private Sub CommandButton20_Click() Sub Macro1() ' ' Macro1 Macro ' Line Graph ' ' ActiveSheet.ChartObjects("Chart 60").Activate ActiveChart.ChartType = xlLine End Sub That's my code but I'm getting a Compile Error 'Expected End Sub' how do I fix it? Does the code look right to you? I recorded the Macro, changed the chart type and then stopped recording... –  Charlie May 15 '13 at 11:01
    
I have tidied up the code below. –  Skip Intro May 15 '13 at 13:43

1 Answer 1

up vote 1 down vote accepted

You had two sub routines, but only one end:

Private Sub CommandButton20_Click()

  ActiveSheet.ChartObjects("Chart 60").Activate
  ActiveChart.ChartType = xlLine

End Sub

Works for me.

You can also assign a macro to a chart so something like:

Sub ChangeMe()

ActiveSheet.ChartObjects("Chart 60").Activate

    If ActiveChart.ChartType = xlLine Then
            ActiveChart.ChartType = xlBarClustered
                Else
                ActiveChart.ChartType = xlLine
    End If

End Sub

might be worth investigating.

share|improve this answer
    
That's great! Thank you it's working well. What code do I need if I want to change the X axis of my pivot chart from App Name to Date at the click of a button? Thanks –  Charlie May 16 '13 at 9:51
    
Charts("Chart1").SeriesCollection(1).XValues = Worksheets("Sheet1").Range("B1:B5") will take the values from Range B1:B5 on Sheet1 and use that as the x axis values of Chart1. –  Skip Intro May 16 '13 at 10:41
    
Also if the answer is working, could you tick it as accepted in case anyone else needs a similar solution? Thanks. –  Skip Intro May 16 '13 at 10:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.