New ComboBox

[tlindeman]

I am sure this is a simple code, however, I do not know how to do it. What I would like is a combo box in cell B4, with the options: BUD, ACTUAL, MGMT

I know how to link the cells , however I would like to do it in VBA, so I can learn and so I do not have to enter the reference values in my sheet.


Thank You
Tony

 

[MickG]

Place a combobox on your sheet, Double click in Design Mode, vb window appears, paste this code at the top of the module.

Private Sub ComboBox1_GotFocus()
ComboBox1.List = Array("BUD", "ACTUAL", "MGMT")
End Sub

 

[tlindeman]

After I enter the VBA, I click the down arrow in the combo box and I am getting a

Run time error 70 Permission Denied

Thank you
Tony

 

[tlindeman]

Found the problem, I had it linked to a cell, It works great now. Thank you for your help.

Tony

 

[MickG]

This is for ActiveX Combobox not Forms Combobox, if that was not the case.
You can try this and just run it from a Sub:-

Sub Cbox()
ActiveSheet.ComboBox1.List = Array("BUD", "ACTUAL", "MGMT")
End sub

 

[tlindeman]

Thank you That works even better.