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