Previous / Next Record Buttons Code Question

[oskar44]

The code below is from a lesson I follow and trying to understand how it works I can’t see where the “Controls” and “Control” words belongs to. The Me.Control1 belongs to a text box and there are 6 similar text boxes numbered Control1 to 6. Perhaps the “Controls” and “Control” words are part of the Excel VBA internal commands?

Please Login or Register  to view this content.

[Leith Ross]

Hell oskar44,

Controls is a collection object belonging to the UserForm. Any object you add to the UserForm at design time is added to this collection.

Collection objects can be referenced 2 ways: by creation Index number or by a key. A key is unique string that identifies the item in the collection.
For example, let's say our UserForm has a label, text box, and command button and the order of creation was command button, label, and then the textbox.

The items in the UserForm Controls collection can be referenced by Index: Controls(1) = "CommandButton1", Controls(2) = "Label1", and Controls(3) = "TextBox1"
or by key like this: Controls("CommandButton1"), where the Collection key is the object's name.

Your lesson made a bad choice of renaming "TextBox1" as "Control1". Now, you have no idea what this control is supposed to do on the UserForm when you are looking at the code. Control is not a reserved word and can used as either the name of a variable or object in VBA.

On a personal note, you would do better asking us for help This lesson is was not written by a VBA expert.

[oskar44]

Thank you Leith for your time and good explanation