<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>>
Ozgrid, Experts in Microsoft Excel Spreadsheets

 Excel Training VBA 2 Lesson 22

Finding the Active Control On a UserForm

Information Helpful? Why Not Donate | Free Excel Help
<<Previous Lesson | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX

Workbook Download           
    This is a zipped Excel Workbook to go with this lesson.

Workbook Download
    This is a zipped Excel Workbook to go with this lesson.

Finding the ActiveControl

When coding a UserForm it can often be very handy to be able to identify the current ActiveControl.  By doing this, we can very easily make our code more generic and save ourselves from typing the same Procedure over and over within the Private Module of the UserForm.  To identify the ActiveControl, all we really need to do is use Me.ActiveControl

An example of when this could be used could be in the situation where we have numerous Controls of the same type on a UserForm and we need to run a similar Procedure when the user activates one of the Events of the Control. 

 

Dim iRow As Integer

 
Private Sub DoComboAction()
'Toggle the Bold Property of the chosen name
iRow = ActiveControl.ListIndex + 1
    If iRow > 0 Then
      With Range(ActiveControl.RowSource).Cells(iRow, 1)
       .Font.Bold = Not (.Font.Bold)
      End With
    End If
iRow = 0
End Sub
 
 
Private Sub Level_1_Change()
    DoComboAction
End Sub
 
Private Sub Level_2_Change()
   DoComboAction
End Sub
 
Private Sub Level_3_Change()
   DoComboAction
End Sub
 
Private Sub Level_4_Change()
  DoComboAction
End Sub
 

The above code makes use of the ActiveControl and allow sus to place the code in one Procedure that runs the code for the ComboBox which has had its ChangeEvent fired.  To see how this works and to get a better idea of how it could be done, Download and open the Workbook links at the top of this lesson.

Information Helpful? Why Not Donate | Free Excel Help
<<Previous Lesson | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX