How to create drop-down list using VBA in Excel '07

[traxmax]

Can someone please help me to create a drop-down list using VBA in Excel 2007?

I want to have a drop-down list of values (7 in total, 2 words each).
Once the particular value is selected the macro writes a value category (3 categories, 2 words each) into a specific cell as well as the value short description (15 words max) into an other cell (same page).

I don't want those drop-down values to be in any worksheet or pulling from any other source.
Please help. Thank you!

 

[Worf]

Hello and welcome to the Board

See if this is useful:

' standard module
Sub Create_DropDown()
Dim Ctrl As OLEObject
With ActiveSheet
    Set Ctrl = .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=160, Top:=80, Width:=90, Height:=20)
End With
With Ctrl.Object
    .AddItem "Jackie Stewart"
    .AddItem "Gerard Ducarouge"
    .AddItem "Jochen Rindt"
    .AddItem "Ronnie Peterson"
    .AddItem "James Hunt"
    .AddItem "Colin Chapman"
    .AddItem "Niki Lauda"
End With
Ctrl.Name = "box1"
End Sub

' at sheet module
Private Sub box1_Change()


Dim cats, ci, desc
cats = Array("driver", "designer", "owner") ' categories
ci = Array(0, 1, 0, 0, 0, 2, 0)
desc = Array("desc0", "desc1", "desc2", "desc3", "desc4", "desc5", "desc6") 'descriptions
Me.Cells(10, 4) = cats(ci(Me.box1.ListIndex))
Me.Cells(11, 4) = desc(Me.box1.ListIndex)


End Sub

 

[traxmax]

Hello and welcome to the Board

See if this is useful:

' standard module
Sub Create_DropDown()
Dim Ctrl As OLEObject
With ActiveSheet
    Set Ctrl = .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
    DisplayAsIcon:=False, Left:=160, Top:=80, Width:=90, Height:=20)
End With
With Ctrl.Object
    .AddItem "Jackie Stewart"
    .AddItem "Gerard Ducarouge"
    .AddItem "Jochen Rindt"
    .AddItem "Ronnie Peterson"
    .AddItem "James Hunt"
    .AddItem "Colin Chapman"
    .AddItem "Niki Lauda"
End With
Ctrl.Name = "box1"
End Sub

' at sheet module
Private Sub box1_Change()


Dim cats, ci, desc
cats = Array("driver", "designer", "owner") ' categories
ci = Array(0, 1, 0, 0, 0, 2, 0)
desc = Array("desc0", "desc1", "desc2", "desc3", "desc4", "desc5", "desc6") 'descriptions
Me.Cells(10, 4) = cats(ci(Me.box1.ListIndex))
Me.Cells(11, 4) = desc(Me.box1.ListIndex)


End Sub

Thank you so much, Worf for your help and insight into my question.The code is nice as well as your sense of humor - five drivers who seems to motor the designer days and nights ... lol
I very much appreciate and enjoy both aspects of your quick response Thank you again!!

 

[Worf]

You are welcome and thanks for the feedback