• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Microsoft Visual Basic for Applications 400 error on macro

jmccullough

New Member
Hello All, I have an Excel workbook with 3 worksheets which contain the Data (worksheet1), a Pivot Tabe (worksheet2) and a Column Chart on worksheet3. What i am attempting to do is to color the columns according to month/yr. My data goes back 3 yrs. So for example, any data for Sep08, Sep09 and Sep10 will all be colored Yellow and so on. I have attached my macro to a button and placed it onto my chart. The column colors are working most of the time however if I click on the button to execute the colorchanger macro I receive the 400 Error. I have my macro stored in "VBAProject (Book1) This WorkBook" and I am running windows xp and using Excel 2010 (recently upgraded) but I had the same error using Excel 2007.


Any help would be greatly appreciated. Thank you in advance.
 
Could you post your macro? That would be more helpful. What strikes me as odd is having the macro in the ThisWorkbook module, as opposed to the chart module, or a regular module.


Also, is the chart a regular chart from the Data, or is it a PivotChart?
 
Thank you for the replies and I apologize for not responding sooner. The chart is a Pivot Chart and I can post the macro or upload the workbook to google docs if that will help. To upload the macro, do I just put the code in quotes? Sorry, I've never posted code before.


Thanks again for looking.
 
You can either just directly copy and paste the code in so it looks like this:


Sub Example

'I did something

End Sub


or you can use the HTML markup

{code}

Code:
Sub Example

'I did something

End Sub

{/code}


(replace the curly brackets with the less than/greater than symbols}
 
Here is my code. I Duplicate it multiple time to allow for all possible month dates. For example, I have multiple ActiveChart.SeriesCollections. Thanks again for any help.


'Sub ColorColumns()

' Dim iPoint As Long, nPoint As Long

' With ActiveChart.SeriesCollection(1)

' For iPoint = 1 To .Points.Count

' Select Case WorksheetFunction.Index(.XValues, iPoint)

' Case "c)Sep 09"

' .Points(iPoint).Interior.ColorIndex = 6 ' Yellow

' Case "d)Oct 09"

' .Points(iPoint).Interior.ColorIndex = 5 ' Blue

' Case "e)Nov 09"

' .Points(iPoint).Interior.ColorIndex = 3 ' Red

' Case "f)Dec 09"

' .Points(iPoint).Interior.ColorIndex = 13 ' Purple

' Case "g)Jan 10"

' .Points(iPoint).Interior.ColorIndex = 46 ' Orange

' Case "h)Feb 10"

' .Points(iPoint).Interior.ColorIndex = 4 ' Green

' Case "i)Mar 10"

' .Points(iPoint).Interior.ColorIndex = 8 ' Cyan

' Case "j)Apr 10"

' .Points(iPoint).Interior.ColorIndex = 7 ' Pink

' Case "k)May 10"

' .Points(iPoint).Interior.ColorIndex = 14 ' Teal

' Case "e)Jun 10"

' .Points(iPoint).Interior.ColorIndex = 43 ' Lime

' Case "f)Jul 10"

' .Points(iPoint).Interior.ColorIndex = 42 ' Aqua

' Case "g)Aug 10"

' .Points(iPoint).Interior.ColorIndex = 50 ' Sea Green

' Case "c)Sep 10"

' .Points(iPoint).Interior.ColorIndex = 6 ' Yellow

' Case "d)Oct 10"

' .Points(iPoint).Interior.ColorIndex = 5 ' Blue

' Case "e)Nov 10"

' .Points(iPoint).Interior.ColorIndex = 3 ' Red

' Case "f)Dec 10"

' .Points(iPoint).Interior.ColorIndex = 13 ' Purple

' Case "g)Jan 11"

' .Points(iPoint).Interior.ColorIndex = 46 ' Orange

' Case "h)Feb 11"

' .Points(iPoint).Interior.ColorIndex = 4 ' Green

' Case "i)Mar 11"

' .Points(iPoint).Interior.ColorIndex = 8 ' Cyan

' Case "j)Apr 11"

' .Points(iPoint).Interior.ColorIndex = 53 ' Brown

' Case "k)May 11"

' .Points(iPoint).Interior.ColorIndex = 14 ' Teal

' Case "e)Jun 11"

' .Points(iPoint).Interior.ColorIndex = 43 ' Lime

' Case "f)Jul 11"

' .Points(iPoint).Interior.ColorIndex = 42 ' Aqua

' Case "g)Aug 11"

' .Points(iPoint).Interior.ColorIndex = 50 ' Sea Green

' End Select

' Next

' End With

' End Sub
 
I shortned up your code a bit, and this seems to be working okay on my machine.

Code:
Sub ColorColumns()

Dim iPoint As Long, nPoint As Long, x As Integer

With ActiveChart.SeriesCollection(1)

For iPoint = 1 To .Points.Count


Select Case Mid(WorksheetFunction.Index(.XValues, iPoint), 3, 3)

Case "Sep"

x = 6  'Yellow

Case "Oct"

x = 5  'Blue

Case "Nov"

x = 3  'Red

Case "Dec"

x = 13  'Purple

Case "Jan"

x = 46  'Orange

Case "Feb"

x = 4  'Green

Case "Mar"

x = 8  'Cyan

Case "Apr"

x = 7  'Pink

Case "May"

x = 14  'Teal

Case "Jun"

x = 43  'Lime

Case "Jul"

x = 42  'Aqua

Case "Aug"

x = 50  'Sea Green

End Select


.Points(iPoint).Interior.ColorIndex = x

Next


End With

End Sub
 
Back
Top