CODEWITHSUNDEEP
excelvbatextbox
SPlatten
SPlatten

Reputation: 5760

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.

Upvotes: 0

Views: 19750

Answers (6)

Sid 03
Sid 03

Reputation: 1

This is the way I found I could get the text. There are not a lot of good examples on how to work w/TextFrames.

ThisWorkbook.Sheets("MySheet").Shapes("MyShape").TextFrame.Characters.Text

Upvotes: 0

relayman357
relayman357

Reputation: 855

If you put an ActiveX texbox on a worksheet named "MyTextBox" then you can set it's text in vba like this:

ActiveSheet.MyTextBox.Text = "hi"

Upvotes: 0

SPlatten
SPlatten

Reputation: 5760

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.

Upvotes: 0

cyboashu
cyboashu

Reputation: 10433

Most likely its a bug in Excel. It has nothing to do with Excel-2003.

If you use a Worksheet type variable then Excel fails to discover the control on that sheet in VBA. So if you declare your sheet holding variable as Object / Variant , the code will work fine.

Other alternative is to directly use the Worksheet's CodeName, so if you set the Worksheet's name as wksDBSheet in the VBA IDE's property grid and use that in your code, it will discover the TextBox

Sub test()
    Dim objDBsheet As Object 'As Worksheet  // Making the 0bjDBSheet type as Object or Variant
                                           '// Allows the discovery of the TextBox on the sheet.
                                           '// Most Likely its a bug.
    Dim objSQL As Range

    Dim tbSQL As MSForms.TextBox
    Set objDBsheet = Application.Worksheets("Database Info.")
    Set tbSQL = objDBsheet.tbSQL
    tbSQL.Text = "Bug"


    '/  Other Alternative is to directly use the CddeName of the sheet.
    Set tbSQL = wksDBsheet.tbSQL
    tbSQL.Text = "Code Name used"

End Sub

Upvotes: 2

Kyle
Kyle

Reputation: 2545

You need to use:

Sheets("Sheetname").Shapes("tbSQL").TextFrame.Characters.Text = "Anything you want"

Upvotes: 1

David Zemens
David Zemens

Reputation: 53623

Text Box as shape, or Label as Form Control, or ActiveX control:

Sub f()
Dim tb As Shape, lblControl As Object, lblActiveX As Object
Set tb = Sheet1.Shapes("TextBox 1")
Set lblControl = Sheet1.Shapes("Label 2").OLEFormat.Object
Set lblActiveX = Sheet1.Shapes("Label1").OLEFormat.Object

lblControl.Text = "Form Control"
lblActiveX.Object.Caption = "ActiveX Control"
tb.TextFrame.Characters.Text = "Text Box"

End Sub

Upvotes: 1

Related Questions