Referencing UserForm Controls


When working with controls on a UserForm, the VBA code is usually contained in the code window for the UserForm. You can also refer to UserForm controls from a general VBA module. To do so, you need to qualify the reference to the control by specifying the UserForm name . For example, consider the following procedure, which is located in a VBA module. It simply displays the UserForm named UserForm1 .

 Sub GetData()     UserForm1.Show End Sub 

Assume that UserForm1 contains a text box (named TextBox1 ), and you want to provide a default value for the text box. You could modify the procedure as follows :

 Sub GetData()     UserForm1.TextBox1.Value = "John Doe"     UserForm1.Show End Sub 
image from book
Understanding the Controls Collection

The controls on a UserForm make up a collection. For example, the following statement displays the number of controls on UserForm1 :

 MsgBox UserForm1.Controls.Count 

VBA does not maintain a collection of each control type. For example, there is no collection of CommandButton controls. However, you can determine the type of control by using the TypeName function. The following procedure uses a For Each structure to loop through the Controls collection and then displays the number of CommandButton controls on UserForm1 :

 Sub CountButtons()     Dim cbCount As Integer     Dim ctl as Control     cbCount = 0     For Each ctl In UserForm1.Controls         If TypeName(ctl) = "CommandButton" Then _           cbCount = cbCount + 1     Next ctl     MsgBox cbCount End Sub 
image from book
 

Another way to set the default value is to take advantage of the UserForm's Initialize event. You can write code in the UserForm_Initialize procedure, which is located in the code module for the UserForm. Here's an example:

 Private Sub UserForm_Initialize()     TextBox1.Value = "John Doe" End Sub 

Notice that when the control is referenced in the code module for the UserForm, you don't need to qualify the references with the UserForm name. However, qualifying references to controls does have an advantage: You will then be able to take advantage of the Auto List Members feature, which lets you choose the control names from a drop-down list.

Caution  

Rather than use the actual name of the UserForm, it is preferable to use Me . Then, if you change the name of the UserForm, you won't need to replace the references in your code.




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net