How to Check Each Control of a Group in Excel VBA

Save

VBA, or Visual Basic for Applications, is a powerful macro programming language that comes with the spreadsheet program Microsoft Excel. You can check each control of a group of controls in a program, or macro, you write to save you from having to specify a particular control to read its properties. Excel makes control grouping possible through its "Frame" control. The "Controls" property of this control provides access to the controls inside the frame. Besides using the "Controls" property to read values of a "Frames" control, you can set control values also. For example, you can hide or display controls within a frame with the "Visible" property.

  • Click the "Developer" tab's "Visual Basic" button to enter the VBA programming environment, then click the "Insert" menu's "Userform" item to create a new form on which to place controls. If the "Developer" tab does not appear among your menu choices in Excel, you may need to turn on its display in Excel's options.

  • Click the "Button" control on the tool box, then drag on the userform to create a button. Click the "OptionButton" control from the tool box, then drag on the userform to create an option button. Right click this button and click "Properties" to display a list of attributes for the control.

  • Click in the "Enabled" row of the "Properties" window, then type "False" to indicate that the option button is unable to process input. Note that the command button, which you created in Step 2, is enabled by default.

  • Click the "Frame" control on the tool box, then drag on the userform. Click the button control you made in Step 1, then press "Control" and "X" simultaneously to cut this control to the clipboard.

  • Click the "Frame" control, then press "Control" and "V" simultaneously to paste the button inside the frame. Repeat the cut and paste with the option button to paste it into the frame as well. By placing both controls into the frame, you've made one group that contains both controls. You'll now write code that checks a property of each control.

  • Double click the userform to display the programming window, then paste the following program into the window. This program iterates through each control in the frame, reading the "Enabled" property you set in step 3, and printing the value of that property.

    Private Sub UserForm_Click()

    Dim ct As Control

    Dim s As String

    For Each ct In Frame1.Controls

    If ct.Enabled = True Then

    s = ct.Name & " is enabled."

    Else

    s = ct.Name & " is disabled."

    End If

    MsgBox s

    Next

    End Sub

  • Click the "Run" menu's "Run" command to display your userform, then click anywhere on the form that doesn't have a control. Your program will run, displaying message about the "Enabled" property described in Step 3.

References

  • "Excel 2010 Bible"; John Walkenbach; 2010
  • "Mastering VBA for Office 2010"; Richard Mansfield; 2010
Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!