Drop Down Box example

[mgfacioli]

I created this macro to include a Drop Down Box in a worksheet, although I think it is a bit old-fashioned. Any suggestions to make it better?

Option Explicit
Option Private Module
'---------------------------------------------------------------------------------------
' Module    : M_DropDownBox
' Author    : facioli
' Date      : 15/05/2015
' Purpose   : create a Drop Down Box with months
'---------------------------------------------------------------------------------------

Sub create_DropDownBox()
    Dim wkbLisBx As Workbook
    Dim wksListb As Worksheet
    Dim rngMeses As Range
    Dim aMeses As Variant
    
    Set wkbLisBx = ThisWorkbook
    Set wksListb = wkbLisBx.Worksheets("Listbox")
    aMeses = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
    
    InitializeDDBox wksListb, aMeses, Add_DDBox(wksListb, 100, 20, 80, 20), "DDB_01"
    InitializeDDBox wksListb, aMeses, Add_DDBox(wksListb, 200, 20, 80, 20), "DDB_02"
    
    'deleteDDBox wksListb
    Set wksListb = Nothing
    Set wkbLisBx = Nothing
End Sub


Function Add_DDBox(ByRef wksListb As Worksheet, ByVal iLeft As Integer, _
    ByVal iTop As Integer, ByVal iLarg As Integer, ByVal iAlt As Integer) As DropDown
'---------------------------------------------------------------------------------------
' Function : Add_DDBox
' Author    : facioli
' Date      : 15-05-2015
' Purpose   : create a Drop Down object
'---------------------------------------------------------------------------------------
'
    With wksListb
        .Select
        Set Add_DDBox = .DropDowns.Add(iLeft, iTop, iLarg, iAlt)
    End With
        
End Function


Sub InitializeDDBox(ByRef wksListb As Worksheet, ByRef aMeses As Variant, ByRef DDB1 As DropDown, ByVal strNomeDDB As String)
'---------------------------------------------------------------------------------------
' Procedure : InitializeDDBox
' Author    : facioli
' Date      : 15-05-2015
' Purpose   : initialize the object with months and assigns a macro to execute every time a item is selected
'---------------------------------------------------------------------------------------
'

    With DDB1
        .RemoveAllItems
        .List = aMeses
        .ListIndex = 1
        .OnAction = "getval_DDBox"
        .Name = strNomeDDB
        '.Locked = True
    End With
End Sub


Sub getval_DDBox()
'---------------------------------------------------------------------------------------
' Procedure : getval_DDBox
' Author    : facioli
' Date      : 15-05-2015
' Purpose   : the macro to be executed
'---------------------------------------------------------------------------------------
'
    Dim wkbLisBx As Workbook
    Dim wksListb As Worksheet
    Dim DDBox1 As DropDown
    Dim iMesAnterior As Integer, iMesAtual As Integer
    Dim iMes As Integer
    
    Set wkbLisBx = ThisWorkbook
    Set wksListb = wkbLisBx.Worksheets("Listbox")
    Set DDBox1 = wksListb.DropDowns(Application.Caller)
    
    iMes = DDBox1.ListIndex

End Sub


Private Sub deleteDDBox(ByRef wksListb As Worksheet)
'---------------------------------------------------------------------------------------
' Procedure : deleteDDBox
' Author    : facioli
' Date      : 25-01-2010
' Purpose   : to delete an especific Drop Down
'---------------------------------------------------------------------------------------
'
    Dim DDBox1 As DropDown
    
    Set DDBox1 = wksListb.DropDowns(2)
    
    DDBox1.Delete
End Sub


Private Sub delete_all_DDBox()
'---------------------------------------------------------------------------------------
' Procedure : deleteListobox
' Author    : facioli
' Date      : 25-01-2010
' Purpose   : to delete all Drop Downs in target worksheet
'---------------------------------------------------------------------------------------
'
    Dim wkbLisBx As Workbook
    Dim wksListb As Worksheet
    Dim ctControls As Controls
    Dim DDBox1 As DropDown
    
    Set wkbLisBx = ThisWorkbook
    Set wksListb = wkbLisBx.Worksheets("Listbox")
    
    For Each DDBox1 In wksListb.DropDowns
        Debug.Print DDBox1.Name
        DDBox1.Delete
    Next DDBox1

End Sub