Hi guys. Seems I didn't fully understand the question. (thanks jonmo1) Sorry about that.
First off I will say that I generally avoid using controls from the Controls toolbox if I have to use many on a worksheet. They are really designed for userforms I suppose. More features, more bugs. What happens is that things seem to work for a time, then all sorts of things go haywire as the project development progresses - to the state where the workbook becomes corrupted and refuses to open - so be warned. However, there seems to be no other way here - and at least the controls are made anew each time.
Setting this up was no exception to the above. I kept getting a message "Object doesn't support this property or method". Searching the web, it seems this is a common occurrence. Trapping the errror with "On Error Resume Next" strangely seems to have resolved the problem.
Here is the basic code then. I would be interested to know how things go after it has been used for a time. :-
Code:
'=============================================================================
'- ADD SEVERAL COMBOBOXES TO A WORKSHEET TO SIMULATE DATA VALIDATION
'- NB. used 'On Error Resume Next' to stop message
'- "Object doesn't support this property or method"
'- but this does not seem to affect anything.
'- Brian Baulsom July 2007 using Excel 2000
'==============================================================================
Sub ADD_COMBOBOXES()
Dim ToRow As Long
Dim MyCell As Range
'--------------------------------------------------------------------------
'- worksheet
With ActiveSheet.Cells
.Font.Name = "Arial"
.Font.Size = 8
.Columns(1).ColumnWidth = 20
.Rows.RowHeight = 15
End With
'---------------------------------------------------------------------------
'- TO TRAP UNEXPLAINED ERROR MSG
On Error Resume Next
'---------------------------------------------------------------------------
'- Add ComboBoxes
For ToRow = 2 To 10
Set MyCell = ActiveSheet.Cells(ToRow, 1)
'- Add Combobox
With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1")
.Name = "CB" & CStr(ToRow)
.ListFillRange = "$J$1:$J$10"
.Placement = xlMoveAndSize
.LinkedCell = MyCell.Address
.Left = MyCell.Left
.Top = MyCell.Top
.Width = MyCell.Width
.Height = MyCell.Height
.MatchRequired = True
.ListRows = 10
End With
Next
'---------------------------------------------------------------------------
MsgBox ("Done")
End Sub
'--------------------------------------------------------------------------------