Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
List all TextBox on a sheet
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
List all TextBox on a sheet
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) "StefWork" wrote in message . .. 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
List all TextBox on a sheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link a textbox on a sheet to a cell?! | Excel Discussion (Misc queries) | |||
Textbox changes position on a copied excel-sheet | Excel Discussion (Misc queries) | |||
Textbox on excel sheet do not update with VBA | Excel Programming | |||
new line at sheet textbox | Excel Programming | |||
Accessing a textbox on a sheet.... | Excel Programming |