add multiple controls

[stewart]

i have a form with a Button (commandbutton1) and a textbox (tb2). The
button procedure takes the input in the textbox and uses it to
determine the number of text boxes it should create. The code appears
to work but I wanted to know if anyone can foresee any problems with
the way I have written the procedure. I am teaching myself VB so it
may not be the most effective way of completing the process. here is
the code, any help would be appreciated.

Private Sub CommandButton1_Click()


Dim i As Integer
For i = 1 To tb2.Value Step 1

With Me.Controls.Add("Forms.textbox.1")
.Top = 200 + (20 * i)
.Left = 15
.Height = 20
.Width = 50
.Name = "txt" & i
End With
Next i
End Sub

 

[Tom Ogilvy]

I don't see anything obvious.

 

[Dave Peterson]

I think I'd check to see that TB2.value was really numeric and maybe even add a
sanity check--If you know that you never want to add more than 20, then check
for that.

And what happens if they click that button a second time?

Did you want to remove the existing textboxes or come up with unique names?

 

[stewart]

I think I'd check to see that TB2.value was really numeric and maybe even add a
sanity check--If you know that you never want to add more than 20, then check
for that.

And what happens if they click that button a second time?

Did you want to remove the existing textboxes or come up with unique names?

Thank you dave for pointing those things out. I think I have taken
care of most of it in the code below. Is there code a can run through
a button click that can tell me the names of the controls on my
userform. For testing purposes I'd like to verify that the added
textboxes are being named properly so that i can begin to write code
for processing the data entered into them. THank you for any
suggestions.

Private Sub CommandButton1_Click()

Dim i As Integer

If Not IsNumeric(tb2.Value) Then
MsgBox "Please enter a number"
Exit Sub

ElseIf tb2.Value > 10 Then
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "You chose " & tb2.Value & " cashiers. Is this correct?" '
Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Verify Input" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbNo Then
MsgBox "Please re-enter number of cashiers"
Exit Sub
Else: GoTo x
End If
Else

x: For i = 1 To tb2.Value Step 1

With Me.Controls.Add("Forms.textbox.1")
.Top = 200 + (20 * i)
.Left = 15
.Height = 20
.Width = 50
.Name = "txtNum" & i
End With
Next i

CommandButton1.Enabled = False
End If

End Sub

 

[Dave Peterson]

Option Explicit
Sub testme01()
Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
MsgBox OLEObj.Name
End If
Next OLEObj
End Sub

Ron de Bruin has lots of information for working with shapes/controls:
http://www.rondebruin.nl/controlsobjectsworksheet.htm

 

[stewart]

Option Explicit
Sub testme01()
Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
MsgBox OLEObj.Name
End If
Next OLEObj
End Sub

Ron de Bruin has lots of information for working with shapes/controls:http://www.rondebruin.nl/controlsobjectsworksheet.htm

is that code designed for use in a userform? I can't seem to get it
to work. I do believe there is something wrong with the naming
portion of my code. I setup the test below

this put in a text box with a specific name of txtNum1
Private Sub CommandButton5_Click()
With Me.Controls.Add("Forms.textbox.1")
.Top = 300
.Left = 100
.Height = 15
.Width = 50
.Name = "txtNum1"
End With
End Sub

then i tried to place the value entered into hat textbox to a
worksheet with this

Private Sub CommandButton3_Click()
Cells(1, 1).Value = textnum1.Value
End Sub

and i get a message that says
"compile error variable not defined" and it highlights
"textnum1.value" in the code for button3

 

[stewart]

is that code designed for use in a userform? I can't seem to get it
to work. I do believe there is something wrong with the naming
portion of my code. I setup the test below

this put in a text box with a specific name of txtNum1
Private Sub CommandButton5_Click()
With Me.Controls.Add("Forms.textbox.1")
.Top = 300
.Left = 100
.Height = 15
.Width = 50
.Name = "txtNum1"
End With
End Sub

then i tried to place the value entered into hat textbox to a
worksheet with this

Private Sub CommandButton3_Click()
Cells(1, 1).Value = textnum1.Value
End Sub

and i get a message that says
"compile error variable not defined" and it highlights
"textnum1.value" in the code for button3

I did find an example of another code to check the name of my created
textboxes and it is properly naming them. However I still come up
with the error variable not defined when i try to access the data in
that box. ANy suggestions?

 

[Dave Peterson]

I used textbox1 as the name of my textbox:

Option Explicit
Private Sub CommandButton3_Click()
Me.Cells(1, 1).Value = Me.OLEObjects("textbox1").Object.Value
End Sub