ActiveX controls have more properties and events than Form controls

 
    Private Sub CalculateButton_Click()
        hourly = Range("B3")
        Range("B4") = hourly * 40
        Range("B5") = Range("B4") * 52
    End Sub
    

 
    Private Sub CalculateButton_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        If KeyCode = vbKeyEscape Then
            MsgBox "you hit escape"
        End If
    End Sub
    

Constant	Value	Description
fmShiftMask	1	Shift key pressed
fmCtrlMask	2	Ctrl key pressed
fmAltMask	4	Alt key pressed

 
    Private Sub CalculateButton_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
        ...
    End Sub
    

when Shift arg is 5 it means the Shift and Alt keys were pressed together

        Private Sub CalculateButton_MouseMove(ByVal Button As Integer, ByVal Shift As Integer,
        ByVal X As Single, ByVal Y As Single) ' displays message in status bar when mouse
        is over control Application.StatusBar = "Double click to calculate" End Sub
    

 
    Private Sub CalculateButton_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        ' xlMouseButton constants
        ' xlPrimaryButton
        ' xl SecondaryButtom
        ' xlMiddle Button
        
        ' Shift arg is a mask
        ' 0 no keys
        ' 1 Shift key
        ' 2 is CTRL key
        ' 4 is ALT key
        ' same for MouseUp eveny
    End Sub

    Private Sub CalculateButton_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        ' occurs when button is released over the control
        ' otherwise same as MouseDown
    End Sub
    

 
    Private Sub CalculateButton_GotFocus()
    End Sub

    Private Sub CalculateButton_LostFocus()
    End Sub
    

 
    Private Sub CommandButton1_Click()
        If Me.CheckBox1 = True Then
            MsgBox "we love birds too"
        Else
            MsgBox "you should like birds"
        End If
    End Sub
    

 
    Private Sub CheckBox1_Click()
        If Me.CheckBox1 = True Then
            MsgBox "we love birds too"
        Else
            MsgBox "you should like birds"
        End If
    End Sub
    

a cell and a checkbox are linked using checkbox's LinkedCell property
when checkbox is checked or unchecked the new value appears in the cell, the click event is fired
changes to the cell (true/False) are reflected by the checkbox changing without the click event being fired

To group radio buttons set each radio button's GroupName property to the same value

when multiple controls are selected indesign mode the Format tab appears

 
    Private Sub CommandButton1_Click()
        If Me.AppleButton = True And Me.WaterButton = True Then
            MsgBox "That is really healthy"
        ElseIf Me.DonutsButton = True And Me.CoffeeButton = True Then
            MsgBox "not really breakfast"
        ElseIf Me.WingsButton = True And Me.BeerButton = True Then
            MsgBox "Tasty!"
        End If
    End Sub
    

 
    Private Sub CommandButton1_Click()
        If Me.AppleButton = True Or Me.DonutsButton = True Or Me.WingsButton = True Then
            ' ...
        Else
            MsgBox "please select a food"
            Exit Sub
        End If
        If Me.CoffeeButton = True Or Me.BeerButton = True Or Me.WaterButton = True Then
            ' ...
        Else
            MsgBox "please select a drink"
            Exit Sub
        End If
    End Sub
    

 
    Sub analyzeRadioButtons()
        If (Me.AppleButton = True Or Me.DonutsButton = True Or Me.WingsButton = True) And (Me.CoffeeButton = True Or Me.BeerButton = True Or Me.WaterButton = True) Then
            Me.CommandButton1.Visible = True
        Else
            Me.CommandButton1.Visible = False
        End If
    End Sub
    ' can't assign single event handler for multiple controls
    ' each radio button has its own handler which calls analyzeRadioButtons
    Private Sub DonutsButton_Click()
        analyzeRadioButtons
    End Sub
    ...
        

 
    ' cell's initial value is zero
    Private Sub SpinButton1_SpinDown()
        Range("A1") = Range("A1") - 1
    End Sub

    Private Sub SpinButton1_SpinUp()
        Range("A1") = Range("A1") + 1
    End Sub
    

 
    Private Sub SpinButton2_SpinDown()
        On Error Resume Next
        If Me.numbersTextBox = "" Then Exit Sub
        Me.numbersTextBox = Me.numbersTextBox - 1
    End Sub

    Private Sub SpinButton2_SpinUp()
        On Error Resume Next
        If Me.numbersTextBox = "" Then Exit Sub
        Me.numbersTextBox = Me.numbersTextBox + 1
    End Sub
            

 
    Private Sub SpinButton3_SpinDown()
        Me.TextBox1.Width = Me.TextBox1.Width - 10
    End Sub

    Private Sub SpinButton3_SpinUp()
        Me.TextBox1.Width = Me.TextBox1.Width + 10
    End Sub
            

use CDate function to cast string to date

 
    Private Sub SpinButton1_SpinDown()
        myDate = CDate(Me.dateTextBox) - 1
        Me.dateTextBox.Text = myDate
    End Sub

    Private Sub SpinButton1_SpinUp()
        myDate = CDate(Me.dateTextBox) + 1
        Me.dateTextBox.Text = myDate
    End Sub        
    

ComboBox's ListFillRange is the property where items are added as a named list or a range

OFFSET function

 
    OFFSET(reference, rows, columns, [height], [width])
    

• Reference - Required. The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
• Rows - Required. The number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
• Columns - Required. The number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Columns can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
• Height - Optional. The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
• Width - Optional. The width, in number of columns, that you want the returned reference to be. Width must be a positive number.

ALT-IND brings up the Name Manager dialog
add a new range entering the formula below and set the ComboBox's ListFillRange property to the new range

 
        =OFFSET(sheet1!$A$1,1,0,COUNTA(sheet1!$A:$A)-1,1)
    

the formula doesn't always recalculate when changes are made
force the calculation using the code below

 
    Private Sub ComboBox1_GotFocus()
        Me.ComboBox1.ListFillRange = "<name of range in Name Manager>"
    End Sub
    

an alternative is

 
    Private Sub ComboBox1_GotFocus()
        Me.ComboBox1.ListFillRange = Me.ComboBox1.ListFillRange
    End Sub
    

change the width arg in the OFFSET method to show 2 columns
change the ComboBox's ColumnCount property to 2

 
        =OFFSET(sheet1!$A$1,1,0,COUNTA(sheet1!$A:$A)-1,2)
    

the list will contain two columns but the selection will only show the first column when picked

 
    Private Sub CommandButton1_Click()
        lastRow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        selectedColor = Range("K4")
        Me.ComboBox2.Clear
        For X = 2 To lastRow 
            If (Cells(X, 2) = selectedColor Then
                Me.ComboBox2.AddItem Cells(X, 1)
            End If
        Next X
    End Sub
    

set ComboBox's ColumnCount to 2

 
    Private Sub CommandButton1_Click()
        lastRow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        selectedColor = Range("K4")
        Me.ComboBox2.Clear
        For X = 2 To lastRow 
            If (Cells(X, 2) = selectedColor Then
                Me.ComboBox2.AddItem Cells(X, 1)
                ' list uses zero-based index
                Me.ComboBox2.List(Me.ComboBox2.ListCount - 1, 1) = Cells(X, 3)
            End If
        Next X
    End Sub    

to enable multiselect set ListBox's property MultiSelect property

 
    Private Sub CommandButton1_Click()
        For x = 0 To Me.ListBox1.ListCount - 1
            If Me.ListBox1.Selected(x) Then
                MsgBox "you found a selection : " & Me.ListBox1.List(x)
            End If
        Next x
    End Sub
    

 
    Private Sub listBox_Click()
        ' ListIndex is selected row and 1 represents the second column in the list
        Range("H1") = Me.listbox.value & "'s favorite color is " & Me.listBox.List(Me.listbox.List(Me.listbox.ListIndex, 1);
    End Sub
    

ListBoxes can have up to ten columns

 
    Private Sub CommandButton1_Click()
        lastRow = ThisWorkbook.Sheets("listBox").Cells(Rows.Count, 1).End(xlUp).Row

        selectedColor = Me.colorComboBox
        selectedGender = Me.genderComboBox
    
        Me.listBox.Clear
        For x = 2 To lastRow
            If Cells(x, 2) = selectedColor And Cells(x, 4) = selectedGender Then
                Me.listBox.AddItem Cells(x, 1)
                ' list uses zero-based index
                Me.listBox.List(Me.listBox.ListCount - 1, 1) = Cells(x, 2)
                Me.listBox.List(Me.listBox.ListCount - 1, 2) = Cells(x, 3)
                Me.listBox.List(Me.listBox.ListCount - 1, 3) = Cells(x, 4)
            End If
        Next x
    End Sub
    

to make a column invisible set its width to zero pt in the ListBox's property sheet

 
    ColumnWidths 60pt;0pt;100pt;60pt
    

the lecture uses a click event but in this version of Excel (Office 16) the event is not available

 
    Private Sub Image1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
        ThisWorkbook.FollowHyperlink "http://www.subdevo.com/"
    End Sub
    

toggle button behaves similar to a checkbox

 
    Private Sub ToggleButton1_Click()
       Me.Image1.Visible = Me.ToggleButton1
    End Sub