Active X Objects in worksheet problems

[gjp55]

Hi,

I have problem regarding Active X boxes in a worksheet. I will be working on the code and click the save button which works fine. The problem comes when I close the workbook (even if I select no to saving changes). I get object errors on various listboxes where it can not fine the listbox object. I have the excel spreadsheet saved a various stages of development where the early versions save on but as soon as I go into the code , this can set the errors off when I come to close the file.

I have been reading a few posts about problems with Excel worksheets and active x objects and how they dont work well together.

I have tried somebody else running the file on another pc and they still get the error.

Does anybody have any idea have any idea how to solve this ?
I can provide a sample of the file it this helps ?

Thanks

 

[gjp55]

It stops on a function call

HideLabels lblDCh, lblDCe, lblDCh

and can not find lblDCh (which is a label)

the function is:

Private Sub HideLabels(Label1 As MSForms.Label, Label2 As MSForms.Label, Label3 As MSForms.Label)
Dim WS As Worksheet
Dim objOLE As OLEObject, LaB As MSForms.Label
Dim i As Long
Set WS = ThisWorkbook.Sheets("Report Filters")

For Each objOLE In WS.OLEObjects
If TypeOf objOLE.Object Is MSForms.Label Then
Set LaB = objOLE.Object
If LaB.Index = Label1.Index Or LaB.Index = Label2.Index Or LaB.Index = Label3.Index Or LaB.Index = lblTime.Index Or LaB.Index = lblSelectedFilters.Index Then
LaB.Visible = True
Else: LaB.Visible = False
End If
End If
Next objOLE

End Sub

 

[RoryA]

They are more stable but quite different. You assign a macro to them, and the returned value is the index number of the selected item rather than its value. The formatting options are limited too.

 

[RoryA]

Where is that code located? If it's not in the same sheet as the label, then it won't work.

 

[gjp55]

I also get another compile error for the same code

"ByRef argument type mismatch"

 

[gjp55]

Its all in the same sheet.

Thanks very much for your help with this by the way.

 

[RoryA]

Did you definitely use an ActiveX label and not a Forms one?

 

[gjp55]

the first error is in the following code (stops at code in red):


Private Function GenerateParameters()
Dim i As Integer, count As Integer, Cols As Integer, GroupList As String, HasSelection As Boolean, FilterName As String
Dim WS As Worksheet
Dim objOLE As OLEObject, LB As msforms.ListBox
Dim x As Long, y As Long
count = 1
ClearFilterDisplay
Set WS = ThisWorkbook.Sheets("Report Filters")
For Each objOLE In WS.OLEObjects

If TypeOf objOLE.Object Is msforms.ListBox Then

Set LB = objOLE.Object

Select Case LB.Name
Case "lstBN"
FilterName = "Brand Name"
Cols = 1
Case "lstPC"
FilterName = "Product Category"
Cols = 1
Case "lstGC"
FilterName = "Global Customer"
Cols = 1
Case "lstLC"
FilterName = "Local Customer"
Cols = 1
Case "lstSD"
FilterName = "Sales Distribution"
Cols = 0
Case "lstSO"
FilterName = "Sales Office"
Cols = 0
Case "lstSG"
FilterName = "Sales Group"
Cols = 0
Case "lstDCh"
FilterName = "Delivery Channel"
Cols = 1
Case "lstDCe"
FilterName = "DeliveryCentre"
Cols = 1
Case "lstYear", Cols = 1
FilterName = "Year"
Case "lstQtr"
FilterName = "Quarter"
Case "lstMonth"
Cols = 1
FilterName = "Months"
Cols = 1
End Select


If LB.Index = lstSelectedFilters.Index Then
y = 1
Else
GroupList = ""
For i = 0 To LB.ListCount - 1
If LB.Selected(i) = True Then 'if the value in the list has been selected
'Add a header row to the List selection once only
If HasSelection = False Then
lstSelectedFilters.AddItem " " & FilterName & " Filters"
HasSelection = True
End If

If GroupList = "" Then
GroupList = LB.List(i)
Else
GroupList = GroupList & "," & LB.List(i)
End If

lstSelectedFilters.AddItem LB.List(i, Cols)
count = count + 1
End If
Next i

If GroupList = "" Then
GenerateParameters = "0"
Else
GenerateParameters = GroupList
End If
i = 0
count = 1
HasSelection = False
End If
Else
y = 1 'Next objOLE
End If

Next objOLE
End Function

 

[gjp55]

The error with that code is "unable to get the Object Property of the OLEObject Class

 

[RoryA]

You probably have objects that are not controls on your sheet. try using:

For Each objole In WS.OLEObjects
    If objole.OLEType = xlOLEControl Then
        If TypeOf objole.Object Is msforms.ListBox Then

 

[gjp55]

just going to try that now but also just noticed that when I try to save the file, I get "uninitialized Active X objects can not be transfered to the selected fileformat. The controls will be lost if you continue. File is being saved as excel 97-2003 (so it can be used by users with earlier excel versions). Not sure if thats relevant to this issue though