2003 macro generates compiler error on 2007
 

The following macro works fine in Excel 2003 but generates a compiler error
on 2007. Any ideas or direction?

Sub WhoRang()
Dim ButtonLabel As String
Dim First6chars As String
Dim RowNum As String
ButtonLabel = Application.Caller
' 2007 compiler error occurs here
First6chars =
Left(ActiveSheet.Shapes(ButtonLabel).TextFrame.Cha racters.Text, 6)
RowNum = Range("N1:N32").Find(What:=First6chars).Row
Application.GoTo Range(Range("O" & RowNum).Value)
End Sub

2003 macro generates compiler error on 2007
 

My recommendation is to record a macro in 2007 as you go through that process
manually and compare what is recorded to the 2003 code.

There are some strange things going on with command buttons on worksheets
created in earlier versions of Excel. I haven't been able to pin it down. I
had a request recently to fix a package that I created for AT&T that was
originally created in Excel 97 and worked faithfully right up until it was
used in 2007. Believe it or not, I ran into failures on the sheet that
seemed to actually be related to either the location or size of the button(s)
involved. I came up with a work-around by taking the coward's path of not
recreating all the buttons on the sheet that I had been, and was lucky in
that the ones that did have to be recreated were all handled properly. The
ones that caused failure were created and manipulated in exactly the same way
except that they were larger sized, had more text in them and were of course,
in different locations on the sheet.

In your case we probably need to drag out the Excel 2007 Object Model
http://msdn.microsoft.com/en-us/library/bb332345.aspx
and make sure that your reference to the shape and its properties is
completely accurate and that Excel 2007 understands it all. I presume that
the button has at least 6 characters in its caption. And that leads to an
idea that you may need to be referencing the .Caption instead of
..TextFrame.Characters.Text ??

This link, to information about the Shapes Object may help you:
http://msdn.microsoft.com/en-us/library/bb178401.aspx

"KenInPortland" wrote:

The following macro works fine in Excel 2003 but generates a compiler error
on 2007. Any ideas or direction?

Sub WhoRang()
Dim ButtonLabel As String
Dim First6chars As String
Dim RowNum As String
ButtonLabel = Application.Caller
' 2007 compiler error occurs here
First6chars =
Left(ActiveSheet.Shapes(ButtonLabel).TextFrame.Cha racters.Text, 6)
RowNum = Range("N1:N32").Find(What:=First6chars).Row
Application.GoTo Range(Range("O" & RowNum).Value)
End Sub

2003 macro generates compiler error on 2007
 

Thanks for your help, JL
I investigated the .Caption property, but it did not fix the 2007
compilation problem, but your point is well taken about the .Text property
and I did leave the change in. It turned out the problem was the LEN
function in my procedure. Apparently 2007 requires the full specification of
"VBA.Strings.Len()" or it gets confused with LEN properties.

"JLatham" wrote:

My recommendation is to record a macro in 2007 as you go through that process
manually and compare what is recorded to the 2003 code.

There are some strange things going on with command buttons on worksheets
created in earlier versions of Excel. I haven't been able to pin it down. I
had a request recently to fix a package that I created for AT&T that was
originally created in Excel 97 and worked faithfully right up until it was
used in 2007. Believe it or not, I ran into failures on the sheet that
seemed to actually be related to either the location or size of the button(s)
involved. I came up with a work-around by taking the coward's path of not
recreating all the buttons on the sheet that I had been, and was lucky in
that the ones that did have to be recreated were all handled properly. The
ones that caused failure were created and manipulated in exactly the same way
except that they were larger sized, had more text in them and were of course,
in different locations on the sheet.

In your case we probably need to drag out the Excel 2007 Object Model
http://msdn.microsoft.com/en-us/library/bb332345.aspx
and make sure that your reference to the shape and its properties is
completely accurate and that Excel 2007 understands it all. I presume that
the button has at least 6 characters in its caption. And that leads to an
idea that you may need to be referencing the .Caption instead of
.TextFrame.Characters.Text ??

This link, to information about the Shapes Object may help you:
http://msdn.microsoft.com/en-us/library/bb178401.aspx

"KenInPortland" wrote:

The following macro works fine in Excel 2003 but generates a compiler error
on 2007. Any ideas or direction?

Sub WhoRang()
Dim ButtonLabel As String
Dim First6chars As String
Dim RowNum As String
ButtonLabel = Application.Caller
' 2007 compiler error occurs here
First6chars =
Left(ActiveSheet.Shapes(ButtonLabel).TextFrame.Cha racters.Text, 6)
RowNum = Range("N1:N32").Find(What:=First6chars).Row
Application.GoTo Range(Range("O" & RowNum).Value)
End Sub