How to count drawing object (text box) in vba

[geniusideas]

Hi,

My next project is to count drawing object which is textbox place this
textbox content into cells.
For example:
TextBox1 = My Name
TextBox2 = is
TextBox3 = Micheal Jackson

Final result all these text is inside individual cell
Range(A1) = My Name
Range(A2) = Is
Range(A3) = Micheal Jackson

Please help in vba code.Thanks
Note : number of textbox is umlimited..
Please help

 

[JLGWhiz]

This will work if your textbox is from the Control Toolbox.

Sub way()
With Sheets(1)
.OLEObjects("Textbox1").Object = "My name"
.OLEObjects("Textbox2").Object = "is"
.OLEObjects("Textbox3").Object = "Mike Jackson"
End With
For i = 1 To 3
Sheets(1).Range("A" & i) = _
Sheets(1).OLEObjects("Textbox" & i).Object.Value
Next
End Sub

 

[Gary Brown]

Public Sub test_tb()
Dim x As Integer
Dim obj As Object

For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.TextBox Then
x = x + 1
Range("A" & x).Value = obj.Object.Value
End If
Next obj

End Sub

 

[geniusideas]

Dear Gary and JL,

Sorry, Both also not working but I found the answer as below

Option Explicit

Sub TextBoxCount()
Dim myDocument As Worksheet
Dim x As Integer
Dim sh As Object
Dim txtb As String

Set myDocument = ActiveSheet
x = 1
For Each sh In myDocument.Shapes
If sh.Type = msoTextBox Then
sh.Select
txtb = Selection.Characters.Text
Cells(x, 1) = txtb
x = x + 1
End If
Next
Range("A1").Select
End Sub

Thanks..anyway

 

[JLGWhiz]

Ah so! You are using the Forms Toolbar textbox.