Application.Caller Issue

[Stretchwickster]

Hi there,

On running a macro (originally written in Excel 2003) in Excel 2007 I get a "Run-time error '9': Subscript out of range" error which arises from the following code:

Public Sub GoToChart()

  ' check which text box button called the macro and activate appropriate sheet
  If TypeName(Application.Caller) = "String" Then
    Sheets(Application.Caller).Activate [COLOR=red]'this line triggers error[/color]
  End If
    
End Sub

On further investigation it appears that the string value contained in Application.Caller is missing the last character and therefore an attempt is made to activate a sheet which doesn't exist. In Excel 2003, this last character is included and so the appropriate sheet is correctly activated.

I should explain that I use the following code to dynamically add a textbox (which, when clicked, calls the above GoToChart sub) to a worksheet to provide an easy means of navigating between a worksheet containing a table of data and a worksheet charting that tabular data.

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, ALeft, ATop, 180, 18).Select
With Selection
  .Name = ActiveChart.Name
  .Characters.Text = "Go to Percentage Chart " & chartCounter
  .OnAction = "GoToChart"
End With

Any solutions would be gratefully received.

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[PHV]

What about replacing this:
.Name = ActiveChart.Name
with this:
.Name = ActiveChart.Name & " "

and this:
Sheets(Application.Caller).Activate
with this ?
Sheets(Trim(Application.Caller)).Activate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stretchwickster]

Thanks for the suggestion - but unfortunately it made no difference. It appears that, in Excel 2007, when a string is returned in Application.Caller it is limited to 30 characters.

Does anyone know why this limit has been imposed? Or if it's documented anywhere?

Any other ideas for workarounds/solutions?

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[SkipVought]

What about shorter sheet names?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Stretchwickster]

I guess I'm going to have to go with shorter sheet names due to Microsoft imposing this arbitrary 30 character constraint in Excel 2007 - very annoying. I wish they would ensure backwards compatibility, as I noticed they removed some additional chart properties which threw up errors in Excel 2007 too.

For info, I've handled the "subscript out of range" error more gracefully by checking the sheet exists before activating it.

Clive

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"To err is human, but to really foul things up you need a computer." (Paul Ehrlich)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
To get the best answers from this forum see: faq102-5096

 

[SkipVought]

For info, I've handled the "subscript out of range" error more gracefully by checking the sheet exists before activating it.

Luv that grace! Good for you!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}