Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

This code should find an ordinary/basic text box on a worksheet and then move it left. Then go to the next sheet and do the same thing. Do until the last sheet.

It works (as I cycle through it using F8), but then the next sheet it says the object doesn't support this property of method.

I've no idea why this is happening.

Dim I As Integer
Dim txBox As Shape
Set txBox = ActiveSheet.Shapes("Text Box 1")


For I = 1 To Worksheets().Count
    Worksheets(I).Activate
    txBox.Select
    **Selection.ShapeRange.IncrementLeft 586.5**

Next I

End Sub
share|improve this question

1 Answer 1

up vote 3 down vote accepted

Assuming the text box is named "Text Box 1" on each sheet, you just need to move the Set txBox = line inside your For loop. Otherwise, txBox refers just to the text box on the sheet that was active when you started your macro. Since txBox is no longer on the active sheet, Selection does not refer to txBox, but more likely a cell and not necessarily a text box, hence the "Object doesn't support this property or method" error. The code below will hopefully do the trick or at least get you moving in the right direction.

Dim I As Integer
Dim txBox As Shape

For I = 1 To Worksheets().Count
    Worksheets(I).Activate
    Set txBox = ActiveSheet.Shapes("Text Box 1")
    txBox.Select
    Selection.ShapeRange.IncrementLeft 586.5
Next I

Edit:
As an aside, you can improve the performance of your macro by avoiding all the .Select, .Activate, Selection calls.

Dim ws as Worksheet
Dim txBox As Shape

For Each ws in Worksheets
    Set txBox = ws.Shapes("Text Box 1")
    txBox.IncrementLeft 586.5
Next ws
share|improve this answer
    
Thank you. I've used your performance improving code example and it's works great. –  RocketGoal Jul 8 '13 at 14:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.