CODEWITHSUNDEEP
vbaexcel
Michał Plich
Michał Plich

Reputation: 175

How to check if Excel sheet contains activeX control?

I try to create a macro, placed in Workbook_Open() procedure, which adds items for combo boxes, named CBTask for each sheet in workbook, (which has combo box named CBTask in it). Unfortunatelly following code generates Compile error: Method or data member not found"

I believe it is because not every sheet has CBTask in it (even on error resume next does not help). Is there any way to check if sheet contains combo box, so I could do the checking before trying clear it, or adding new items?

Private Sub Workbook_Open()
Dim ws As Worksheet
Dim i As Integer
Dim strTaskName As String

On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
    ws.CBTask.Clear
    strTaskName = taskName(1)
    Do
        ws.CBTask.AddItem strTaskName
        strTaskName = taskName(i)
        i = i + 1
    Loop While taskName <> "LastOne"
Next ws
On Error GoTo 0
End Sub

(as you can see, this code also uses additional function "taskName(intSubtaskValue as integer)" to convert integer to string (e.g. taksName(1) = "Task01", taskName(2) = "Task02...)

Upvotes: 1

Views: 3149

Answers (2)

eirikdaude
eirikdaude

Reputation: 3255

To expand on Rory's answer, in addition to fully qualifying your objects, you can check if an ActiveX-control of a given name exists on a worksheet by using this function:

Function obj_exists(obj_name As String, on_worksheet As Worksheet) As Boolean
  On Error GoTo errHandler
  Debug.Print on_worksheet.OLEObjects(obj_name).Name
  obj_exists = True
  On Error GoTo 0
  Exit Function
errHandler:
  obj_exists = False
  On Error GoTo 0
End Function

Upvotes: 2

Rory
Rory

Reputation: 34075

The Worksheet class doesn't have a member named CBTask which is why that code fails. I suggest you use the OLEObjects property instead:

ws.OLEObjects("CBTask").Object.Clear

Upvotes: 2

Related Questions