Stacked column next to a clustered column for each month in a 12 month range?

Jed Shields

Active Member
Joined
Sep 7, 2011
Messages
278
Hi guys,

I'm trying to create a stacked column chart next to a standard column but am having trouble.

For the months Jan to Dec, I want to see s stacked column showing:

Sales 1
Sales 2
Sales 3

So Sales 1 on top of 2 on top of 3

However, next to it I'd like to show a single column for Losses.

I can get it working if I change the chart type of Losses to a line, scatter etc, but I'd really like a comparable column. I can sort of get something along the right lines, by using two columns for each month, Sales in one, Losses in the other, but if I try to change the chart type on one it chnages it for all of them.

If I put Losses on the secondary axis then it seems to work, but I need to make sure that the primary and secondary axis are identical. I've tried doing this with some code that I found but it doesn't appear to work...

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address
    Case "$BQ$3"
        ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlCategory) _
            .MaximumScale = Target.Value
    Case "$BQ$4"
        ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlCategory) _
            .MinimumScale = Target.Value
    Case "$BQ$5"
        ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlCategory) _
            .MajorUnit = Target.Value
    Case "$BR$3"
        ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlValue) _
            .MaximumScale = Target.Value
    Case "$BR$4"
        ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlValue) _
            .MinimumScale = Target.Value
    Case "$BR$5"
        ActiveSheet.ChartObjects("Chart 12").Chart.Axes(xlValue) _
            .MajorUnit = Target.Value
    Case Else
End Select

End Sub</pre>
Is there an obvious solution? Am I on the right tracks, but ****ed up the code?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Jed Shields

Active Member
Joined
Sep 7, 2011
Messages
278
Okay, I've managed to get the two seperate columns using a primary and secondary axis. However, I'm no closer to getting the code to match the maximum axis values to work:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Select Case Target.Address
    
    Case "$BQ$3"
        ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlValue) _
            .MaximumScale = Target.Value
    Case "$BQ$4"
        ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlValue) _
            .MinimumScale = Target.Value
    Case "$BQ$5"
        ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlValue) _
            .MajorUnit = Target.Value
        
    Case "$BR$3"
        ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlSecondary) _
            .MaximumScale = Target.Value
    Case "$BR$4"
        ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlSecondary) _
            .MinimumScale = Target.Value
    Case "$BR$5"
        ActiveSheet.ChartObjects("Chart 36").Chart.Axes(xlSecondary) _
            .MajorUnit = Target.Value
    Case Else
    
End Select

End Sub
I've no idea if Chart.Axes(xlSecondary) is the correct syntax for the secondary y axis...
 

Jed Shields

Active Member
Joined
Sep 7, 2011
Messages
278
Aha, that was the page that I used to get it sort of working! I've just noticed that you've managed to get both sets of charts working off the same axis, I'll have to have a good read :)
 

Watch MrExcel Video

Forum statistics

Threads
1,096,033
Messages
5,447,998
Members
405,477
Latest member
dsteffen1016

This Week's Hot Topics

Top