Formatting axes based on cell values

[doublej41]

I am trying to set the maximum value for the x-axis of my chart based on the value I enter into a cell.

I am using the following VBA code under a worksheet change event:

Option Explicit
x1Category As Range


Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
    Case "$C$1"
        ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Category) _
            .MaximumScale = Target.Value
    Case Else
End Select

End Sub

When I change the value in cell C1, I get a Run-time error 13 and the message 'Type mismatch'. Can anyone shed some light on what may be causing this error?

I am trying to do this with a XY scatter graph.

Thanks.

 

[doublej41]

It's an XY scatter.

It will be very useful for the people using the charts as the auto scaling doesn't do the best job for what we need. I thought it was going to be an easier task than this though.

 

[Jon Peltier]

If the max scale happens to be lower than the minimum scale, and the minimum scale is not auto, you'll get an error. Also if the chart's on a protected sheet and hasn't been unlocked, you can't change anything.

 

[doublej41]

There is my problem, all of the sheets are protected, and they need to be. Could unlocking certain cells be a workaround at all, or will protecting the worksheet stop it working period?

Thanks for all of your help. You can probably tell that the code I was using was from your website. It's a very helpful tool.

 

[Jon Peltier]

If the changing cell is a formula, it will change even if it's locked and the sheet is protected. But if the chart is also locked, you can't change it. You can unlock the chart, but then the users could also change it.

You can insert code that will unprotect the worksheet then reprotect it. The weakness with this is that someone could find the password, unless you protect the VBA project (even if you protect it, if they are smart and sneaky).

I find bits and pieces of my code everywhere. Sometimes it's real old code that's very ugly, other times it's newer code that's merely unattractive. Sometimes it's been modified, sometimes well, but more often incorrectly. Usually it runs, unless there's an issue with Excel versions, or unless it's been applied in the wrong context. I think my code will outlive me.

 

[doublej41]

I have inserted some code to unprotect and protect the worksheet. There is a slim chance any of the users would see the code and therefore the password, and even if they did it wouldn't matter.

Is it possible to adapt the Worksheet_Calculate function so that it will only execute the code if a particular sheet is active? I am using this code to set the axes for charts on 4 separate sheets and there is a cell on a fifth that updates a cell on each of the 4 sheets. So when I update the cell on sheet 5, I can see the spreadsheet refreshing several times as it carries out the calculations on sheets 1-4.

Essentially what I would like is for it to act as a Worksheet_Change event, but as you mentioned above this will not work with a formula in the target cell. So, is there another way I can eliminate executing the code unnecesarily, so that the code is only executed when data is pasted to that particular sheet?

I think my code will outlive me.

I think you are right there. People like me will be finding your code on google for years to come .

 

[Jon Peltier]

The worksheet_calculate code should be on the module associated with the worksheet. This way, it will only fire when that worksheet recalculates. You could also test the value in the cell to see if it's different than the axis parameter, and if it's the same, skip the step that adjusts the axis.

 

[doublej41]

Sheets 1-4 have a formula that includes a cell from sheet 5, so whenever I update the cell in sheet 5 it is recalculating sheets 1-4.

Clutching at straws here, but could an if statement (or similar) be used to execute the code only if a certain cell changes on one of sheets 1-4. i.e. Sheet 1 will only execute the code if cell A1 on sheet 1 is updated?

 

[Jon Peltier]

Is Sheet1!A1 changing because the user entered a new formula, or because it has a formula? You could put the Wkbk_Change procedure on any sheet's module and it will fire when that sheet is changed. Then you can have the code change a chart anywhere else.

 

[doublej41]

Sheet1!A1 is changing because a user pastes data into the cell.

You could put the Wkbk_Change procedure on any sheet's module and it will fire when that sheet is changed.

How do I go about doing that? The change event wouldn't work previously as I had a formula in the target cell. I also got an error if I tried to use one cell to execute the change event and a different cell to contain the value for my axes.

 

[Jon Peltier]

Okay, let's start from scratch. My assumptions:

1. Cell A1 changes because a value is entered into it, either by typing or by pasting.

2. The X axis maximum will take on the value of cell A1.

Activate the sheet with cell A1 mentioned above and with the chart. Right click on the sheet tab, and choose View Code. This opens a code module that has the worksheet name at the top. It also has two dropdowns at the top.

In the left dropdown, choose Worksheet. This empty procedure stub appears in the module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

You can delete it. In the right dropdown choose Change. This appears in the module:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Whenever any cell is changed by changing the contents (not by calculating a formula) this procedure runs, and the range that changes is passed into the procedure as Target, so you can figure out what changed.

Here are some pieces we need:

1. If Not Intersect(Target, Me.Range("A1") Is Nothing Then

This is VBA speak for "If the changed range includes cell A1". If it doesn't include A1, then the intersection of A1 and the changed range is nothing, so we want the case when it is not nothing. In the context of this module, "Me" means this worksheet.

2. If Me.ChartObjects(1).Axes(xlCategory).MaximumScale <> Me.Range("A1").Value Then

This compares the current axis maximum to the cell value. If it doesn't match, we want to change the axis.

All together now:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("A1") Is Nothing Then

        If Me.ChartObjects(1).Axes(xlCategory).MaximumScale <> Me.Range("A1").Value Then

            Me.ChartObjects(1).Axes(xlCategory).MaximumScale = Me.Range("A1").Value

        End If

    End If

End Sub