vba refer to control as variable syntax

Question

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

T.M. · Accepted Answer · 2019-01-27 20:58:03Z

How to reference controls properly

You can reference controls

1a) directly by Name and using IntelliSense (e.g. Me.Test),
1b) indirectly via the Controls collection or
2) implicitly via setting an object directly or indirectly (e.g. Set 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?

Joost · Accepted Answer · 2019-01-27 13:27:28Z

1

Using Me in a form procedure is a replacement for the form name, which seems to be frmtst above. So Me.frmtst(tempname) is a double refer. You can refer to the control tempname with Me.tempname. Set the font with Me.tempname.Font.Name = "Lucida Console" and set font size with Me.tempname.Font.Size = 10

edited Jan 27, 2019 at 13:27

answered Jan 27, 2019 at 13:15

Joost

1028 bronze badges

1 Comment

user2242621 Over a year ago

Awesome info, & problem solved. It was the ".size" I was laking. Sincerely appreciate all input. Yes, I used an exponent for sizing. Previously I establish an array of the largest character length for each field of all records. when I try to use a proportionate formula I end up with too much white space on the long ones. So by calculating exponentially it works out just right.

Collectives™ on Stack Overflow

vba refer to control as variable syntax

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related