Bringing relevant chart to the front 1

[john434]

Hi all,

I've created a report which can be run at different levels, i.e. countywide and for each of 3 different regions.

Part of the report is a trend analysis which will show one of 4 graphs of trend information.

As i'm very inexperianced with VBA, i'm probably not going about this the right way, however here is my position.

I have all 4 charts placed one on top of the other within my workbook and have the following code to bring the relevant chart to the front, based on a field within the worksheet.

For instance when the check field = "Countywide" chart 3 will be bought to the front. check field = "Northern" chart 7 will be bought to the front and so on.

Here is my code, which appears to be doing nothing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Check As String
Check = R4C16
If Check = "Countywide" Then
ActiveSheet.Shapes("Chart 3").Select
Selection.ShapeRange.ZOrder msoBringToFront
If Check = "Eastern" Then
ActiveSheet.Shapes("Chart 6").Select
Selection.ShapeRange.ZOrder msoBringToFront
If Check = "Southern" Then
ActiveSheet.Shapes("Chart 8").Select
Selection.ShapeRange.ZOrder msoBringToFront
If Check = "Northern" Then
ActiveSheet.Shapes("Chart 7").Select
Selection.ShapeRange.ZOrder msoBringToFront
End If
End If
End If
End If
End Sub

You guys and girls will no doubt spot the problem straight away, so thanks in advance for your help!

Cheers

 

[PHV]

I think you want to replace this:
Check = R4C16
with this:
Check = Range("P4")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

I often have Excel workbook applications where the user makes a selection and then the chart displays the data associated with that selection.

I do this with ONE CHART and a Forms DropDown control.

The simplest way is to filter the source data to reflect the data associated with the selection. But there are other ways as well.

The beauty of this ONE CHART approch is that you only have ONE CHART to maintain, when changes need to be made, which is a sure as the sun rising in the east. There is very little VBA code needed.

In your case then charts in your sheet can be positioned as such, for instance...

    ActiveSheet.Shapes("Chart 2").ZOrder msoBringToFront

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

Guess that you need to test cell rather than variable. Additionally slightly cleaned:
{code]Private Sub Worksheet_Change(ByVal Target As Range)
With Me
Select Case .Cells(4,16)
Case "Countywide"
.Shapes("Chart 3").ZOrder msoBringToFront
Case "Eastern"
.Shapes("Chart 6").ZOrder msoBringToFront
Case "Southern"
.Shapes("Chart 8").ZOrder msoBringToFront
Case "Northern"
.Shapes("Chart 7").ZOrder msoBringToFront
End Select
End With
End Sub[/code]

combo

 

[john434]

Thanks Combo,

that works perfectly.

Seems to me even with my limited knowledge, that Select Case can be used instead of if-then-else, is this correct?

Also, just so i can start to understand

"Private Sub Worksheet_change(ByVal target As Range) with Me" what does the bold "With Me" and the following "end with" bit actually do?

Thanks again

 

[combo]

The 'Select Case' is more or less similar to If.. End If, but typically one input value is tested against a set of conditions. It is more flexible, as for instance it is possible to mimic If..End if with:

Select Case True
Case [i]Condition1][/i]
    ' path 1
Case [i]Condition2][/i]
    ' path 2
'...
End Select

Me refers to object linked with calling module. As you used worksheet's event module, 'Me' is here the worksheet (object). I assumed that charts are on it.

[tt]With..End With[/tt] allow to continue refering to object that is after [tt]With[/tt] using dot (.), until the end of structure.

If you look at your original code you can find embedded Ifs. This will work in the way that if main condition fails the code quits. So you would rather need ElseIf in plain structure instead of nesting.

combo