Copying text from all activex textboxes to a new worksheet

QuestionEdit

I have anfile with 10 worksheets. Each worksheet has about 5 to 10 activex textboxes. How do I extract the texts from these textboxes such that they will all go into an 11th worksheet - arranged per worksheet? For example, column 1 of worksheet 11 will contain the texts from the textboxes from worksheet 1, and so forth. Thanks.

AnswerEdit

Reeko assume the summary sheet is named summary (location to place the data)

Sub copydata() Dim sh As Worksheet, sh1 As Worksheet Dim col As Long, rw As Long Dim obj As OLEObject Set sh1 = Worksheets("Summary") col = 0 For Each sh In Worksheets

If sh.Name <> sh1.Name Then
 col = col + 1
 rw = 0
 For Each obj In sh.OLEObjects
   If TypeOf obj.Object Is MSForms.TextBox Then
      rw = rw + 1
      sh1.Cells(rw, col) = obj.Object.Value
   End If
 Next
End If

Next End Sub

That worked for me.