Issue with EXCEL graphs in Office 2003

[Jennpen1]

The issue I am having is getting the graphs to update when I run a macro that redefines the range. Everything works fine in Office 2000 but not in Office 2003.

I have a file that contains multiple sheets:

Data sheets = there are many of these which all contain the same rows and columns but contain different data
"First" and "Last" sheets = sheets used to define data ranges
"Summary" Sheet = a sheet that contains a summary of data from the multiple data sheets, on this sheet the cells read from the "First" sheet to the "Last" sheet (hope that makes sense)
"Graphs" sheet = contains multiple graphs that read from the "Summary" Sheet, this sheet also contains a drop down menu that contains the individual names of the data sheets and a button that runs a macro

So the user selects from the drop down menu the data they want to see then runs the macro. The macro then places the "first" sheet in front of the data sheet (selected from the dropdown menu) and the "Last" sheet after it. So the summary sheet then reads from the selected data sheet and the graphs read that data.

In Office 2003, the macro runs fine and the first time it is run the graphs are correct. The second time it is run, the macro runs fine and the data on the summary sheet is updated correctly. However the graphs do not change. The cells they are reading from contain different data but it is like the graphs do not recognize it and hold the data that was previsouly there. If I were to close the file, save changes, and re-open, the graphs refresh and are correct.

This is hot happening in Office 2000, so is there something I need to do in Office 2003 to refresh the graphs? Could it be that Office 2003 was not installed properly?

 

[SkipVought]

Hi,

Confirm that the chart source data for each series is referencing the expected range.

Confirm that Tools>Option>Calculation Tab - is Automatic.

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[xlbo]

can you confirm that the charts are not pivot charts

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Jennpen1]

Source data is referencing the correct range. The odd part is that the range has the numbers I want plotted, but the graphs are plotting the data that used to be there.

Tools>Options>Calculation Tab is set to Automatic already. I pushed F9 just because I was desperate to try anything and nothing happens on the graphs.

Charts are not pivot charts. All of the charts are lines charts, made using the chart wizard.

 

[SkipVought]

So you...

activate the chart

right-click in the chart and select source data

select the Series tab

click in the Values textbox

and...

the CORRECT SOURCE DATA SHEET!RANGE is activated?

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[Jennpen1]

Yep, and just to reiterate, the data in that range is not what is being plotted on the graph.

Again, this is working fine in Office 2000 but not in Office 2003.

 

[SkipVought]

Our company just upgraded from 2000 to 2003 (within the past month)

I have ALOT of Excel application: data acquisition, data analysis, data reporting, charts -- it ALL works just fine!

There is SOMETHING that you are missing.

When you state, "this is working fine in Office 2000 but not in Office 2003." are you running both versions simultaneously? Or was it that it worked last week in 2000 but not this week in 2003?

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[xlbo]

Gotta concur with Skip here. From what has been posted, there is no reason your charts shouldn't update.

There must be something happening in the macro that is causing this behaviour as the data refreshes when you re-open the workbook - does the macro set any application / environment variables ?

Also, can you post the code that is running ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Jennpen1]

Trust me, I understand your feelings, this does not make sense. My office is slowly getting pushed Office 2003, so some people have Office 2003 and some still have Office 2000. I have e-mailed the file (the one that will not work on my computer) to a co-worker with Office 2000 and it worked fine on his. I have tried the file on several other computers with Office 2003 and it is not working on any of them.

The code I am using is as follows, but just know that the code itself does not define the graphs. It simply rearranges the worksheets so that the sheet that the graphs read from is between first and last. I have walked through the macro and it is doing everything exactly as it should be.

Private Sub CommandButton1_Click()
Dim desiredWorksheet2 As String
desiredWorksheet2 = Me.ComboBox1.Value
Range("a1").Select
ActiveCell.FormulaR1C1 = desiredWorksheet2

Sheets("SQ2").Select
Sheets("SQ2").Move After:=Sheets("OMEGA")
Sheets("SQ1").Select
Sheets("SQ1").Move Before:=Sheets(desiredWorksheet2)
Sheets("SQ2").Select
Sheets("SQ2").Move After:=Sheets(desiredWorksheet2)
Sheets("Squadron KM chart").Select

End Sub


Is there some kind of command that will refresh data after the code runs?

 

[xlbo]

Application.Calculate

should force a recalc - would be worth sticking it in as the last line on the macro just to check...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Jennpen1]

I added it and the graphs still do not update.

 

[xlbo]

Not sure I can suggest much more without seeing it 1st hand.....

Am on 2003 here - if you want to email me the file, I will take a quick look - if there is any sensitive data, just gobbledegook it

wefwaegeoff.asefaewfzzz3543barracloughfe@srferePunchTavernsaewfd\35qgva.comw4sdfaw45

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Jennpen1]

Geoff, I just sent the file to you. It is zipped so I hope you can open it.

I guess if it works on your computer that means that something must have been installed incorrectly.

Thanks for your help.

 

[xlbo]

Very weird - I get exactly the same symptoms.

Doesn't refresh on Save but does refresh on SaveAs !!!!

As far as I can tell, this is something to do with the DIV/0! errors in the source data - if I clear those cells, it calculates...in fact, if I edit any cell in the source data range, it refreshes the data.......

Any ideas Skip ???

JennPenn1 - if you include a SaveAs line in the code (as the last line of code), it should work:

ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & ThisWorkbook.Name

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Is this an undocumented FEATURE of 2003?

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[Jennpen1]

Geoff, Thanks for taking a look at it and confirming that I am not an idiot, at least not in this case. It is frustrating to have a problem that you cannot trace back to the cause.

I will have to resort to the Save As code as a type of band-aid to the problem. Thanks for the suggestion. Do you happen to know a way to bypass the dialog box that asks if you would like to replace the existing file through code? The less the user has to do the better.

Skip, I do not know if this is an undocumented FEATURE or an undocumented ISSUE. I am actively looking for info as to why this problem with 2003 and I am coming up blank.

Thanks to both of you for looking into this problem for me.

 

[xlbo]

Application.DisplayAlerts = false
ThisWorkbook.SaveAs ThisWorkbook.Path & "\" & ThisWorkbook.Name
Application.DisplayAlerts = true

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question