Browse » Home
Tuesday, May 24, 2011
How to Check Each Control of a Group in Excel VBA
1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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 ControlDim s As StringFor Each ct In Frame1.ControlsIf ct.Enabled = True Thens = ct.Name ' is enabled.'Elses = ct.Name ' is disabled.'End IfMsgBox sNextEnd Sub
7. 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.