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

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" <(E-Mail Removed)> wrote in message
news:747c23ee-8609-4cec-93d6-(E-Mail Removed)...
> 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

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
> .
>

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

Ah so! You are using the Forms Toolbar textbox.


"geniusideas" <(E-Mail Removed)> wrote in message
news:d7b203d8-4048-4c3a-995a-(E-Mail Removed)...
> 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
>