How to read/set Excel Textbox property from MS-Access VBA-CodePudding

I have a simple Textbox in Excel worksheet (.xlsx) where I can read the BackColor property in Excel VBA sub with:

Debug.Print TextBox1.BackColor

I'm trying to reference that same textbox from MS-Access using the Excel 16 Object Model, but it can't see the textbox under any of the Excel Worksheet objects I'm looking at

It errors out on the line marked with asterisks below with error message

Method or Data Member Not Found

Public Sub SetHexColor()

    Dim xlApp   As Excel.Application
    Dim xlBook  As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    
    Set xlApp = GetObject(, "Excel.Application")
    
    Set xlBook = Workbooks.Open("C:\Users\.........\Documents\TextBox.xlsx")
    Set xlSheet = xlBook.Worksheets(1)
    **Debug.Print xlSheet.TextBox1.BackColor**
    
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

Is there another way to reference and preferably set properties of a Textbox control in Excel?

I'd prefer not to have call an Excel function to set the property if possible - or maybe that's the issue - it has to be an xlsm file?

CodePudding user response：

Excel.Worksheet is a generic worksheet object - it only provides access to "out of the box" methods of the Worksheet object: if you've "subclassed" your worksheet by adding members such as TextBox1 then you can't access those added members via the generic Worksheet type.

You can either do this

Dim xlSheet As Object

or leave the declaration as-is, and use something like

Debug.Print xlSheet.OLEObjects("TextBox1").Object.BackColor

Note this is not specific to automating Excel from access VBA - the same would be true if working entirely within Excel.