I am creating controls with VBA and cannot set the font by referring to them as a control.
I name them and can modify the font if I refer to them by name Me.(control variable name).Font
.
I need to know the proper Syntax to make that work.
I think I've tried every combination but none has been successful.
For CountRecords = 0 To rs.RecordCount - 1
tempLeft = 6
For countfields = 0 To rs.Fields.Count - 1
tempname = rs.Fields.Item(countfields).Name & CountRecords
frmtst.Controls.Add "forms.textbox.1", tempname
Set ctl = Me.frmtst(tempname)
Me.test.Font = 14 'set the font on a test textbox
Me.Controls(tempname).Value.Font = 14 '****Trouble line ********
ctl.Width = ((columnwidth(countfields) ^ 0.8) * 10) + 25
ctl.Height = 24
ctl.Left = tempLeft 'templeft + columnwidth(CountFields) + 18
tempLeft = tempLeft + ctl.Width + 3
ctl.Top = 20 * CountRecords + 3
ctl = rs.Fields.Item(countfields).Value
If rs.Fields.Item(countfields).Type = 6 Then
ctl = Format(ctl, "$#,##0.00")
end if
Next countfields
rs.MoveNext
Next CountRecords
How to reference controls properly
You can reference controls
Me.Test
), Controls
collection orSet ctl = Me.Controls(tempname)
)Note that the particle Me
always refers to the current UserForm instance (not to its name) and can/should be used within the userform code module.
For instance you can refer to Me.Controls
or a given item within the controls' collection, e.g. Me.Controls(tempname)
.
- It's bad use, however to refer to the default instance of a UserForm (e.g. frmtst
) from that form's code behind.
Furthermore it's impossible to refer to both within the same statement like Me.frmtst(tempname)
.
Suggested reading for a deeper understanding: UserForm1.Show?
1a) Missing .Size
property in test assignment to .Font
' Direct referencing a control of the current Userform instance - missing .Size property
Me.Test.Font.Size = 14 ' instead of: Me.test.Font = 14
1b) Bad insertion of .Value
property before failing .Font property
' Indirect referencing a control of the current Userform instance - bad .Value prop, .Font prop without .Size
Me.Controls(tempname).Font.Size = 14 ' instead of: Me.Controls(tempname).Value.Font = 14
2) Object reference
If, however you prefer to set an object to the memory, the code line as shown in case [1b] is redundant and you should decide to stick to the chosen method.
Dim ctl As MsForms.TextBox ' declare MSForms object (e.g. TextBox, or Object)
Set ctl = Me.Controls(tempname) ' instead of: Set ctl = Me.frmtst(tempname)
ctl.Font.Size = 14 ' instead of: .Font = 14
Further remarks
Always use Option Explicit
to check the correct and complete declaration of all variables (would have been fine to include some in your code).
BTW did you actually calculate the control's width via an exponent, i.e. ^ 0.8) * 10) + 25
?