Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 17th 20, 12:48 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2020
Posts: 9
Default 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


  #2   Report Post  
Old September 17th 20, 01:29 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2020
Posts: 9
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
code to attach a macro to a newly created worksheet C. Corodan Excel Programming 2 July 30th 08 03:09 PM
Adding Help Text to Newly Created Functions Jim Aksel Excel Programming 0 November 28th 07 11:29 PM
Add list to newly created menu Anthony Excel Discussion (Misc queries) 4 February 25th 05 02:53 AM
Subscript out of range when adding worksheet RB Smissaert Excel Programming 6 January 7th 04 04:11 PM
How do I return to newly created worksheet? JJ[_5_] Excel Programming 3 December 29th 03 09:36 PM


All times are GMT +1. The time now is 02:13 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017