Create combo box with VBA in spreadsheet not form ?

[bdsii]

I have not been able to figure this one out and am hoping someone here can move me in the correct direction.

I have a spreadsheet where data will be pasted daily and each day the number of rows of data will differ. The data covers Columns A through D. I am then processing that data with VBA with no problem.

What I cannot figure out is how through VBA only to create a combo box in Column E for each row of data present that day. I have the code to populate the combo box, I think, I just do not know how to create the combo box through VBA and specify that each row with data will get a combo box. The selected value from the combo box could be different for each row which is why I need a specific combo box that is tied to each row so when I complete the process, the value selected would function just like I typed in the value selected into column E. Does that makes sense ?

Once I have the combo boxes established for each row, do I need to refer to them in the final code by the combo box name or just the same as I would any other cell location ?

thanks !

[p45cal]

1. Which version of Excel?
2. An activex combobox, or a forms controls combo?

[bdsii]

Sorry, forgot to include that info. This will be Excel 2007 and I am not sure of the second question. I know I would like the combo box value selected to be used just as if that data were populated in any other cell in the spreadsheet. I want the box to be in the actual spreadsheet cell so does that exclude the option for a forms controls combo ?

I hope I have provided enough info.......let me know if I have not.

Thanks for any help you can provide :-)

[p45cal]

I'm not sure if you're not talking about Data Validation dropdowns?
Anyway, here's some code to add an activex combobox to column E where there is data in any cell in the same row in columns A:
[vba]Sub blah()
With ActiveSheet
Set theRng = Intersect(.Columns("E"), Union(.Columns("A").SpecialCells(xlCellTypeConstants, 23), .Columns("A").SpecialCells(xlCellTypeFormulas, 23)).EntireRow)
'theRng.Select
For Each cll In theRng
L = cll.Left: T = cll.Top: W = cll.Width: H = cll.Height
Set cmb = .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, Left:=L, Top:=T, Width:=W, Height:=H)
'cmb.Name = "cmbo" & cll.Address(0, 0) 'optional, to name the object according to the top left cell
Next cll
End With
End Sub
[/vba]
I'll see if I can do the same with Forms comboboxes…

[p45cal]

I'll see if I can do the same with Forms comboboxes…

wel:
[vba]Sub blah2()
With ActiveSheet
Set theRng = Intersect(.Columns("E"), Union(.Columns("A").SpecialCells(xlCellTypeConstants, 23), .Columns("A").SpecialCells(xlCellTypeFormulas, 23)).EntireRow)
'theRng.Select
For Each cll In theRng
L = cll.Left: T = cll.Top: W = cll.Width: H = cll.Height
Set cmb = .DropDowns.Add(L, T, W, H)
cmb.Name = "cmbo" & cll.Address(0, 0) 'optional, to name the object according to the top left cell
Next cll
End With
End Sub
[/vba] and for good measure, if it's data validation dropdows:
[vba]Sub blah3()
With ActiveSheet
Set theRng = Intersect(.Columns("E"), Union(.Columns("A").SpecialCells(xlCellTypeConstants, 23), .Columns("A").SpecialCells(xlCellTypeFormulas, 23)).EntireRow)
'theRng.Select
With theRng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$N$13:$N$21"
End With
End With
End Sub
[/vba]where N13:N21 contained the values to use in the data validation dropdown list.

[bdsii]

Woo-Hoo, thanks p45cal, I appreciate it. Gonna test those this morning ! :-)

Thanks so much !

bdsii