excelvbatextbox

How to access textBox placed on sheet in VBA module?


I have placed a TextBox control on a sheet in Excel, in a VBA module I want to acces the TextBox and populate with content.

How do I reference the TextBox control?

I've named the TextBox 'tbSQL', in the module I can see Application and can refernece the sheet from the module, the sheet is called 'Database Info.'

I want to write data into the TextBox from the VBA module, but so far I haven't been able to reference it.

I've tried:

    Public Const DATABASE_INFO As String = "Database Info."

    Dim objDBsheet As Worksheet, objSQL As Range
    Dim tbSQL As TextBox
    Set objDBsheet = Application.Sheets(DATABASE_INFO)
    Set tbSQL = objDBsheet.Shapes("tbSQL")

But this is as far as I get it errors on the Set tbSQL line. The reported error is "Type Mismatch"

I know the control is a TextBox, it was created from the Controlbox toolbar. When looking in the range bar in Excel it displays:

    "=EMBED("Forms.TextBox.1","")"

In the Properties box for the TextBox control I have set the (Name) property to tbSQL, however it remains unchanged in the fx text box. It does show as tbSQL in the range box.


Solution

  • In the end it looks like it is a bug in Excel 2003, I was able to reference the control by using Sheet1.tbSQL instead of going through Application.Sheets.