Please help - Runtime Error 13 on graphs

[Sandra]

On one of our workbooks we have several graphs that pull
information from the other worksheets within the
workbook. When I click on the tab (worksheet) that
contains the graph(s), I get the message "Runtime Error
13: Type Mismatch." This error has given us a lot of
problems and has even screwed up data in this and other
open workbooks.

Please, can someone help me figure out how to look into
this problem? I know very little about graphs in Excel so
please be very details and step-by-step.

Sandra

 

[Jon Peltier]

Sandra -

There is some kind of macro that is kicked off when you activate the
sheet with the chart. Right click on the sheet tab, and select View
Code. If you see a line that says

Private Sub Worksheet_Activate()

select the line and press the F9 key. Then keep pressing the F8 key as
the macro steps through, and see which line is causing the error. Ask
someone there for assistance, or post back with the code that's causing
problems. Clearly indicate which line is the bad one, and use a
technique to indicate it that is visible on a text only newsreader
(e.g., put a line of asterixes above and below the bad line, don't color
it red or bold it).


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Thanks Jon for your timely response. Unfortunatley, when
I do the "view code", it is just blank. there is NOTHING
and I have tried everything. What am I doing wrong? How
do I view the code? I know there must be something there
but everythign is blank.

Sandra

 

[Sandra]

Ok one more thing....I tried it again but I changed it
to "Chart" from "General" in Visual Basic, and it gave me
just:

Private Sub Worksheet_Activate()

End Sub

There is nothing in between so I don't know what the
problem is.

Sandra

 

[Jon Peltier]

It might be a Worksheet_Deactivate procedure: look for those on the
other sheets.

It might be a workbook level procedure. Press Alt-F11 to open the VB
Editor, find your project in the project explorer (press Ctrl+R to
highlight this window), click the little plus sign if necessary to see
Microsoft Excel Objects, and that little plus sign if necessary to see
ThisWorkbook, then double click on this workbook. Do you see one of these:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

Do other people have the problem with this file on other computers? Do
you have this problem with other files?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Sandra]

Jon, again thanks sooooo much for your help. Ok this is
what I did. I looked for anything and I ended up finding
a few things:
1) It seems there were two versions of the same file
open. I'm not sure why that is but it showed up in VB as
two projects. Can you explain this? I didn't have it open
twice!
2) Two things I previously neglected to say is that we are
using Win 2000 and Win XP. Win XP has the ability to
insert into the footer (through Page Setup) the filename
and path. Win 2000 can only insert the file name. We
previously installed an add-in suggested on this web board
so that our Win 2000 computers could insert the path also.
Once we started getting the error, I tried removing that
add-in but it didn't seem to change the error. But yes,
both the Win 2000 computer and the XP computer both use
the file and get the same error message. Secondly, when i
was opening up the file before, i would get the question
of whether i wanted to enable macros. So I figured there
had to be a macro that was causing this problem, which is
why I originally tried to get rid of the add-in.
3) On the workbook level of each VB project, I found the
following:
****
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "&8" &
ActiveWorkbook.FullName
End Sub
*****
I deleted this from both and reopened the file and lo and
behold it didn't give me the error or ask if i wanted to
enable macros.

So did this fix the problem? Only time will tell. If you
can explain any more please do, otherwise I will wait to
see if this helped.

Sandra

 

[Sandra]

Grrrrr....I spoke too soon. The problem is not fixed. It
seemed that I don't get the error message again once I've
gotten it the first time until I close Excel totally and
reopen it. So ok, so I close excel, reopen Excel and open
the file or open the file from the Start-Documents menu,
and I get the error again.

Ok let's start over. My first question is, if I have a
Private Sub - End Sub with nothing in between, will this
affect anything else? I cannot find anything in the whole
file.

In fact, we just re-did this entire workbook (probably two
dozen worksheets all interconnected) this year for 2004
because the 2003 workbook was getting the error. It seems
whatever the error was, it was not fixed. What else can I
do? I'm starting to pull my hair out.

Sandra

 

[Tushar Mehta]

For someone who knows very little about XL graphs, you seem to be doing
a very thorough job of diagnosing the problem. <g>

Do you have any other add-ins active? Check Tools | Add-Ins... and
Tools | COM Add-Ins... (Unfortunately, IIRC, it is possible for an add-
in to install itself and not show up on either list).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Sandra]

well I know a bit about VB but not about how to set up
graphs to pull the data from other worksheets.

Ok the only other add-in I see is the analysis toolpack.
Shows up in VB as funcres i think.

Sandra

 

[Jon Peltier]

Sandra -

The empty event procedure should not be an issue. You could just delete
it anyway. The analysis toolpack should be okay. And I don't see
anything improper about the _BeforePrint routine.

Also, it's Office XP (Excel XP) and Office 2000 (Excel 2000) that have
the different capabilities you mentioned. Win XP and Win 2000 are the
platforms within which they can be run. And the OS suffix need not match
the Office suffix.

I was hoping you'd be able to find a procedure that ran on its own, so
you could set a breakpoint that allowed you to step through it, and find
the line that raised the error.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______