Solved: changing textboxes' properties programmatically in a userform

[mancubus]

hi all.

i have a userform with 95 textboxes.

i wanted to change the 3 properties of each by below code with no success.

any ideas?

[VBA]Sub chg_uf_tb_prop()

Dim uf As UserForm
Dim ctrl As Control

Set uf = UserForm1

For Each ctrl In uf.Controls
If TypeName(ctrl) = "TextBox" Then
With ctrl
.TabStop = True
.TabKeyBehavior = False
.MultiLine = False
End With
End If
Next ctrl

End Sub[/VBA]

[mikerickson]

Do you want these changes to be permanent or just for this one instance of the userform?

[Paul_Hossler]

I think I had to use something like this below,

For Each did not seem to work, but For I = 1 to ... did

[VBA]
Sub chg_uf_tb_prop()

Dim uf As UserForm
Dim ctrl As Control

Dim i as Long

Set uf = UserForm1

For i = 1 to uf.Controls.Count
If TypeName(uf.Controls(i)) = "TextBox" Then
With uf.Controls(i)
.TabStop = True
.TabKeyBehavior = False
.MultiLine = False
End With
End If
Next i

End
[/VBA]

Crossing my fingers and hoping I remembered right

Paul

[mancubus]

Do you want these changes to be permanent or just for this one instance of the userform?

thanks mike.
permanent.

and...
i would like to hear about temporary changes, if possible...

[mancubus]

I think I had to use something like this below,

For Each did not seem to work, but For I = 1 to ... did

thanks paul.

i'll give it a try.

[mancubus]

paul,

it throws "Invalid Argument" error on
[VBA]
If TypeName(uf.Controls(i)) = "TextBox" Then
[/VBA]

[GTO]

Hi mancubus,

Maybe I'm missing something obvious. If we were positioning/re-positioning a bunch of controls, I would understand doing it via code, but for the listed properties, wouldn't it be easiest just to select all the textboxes (design-time) and change these in the properties window?

That said, I think this would work:
[VBA]Sub ChangeStuff()
Dim o As Object
Dim i As Long

Set o = ThisWorkbook.VBProject.VBComponents("UserForm1").Designer
For i = 1 To o.Controls.Count
If TypeName(o.Controls(i - 1)) = "TextBox" Then
o.Controls(i - 1).TabStop = True
o.Controls(i - 1).TabKeyBehavior = False
o.Controls(i - 1).MultiLine = False
'o.Controls(i - 1).BackColor = &H80000005 ' &HC0E0FF
End If
Next
End Sub[/VBA]

[Paul_Hossler]

mancubus -- sorry, the trick is that the Controls collection starts at 0 and goes to .Count-1

[VBA]

Sub test()
Dim i As Long
Load UserForm1

For i = 1 To UserForm1.Controls.Count
If TypeName(UserForm1.Controls(i - 1)) = "TextBox" Then
MsgBox "Is Textbox"
Else
MsgBox "Not TextBox"
End If
Next i
End Sub
[/VBA]

Paul

[mancubus]

Hi mancubus,

Maybe I'm missing something obvious. If we were positioning/re-positioning a bunch of controls, I would understand doing it via code, but for the listed properties, wouldn't it be easiest just to select all the textboxes (design-time) and change these in the properties window?

That said, I think this would work:

thanks gto.
it worked.

to tell the truth, i never thought of it.

it's an inherited wb. uf contains almost every mostly used controls...
CommandButtons
Frames
Labels
TextBoxes
ComboBoxes
Checkboxes


btw, i tried.
if i select with mouse or from edit/select all, all controls are selected and only tabstop property is visible.
is there a way to only select certain type of control?

[GTO]

thanks gto.
it worked.

Glad that worked

btw, i tried.
if i select with mouse or from edit/select all, all controls are selected and only tabstop property is visible.
is there a way to only select certain type of control?

Well, its actually not selecting a certain type per se, but you can select non-contiguous controls just like we do cells on a sheet. With a userform, make sure you have the userform selected first (so that we don't accidently include a non wanted control). Hold down the CTRL key and left-click once on each textbox.

[mancubus]

Glad that worked



Well, its actually not selecting a certain type per se, but you can select non-contiguous controls just like we do cells on a sheet. With a userform, make sure you have the userform selected first (so that we don't accidently include a non wanted control). Hold down the CTRL key and left-click once on each textbox.

thanks.
i know that. thats what i did at first. but when it seemed to me that it would take years to select all textboxes one by one i tried mouse and edit control.
so using vba seems the best option for me.

[mancubus]

mancubus -- sorry, the trick is that the Controls collection starts at 0 and goes to .Count-1

thanks again paul.

that's ok.

i used some coding -that i reached before opening the thread- in which the loop begins with 0, but i missed the point.

[mikerickson]

To progamaticaly change the properties of a userform's controls, one has to use the Designer property of the vbComponent. (And then save the workbook)
[VBA]Sub PermanentChange()
Dim oneControl As Object

With ThisWorkbook.VBProject.vbComponents("UserForm1").Designer
For Each oneControl In .Controls
If TypeName(oneControl) = "TextBox" Then
With oneControl
.TabStop = True
.TabKeyBehavior = False
.MultiLine = False
.BackColor = RGB(255, 127, 127)
End With
End If
Next oneControl
End With
End Sub[/VBA]
To change it once, just refer to the userform, change the properties and .Show the userform before that instance of the uf passes out of scope.
Note the change in how the userform is dimesioned

[VBA]Sub chg_uf_tb_prop()

Dim uf As UserForm1: Rem <<<
Dim ctrl As Control

Set uf = New UserForm1: Rem <<<

For Each ctrl In uf.Controls
If TypeName(ctrl) = "TextBox" Then
With ctrl
.TabStop = True
.TabKeyBehavior = False
.MultiLine = False
End With
End If
Next ctrl

uf.Show: Rem <<<<

End Sub
[/VBA]

[mancubus]

thanks mike.