List all TextBox on a sheet
Stephane,
What you have here is two controls at work. There is, as you would expect,
the textbox object,but there is also an OLEObject object that acts as a
container for the textbox on the worksheet.
The name of the OLEObject is what you see in the Names box on the worksheet,
and it is this that can be referred to as a variable in VBA.
The textbox name property is not something that you can use in a string
variable in VBA. You can create a textbox object as you did, but to do that
you have to get hold of that object somehow to begin with, whereas accessing
it through the OLEObjects collection, by the index or the name, cab give you
direct access.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"StefWork" wrote in message
. ..
Bob Phillips wrote:
If they are worksheet control toolbox text boxes then
Dim i As Long
For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "TextBox" Then
If IsNumeric(ActiveSheet.OLEObjects(i).Object.Value) Then
MsgBox ActiveSheet.OLEObjects(i).Name
End If
End If
Next i
This is great help for me thanks a lot but it work on this way and I
don't know why....
Dim i As Long
Dim ATextBox As MSForms.TextBox
For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "TextBox" Then
Set ATextBox = ActiveSheet.OLEObjects(i).Object
If IsNumeric(ATextBox.Value) Then
MsgBox ATextBox.Name
End If
End If
Next i
With you code the msgbox popup TextBox1, TextBox2, TextBox3 etc....
With my code the msgbox popup the value that I have enter in the Name
property of the object !
Your help was usefull for me
Many thanks !
Stephane Gagnon
|