This example is used in specifying options that might not be included in a database of available housing and its attendant amenities.
It builds on the previous example, with some differences:
Private Sub cboNotIncl_Change() Dim n As Long Dim notincl_array(1 To 9) As String n = myTarget.Row If n >= 3 And n < 10000 Then If myTarget.Address = "$G$" & n Then 'set up the array elements for the not included services notincl_array(1) = "Central Air" notincl_array(2) = "Hot Water" notincl_array(3) = "Heater Rental" notincl_array(4) = "Utilities" notincl_array(5) = "Parking" notincl_array(6) = "Internet" notincl_array(7) = "Hydro" notincl_array(8) = "Hydro/Hot Water/Heater Rental" notincl_array(9) = "Hydro and Utilities" cboNotIncl.List = notincl_array() Else Exit Sub End If With cboNotIncl 'make sure the combo box moves to the target cell .Left = myTarget.Left .Top = myTarget.Top 'adjust the size of the cell to fit the combo box myTarget.ColumnWidth = .Width * 0.18 'make it look nice by editing some of the font attributes .Font.Size = 11 .Font.Bold = False 'populate the cell with the user choice, with a backup guarantee that it's in column G If myTarget.Address = "$G$" & n Then .LinkedCell = myTarget.Address 'prevent an error where a numerical value is formatted as text myTarget.EntireColumn.TextToColumns End If End With End If 'ensure that the active cell is only between rows 3 and 1000 End Sub
The above macro is initiated every time a cell is activated with the SelectionChange event in the worksheet module:
Public myTarget As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set myTarget = Target 'switch for Not Included If Target.Column = 7 And Target.Cells.Count = 1 Then Application.Run "Module1.cboNotIncl_Change" End If End Sub