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

Jed Shields

Active Member
Joined
Sep 7, 2011
Messages
283
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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...
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top