Adding code behind newly created chartsheet - subscript out of range
I have an addin that I created that when run from the active worksheet creates a chart on a chartsheet withi the activeworkbook. I then am adding a code event behind the newly created sheet to catch a mouse down event.
Any attempts I have made to place this code with the sheet from the addin results in a subscript out of range error that seems to be indicating that the newly created sheet doesn't exist.
scode = "Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)" & vbNewLine
.......
........
scode = scode & vbTab & "With .Format.Line" & vbNewLine
scode = scode & vbTab & ".Visible = msoTrue" & Chr(13) & ".ForeColor = ptcolor" & Chr(13) & ".BackColor = ptcolor" & Chr(13) & "End With" & vbNewLine
scode = scode & vbTab & "End With" & vbNewLine
scode = scode & vbTab & "End If" & vbNewLine
scode = scode & vbTab & "End Sub"
ThisWorkbook.VBProject.VBComponents(ActiveSheet.Co deName).CodeModule.AddFromString scode
I have done this in earlier versions of excel and not had a problem so can't figure out why it is now an issue.
If I run it from a separate procedure in the activeworkbook it works with out issue. Maybe it is something to do with the addin trying to place the code in another workbook??
David
|