Moving all code to add-in (reference): error when referencing ActiveX controls

[EuroSong]

I have a collection of workbooks, each containing monthly data. They rely heavily on VBA functionality, and I'm always making tweaks and improvements.

Since I recently found out how to separate out the code into an add-in, it struck me as a great idea to do so for two reasons:
1) Smaller files, as the code & userforms would not have to be saved with each monthly file;
2) I would be able to make changes to the code all in one place, instead of having to go into each separate monthly workbook; check no-one else was using it; un-share it; modify the code; then re-share.

I therefore took out the code and saved it as an .xlam file, then linked it as a reference from the main workbook. Along the way I have solved several problems, like referencing sheets by code-name from the xlam file; how to load user forms correctly; and how to gracefully disable functionality if for some reason someone moved the xlam file.

However there is one problem that eludes me. My code makes reference to ActiveX controls on a worksheet: but the line creates a "Method or data member not found" compile error. Please see the following:

Function ClearFilters()
    Set sht_Cases = shtObj(ActiveWorkbook, "sht_Cases") ' See function below
    With sht_Cases
        .RB_ALL.Value = True ''' This line causes the error
        .RB_MaleFemaleFamily.Value = True ' as would this line, if the code progressed.
    End With
End Function

'  The following function is used in several places, in order to return a sheet object from code name in another workbook
' "ActiveWorkbook" is passed, which will naturally be the WB active when the user triggers the function
Function shtObj(wb As Workbook, CodeName As String) As Excel.Worksheet
    Dim ws As Excel.Worksheet
    For Each ws In wb.Worksheets
        If ws.CodeName = CodeName Then
            Set shtObj = ws
            Exit For
        End If
    Next ws
End Function

As far as I can tell, the shtObj function works correctly in that it returns the correct sheet as an object. In break mode in the Immediate window I can even msgbox sht_Cases.name and it will correctly return the name of the sheet - therefore it has been set correctly. However the compiler refuses to recognise the RB_ALL ActiveX object as a child of the sheet.

This works perfectly when the code is contained within the same workbook. I thought perhaps it had something to do with the xlam file not having a reference to the ActiveX object library: but I checked, and found the same references in the .xlam file as are present in the main file (except obviously the fact that the main file has an additional reference to the xlam file!)

Grateful for any help. Thanks.

 

[shg]

VBA can't bind the control name to the sheet object at runtime. You'll need to use the control name, something like this:

    .OLEBjects("RB_All").Object.Value = True

 

[EuroSong]

Thank you so much, shg! Problem solved

 

[shg]

You're welcome.