R
Rodyna
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
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