ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Adding code behind newly created chartsheet - subscript out of range (https://www.excelbanter.com/excel-programming/454942-adding-code-behind-newly-created-chartsheet-subscript-out-range.html)

David Cuthill[_3_]

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


David Cuthill[_3_]

Adding code behind newly created chartsheet - subscript out of range
 
On Wednesday, 16 September 2020 at 17:48:47 UTC-6, David Cuthill wrote:
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



okay figured it out ...

needed to change ThisWorkbook to ActiveWorkbook and now it runs without issue.

David


All times are GMT +1. The time now is 12:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com