List all TextBox on a sheet

S

StefWork

Hi all,

I have 10 TextBox and a command button on an Excel Sheet


I don't want to do this in my code...

if IsNumeric(TextBox1.Value) and IsNumeric(TextBox2.Value) etc....


What I want to do is something like that :

For Each Component in Components
if (Component is TextBox) and IsNumeric(Component.Value) then
MsgBox Component.Name
end if
Next

Is this possible ? Any examples ?


Thanks+ACE-
 
B

Bob Phillips

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

StefWork

Bob said:
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
Click to expand...

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
 
B

Bob Phillips

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)
 
Top