Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control array problem
I haven't used control arrays before and am having trouble with setting
up and using them on a userform. I have Private Sub UserForm_Click() End Sub Private Sub UserForm_Initialize() TextBox1(0).Visible = True For i = 1 To 5 Load TextBox1(i) TextBox1(i).Text = "A" TextBox1(i).Visible = True TextBox1(i).Left = TextBox1(i - 1).Left + 30 Next i TextBox1(0).SetFocus End Sub as the code for userform1 and a module Sub test() UserForm1.Show End Sub On the userform, I have set the Tabindex for textbox1 to 0. When I run this, I get a Runtime error 13: Type Mismatch error. Could someone tell me what is causing this please? Also, is TextBox1(0).SetFocus ok to set the focus on the first textbox? Thanks Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control array problem
Control arrays don't exist in VBA, they are only in VB.
You can reference the controls using a loop something like this (which is written on the fly but should give you the idea). Sub LoopControls Dim lCounter as long For lCounter = 1 to 10 debug.print frmName.Controls("TextBox" & cstr(lcounter)).Text next lcounter End Sub -- Robin Hammond www.enhanceddatasystems.com "inquirer" wrote in message ... I haven't used control arrays before and am having trouble with setting up and using them on a userform. I have Private Sub UserForm_Click() End Sub Private Sub UserForm_Initialize() TextBox1(0).Visible = True For i = 1 To 5 Load TextBox1(i) TextBox1(i).Text = "A" TextBox1(i).Visible = True TextBox1(i).Left = TextBox1(i - 1).Left + 30 Next i TextBox1(0).SetFocus End Sub as the code for userform1 and a module Sub test() UserForm1.Show End Sub On the userform, I have set the Tabindex for textbox1 to 0. When I run this, I get a Runtime error 13: Type Mismatch error. Could someone tell me what is causing this please? Also, is TextBox1(0).SetFocus ok to set the focus on the first textbox? Thanks Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control array problem
Another way is to use the TypeOf operator while looping through
the Controls collection. E.g., Dim Ctrl As MSForms.Control For Each Ctrl In UserForm1.Controls If TypeOf Ctrl Is MSForms.TextBox Then Debug.Print Ctrl.Text End If Next Ctrl -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Robin Hammond" wrote in message ... Control arrays don't exist in VBA, they are only in VB. You can reference the controls using a loop something like this (which is written on the fly but should give you the idea). Sub LoopControls Dim lCounter as long For lCounter = 1 to 10 debug.print frmName.Controls("TextBox" & cstr(lcounter)).Text next lcounter End Sub -- Robin Hammond www.enhanceddatasystems.com "inquirer" wrote in message ... I haven't used control arrays before and am having trouble with setting up and using them on a userform. I have Private Sub UserForm_Click() End Sub Private Sub UserForm_Initialize() TextBox1(0).Visible = True For i = 1 To 5 Load TextBox1(i) TextBox1(i).Text = "A" TextBox1(i).Visible = True TextBox1(i).Left = TextBox1(i - 1).Left + 30 Next i TextBox1(0).SetFocus End Sub as the code for userform1 and a module Sub test() UserForm1.Show End Sub On the userform, I have set the Tabindex for textbox1 to 0. When I run this, I get a Runtime error 13: Type Mismatch error. Could someone tell me what is causing this please? Also, is TextBox1(0).SetFocus ok to set the focus on the first textbox? Thanks Chris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control array problem
Chip Pearson wrote:
Another way is to use the TypeOf operator while looping through the Controls collection. E.g., Dim Ctrl As MSForms.Control For Each Ctrl In UserForm1.Controls If TypeOf Ctrl Is MSForms.TextBox Then Debug.Print Ctrl.Text End If Next Ctrl Thank you, that helps a lot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Control Array Sum Formula | Excel Discussion (Misc queries) | |||
Control Array Help... | Excel Programming | |||
Problem: Control Toolbox Control resizes when clicked | Excel Programming | |||
Creating a Control Array | Excel Programming | |||
Set Focus Problem for textbox control on multipage control | Excel Programming |