Run time error 2147319765(8002802b)

[Bruce_NC]

I created a macro to copy comboboxes - thanks to Dave Peterson and Tom Ogilvy
posts. The macro is as follows:

Sub Employees()
Dim OLEObj As OLEObject
Dim myOLEObj As MSForms.ComboBox
Dim myRng As Range
Dim myCell As Range
Dim myList As Range

With Worksheets("Employees")
Set myList = .Range("a2:b105")
End With

With Worksheets("Test")
Set myRng = .Range("L7:L525")
For Each myCell In myRng.Cells
With myCell
.NumberFormat = ";;;" 'hide the value in the cell
Set OLEObj = .Parent.OLEObjects.Add _
(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, _
Top:=.Top, _
Left:=.Left, _
Width:=.Width, _
Height:=.Height)

End With
Set myOLEObj = OLEObj.Object
With myOLEObj
.ColumnWidths = "0;100"
.ColumnCount = 2
.ListRows = 15
End With

With OLEObj
.LinkedCell = myCell.Address(external:=True)
.ListFillRange = myList.Address(external:=True)

End With

Next myCell
End With
End Sub

I have 2 almost identical macros, one for employees and one for
classifications. The first time I ran them it worked fine. I'm trying to
make 5 "pairs" of columns. After changing the range to the next column I get
the run time error 2147319765(8002802b) Automation Error Element not found.

Does anybody have any ideas?
Thanks in advance for your help.
Bruce

 

[Jim Thomlinson]

Which line does the error occure on???

 

[Bruce_NC]

It highlights the
Set myRng = .Range("L7:L525")
Bruce

 

[Jim Thomlinson]

Do you have a worksheet called Test? If you change the Tab name then the
macro will crash... you can get around that by using the code name of the
sheet.

 

[Dave Peterson]

You're doing this to add 500 comboboxes to the worksheet twice--a total of over
1000 of these controls?

That's a lot. And with lots of controls from the control toolbox toolbar, bad
things can happen.

Can you replace them with dropdowns from the Forms toolbar (with only one column
in the dropdown)?

Or could you use Data|Validation and a formula to return the other value (single
column again)?

Or maybe just a single combobox, but use a worksheet_selectionchange event to
move and modify it for each cell.

If you want to try that last approach, create two comboboxes on the worksheet.
Name them:
CBXClassifications
CBXEmployees

Set them up exactly the way you want--either manually or via code. But don't
set up the linkedcell and don't format the linkedcell with a number format of:
;;;

Then put this behind the worksheet that owns those two comboboxes:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Rng_Classifications As Range
Dim Rng_Employees As Range
Dim UseThisCBX As OLEObject

Set Rng_Classifications = Me.Range("L7:L525")
Set Rng_Employees = Me.Range("M7:M525")

'check to see if they're still there!
Set UseThisCBX = Nothing
On Error Resume Next
Set UseThisCBX = Me.OLEObjects("CBXClassifications")
On Error GoTo 0

If UseThisCBX Is Nothing Then
MsgBox "Design Error Classifications--contact Bruce_NC at xxxx"
Exit Sub
End If

Set UseThisCBX = Nothing
On Error Resume Next
Set UseThisCBX = Me.OLEObjects("CBXEmployees")
On Error GoTo 0

If UseThisCBX Is Nothing Then
MsgBox "Design Error Employees--contact Bruce_NC at xxxx"
Exit Sub
End If

'hide both comboboxes
Me.OLEObjects("CBXClassifications").Visible = False
Me.OLEObjects("CBXEmployees").Visible = False

If Target.Cells.Count > 1 Then
'do nothing, just get out
Exit Sub
End If

Set UseThisCBX = Nothing
If Not (Intersect(Target, Rng_Classifications) Is Nothing) Then
Set UseThisCBX = Me.OLEObjects("CBXClassifications")
ElseIf Not (Intersect(Target, Rng_Employees) Is Nothing) Then
Set UseThisCBX = Me.OLEObjects("CBXEmployees")
End If

If UseThisCBX Is Nothing Then
'not in either range, just get out
Exit Sub
End If

With UseThisCBX
.Visible = True
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width
.Height = Target.Height
'don't format this cell as ;;;
.LinkedCell = Target.Address(external:=True)
End With

End Sub

 

[Bruce_NC]

Thanks Dave, I'll study your reply in detail tomorrow.
Brief background, I'm setting up a form for the Accting Dept to give to
project managers. The PM are to select the contract/line items and then
select the classification and employee that can charge against that line
item, more than one employee allowed per line item, hence all the combo
boxes. I use the linked cells to capture the data and display it on anaother
sheet - vlookup function. The other sheet is the "final" form that gets
submitted to accting. I have a macro that copies that sheet to another
workbook and saves it as the project name.

I appreciate all your help.
Bruce

 

[Bruce_NC]

Thanks Dave,
Your 2 combobox solution works slick and solves my immediate problem. The
error code - we'll let someone else figure that out.
Have a great day.
Bruce

 

[Dave Peterson]

ps. You may want to add code to your macro that creates each of those
comboboxes if they aren't found--that would be better than getting irritating