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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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,102,556
Messages
5,487,538
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top