Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming with the VBE using AddFromFile gives Run time error #9
I'm sorry if my post is very long, My problem has many parts and isn't easy
to explain. I have a workbook with 2 worksheets and 11 chart sheets. I wanted a Table of Contents page in my workbook that showed the chart sheets. I know Excel 2003 isn't designed to do that but I googled like crazy till I found the workaround. My main macro first inserts a TOC page, lists the sheets then lists the names of the chart sheets. To get around not being able to hyperlink to a chart sheet I use the code below that would imitate a hyperlink for 1 chart. To make it work on all the charts listed i copied the if statment 11 times and changed the range accordingly. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Worksheets("TOC").Range("A" & 11)) Is Nothing Then On Error Resume Next Charts(Target.Value).Activate If Err.Number < 0 Then MsgBox "No such chart exists.", vbCritical, _ "Chart Not Found" End If On Error GoTo 0 End If 'repeats for the next 11 cells in the A column end sub This piece of code has to be put in the sheet code named TOC (in VBE) to work. It works fine. But I didn't want to have to copy and paste this into the sheet everytime I ran my macro. So I decided to try using some automation. I tried to programme with the vbe and have my long private sub in a text file. I then wrote : ActiveWorkbook.VBProject.VBComponents(Worksheets(" TOC").CodeName) _ ..CodeModule.AddFromFile "C:\Documents and Settings\ro281815\My Documents\handleCharthyperlink.txt" Here is where it gets interesting. If I have the VBE open and run my macro it works fine. BUT if I have the VBE closed the AddFromFile causes an error #9 . So I tried to debug, used F8 to step through my code, get to my AddFromFile line and Excel decided that it encountered a problem and has to close. So I thought maybe there's something wrong in how I'm using VBCodemodule, I tested AddFromString with a tiny sub and ran it with the VBE open, no errors. Ran it by stepping through , no errors. Ran it closed and got error #9 (1) Does VBE have to be open when trying to programme with VBE components? (2)Why does using F8 throw up errors when VBE is open? I don't want to have to make my long private sub as a string, but it doesn't look like I have much choice. I hope someone can help me. Thanks, Rodyna |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula causing run-time error in Excel Programming | Excel Worksheet Functions | |||
Button programming Run-time error '1004' with range | Excel Programming | |||
CodeModule.AddFromFile problem | Excel Programming | |||
modifying code using addfromfile method | Excel Programming | |||
Reload Typelibrary? .Remove() & .AddFromFile() fails | Excel Programming |