Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add multiple controls
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add multiple controls
I don't see anything obvious.
-- Regards, Tom Ogilvy "stewart" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
add multiple controls
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 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
add multiple controls
On Sep 10, 10:37 pm, Dave Peterson wrote:
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 wrote: 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 -- Dave Peterson 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
add multiple controls
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 wrote: On Sep 10, 10:37 pm, Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
add multiple controls
On Sep 11, 6:19 pm, Dave Peterson wrote:
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 wrote: On Sep 10, 10:37 pm, Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
add multiple controls
On Sep 12, 7:21 am, stewart wrote:
On Sep 11, 6:19 pm, Dave Peterson wrote: 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 wrote: On Sep 10, 10:37 pm, Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson 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 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
add multiple controls
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 stewart wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot select multiple controls | New Users to Excel | |||
Rename multiple controls. | Excel Programming | |||
Rename multiple controls. | Excel Programming | |||
Looping through multiple controls | Excel Programming | |||
Using same code for Multiple cmd Controls | Excel Programming |