How to count drawing object (text box) in vba
 

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

How to count drawing object (text box) in vba
 

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





"geniusideas" wrote in message
...
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

How to count drawing object (text box) in vba
 

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

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"geniusideas" wrote:

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
.

How to count drawing object (text box) in vba
 

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

How to count drawing object (text box) in vba
 

Ah so! You are using the Forms Toolbar textbox.


"geniusideas" wrote in message
...
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