Excel 2007 VBA and Text Boxes

[crownbec]

I am a new to Excel 2007. I have been using Excel and VBA for 10+ years.

My problem is I cannot get VBA in Excel 2007 to recognize Text boxes I have
on a worksheet.

I have a number of legacy (2003) workbooks that contain text boxes that are
modified by VBA code.

When I try to run the 2003 code in a 2007 workbook I get an error.....â€Item
with the specified name wasn’t foundâ€

When I use the macro record in 2007 to record actions on shapes they record
nothing.

e.g. Create a text box and add text and color the box.

My result ...

Sub Macro1()
'
' Macro1 Macro
'

'
End Sub

.......absolutely nothing....I wonder if anyone could help. What am I doing
wrong?

Thanks

Gary

 

[Bob Phillips]

This works fine for me

Dim wsSheet As Worksheet
Dim sButtonName As String

Set wsSheet = ActiveSheet
sButtonName = "txtTest"
wsSheet.TextBoxes(sButtonName).Formula = "=A1"

and when I recorded a macro, I got

Sub Macro2()
ActiveCell.FormulaR1C1 = "=RC"
End Sub


They have broken lots of things in 2007, but this doesn't seem to be one of
them.

 

[crownbec]

This works fine for me

Dim wsSheet As Worksheet
Dim sButtonName As String

Set wsSheet = ActiveSheet
sButtonName = "txtTest"
wsSheet.TextBoxes(sButtonName).Formula = "=A1"

and when I recorded a macro, I got

Sub Macro2()
ActiveCell.FormulaR1C1 = "=RC"
End Sub


They have broken lots of things in 2007, but this doesn't seem to be one of
them.

--
__________________________________
HTH

Bob

Hi Bob,

Thanks for that. Glad to see you too get nothing (no box identified) when
you try to record a macro to record a macro.

I can't seem to get your VBA to work.

Sorry

Gary