How to access textBox placed on sheet in VBA module?

Question

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.

It will be either form or ActiveX. Did you use the "Insert" button on the "Developer" tab? — Kyle
– Kyle, Commented Jul 14, 2016 at 18:51
That is helpful, thanks. See below. That should do it. Please post back if not. — Kyle
– Kyle, Commented Jul 14, 2016 at 18:56

cyboashu · Accepted Answer · 2016-07-14 19:37:56Z

2

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

edited Jul 14, 2016 at 19:37

answered Jul 14, 2016 at 18:50

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kyle Over a year ago

This is only for ActiveX. If it is a form control, this will not work.

SPlatten Over a year ago

Tried this before I posted on StackOverflow, the control is not accessible from the sheet.

David Zemens Over a year ago

Would this also work? objDBsheet.OLEObjects("tbSql")? (even declaring objDBSheet as Worksheet?

David Zemens · Accepted Answer · 2016-07-14 18:57:57Z

1

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

answered Jul 14, 2016 at 18:57

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Comments

Kyle · Accepted Answer · 2016-07-14 19:00:04Z

1

You need to use:

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

edited Jul 14, 2016 at 19:00

answered Jul 14, 2016 at 18:56

Kyle

2,5452 gold badges19 silver badges33 bronze badges

3 Comments

cyboashu Over a year ago

is there a Text property for Shapes?

David Zemens Over a year ago

@cyboashu no, you'd need .TextFrame.Characters.Text

cyboashu Over a year ago

@DavidZemens yup. I sued the same in my answer.

SPlatten · Accepted Answer · 2016-07-14 19:59:38Z

0

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.

answered Jul 14, 2016 at 19:59

SPlatten

5,85713 gold badges79 silver badges140 bronze badges

1 Comment

David Zemens Over a year ago

hmmm not that it should (usually) matter, but I wonder if Application.Worksheets or ActiveWorkbook.Worksheets would have worked?

relayman357 · Accepted Answer · 2021-01-19 14:34:58Z

0

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"

answered Jan 19, 2021 at 14:34

relayman357

8752 gold badges11 silver badges35 bronze badges

Comments

DaveL17 · Accepted Answer · 2025-01-27 18:37:14Z

0

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

edited Jan 27 at 18:37

DaveL17

2,06110 gold badges30 silver badges48 bronze badges

answered Jan 27 at 17:21

Sid 03

1

Comments

VBasic2008 · Accepted Answer · 2025-03-20 08:24:00Z

Reference an ActiveX Control by Its Name

The Ingredients

In the workbook containing the code (ThisWorkbook), OP has a worksheet named Database Info. with its code name Sheet1.
OP has a text box renamed to tbSQL.
When in Design Mode (Developers tab), when the text box is selected, =EMBED("Forms.TextBox.1","") shows in the formula bar. This clearly indicates that the text box is an ActiveX Control. The same text will also show for all other text boxes of the same type. For any ActiveX label, it would show =EMBED("Forms.Label.1",""). You can change its name, which is at the same time its code name, in Excel's Name Box or right-click the selected control and select Properties and modify the (Name) property in VBA's Properties Window. They always become the same.

The Issue

OP wants to create a reference to the text box using its name as a string. Using this reference, he then wants to write some text to it, e.g.,
```
tb.Text = "Some Text"
```
cyboashu (rather implicitly) shows that he could declare the sheet's variable as on object and do:
```
Dim ws As Object: Set ws = ThisWorkbook.Sheets("Database Info.")
Dim tb As MSForms.TextBox: Set tb = ws.tbSQL
```
or use the sheet's code name to do:
```
Dim tb As MSForms.TextBox: Set tb = Sheet1.tbSQL
```
and continue with:
```
tb.Text = "Some Text"
```
These don't provide the use of the text box name as a string.

David Zemens (again rather implicitly) shows that he could do the following:

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Database Info.")
Dim tb As Object: Set tb = ws.Shapes("tbSQL").OleFormat.Object
tb.Object.Text = "Some Text"

Close but hard to figure out. Also, no Intellisense due to As Object.

relayman357 suggested the following possibility:
```
ActiveSheet.tbSQL.Text = "Some Text"
```
Interesting, but code names again.
Since OP cannot do:
```
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Database Info.")
ws.tbSQL.Text = "Some Text"
```
he has chosen to use the code names instead (a form of the 2nd solution of cyboashu):
```
Sheet1.tbSQL.Text = "Some Text"
```
forfeiting the desired dynamic.

The Solution

Sub Test

    Const SHEET_NAME As String = "Database Info."
    Const TEXTBOX_NAME As String = "tbSQL"
    Const TEXTBOX_TEXT As String = "Some Text"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets(SHEET_NAME)
    Dim tb As MSForms.TextBox: Set tb = ws.OLEObjects(TEXTBOX_NAME).Object
    ' Alternatively, following the idea of `David Zemens`:
    'Set tb = ws.Shapes(TEXTBOX_NAME).OLEFormat.Object.Object ' yes, twice!
    
    tb.Text = TEXTBOX_TEXT

    ' Or the one-liner:
    'ThisWorkbook.Sheets(SHEET_NAME).OLEObjects(TEXTBOX_NAME).Object _
        .Text = TEXTBOX_TEXT

End Sub

Collectives™ on Stack Overflow

How to access textBox placed on sheet in VBA module?

7 Answers 7

3 Comments

Comments

3 Comments

1 Comment

Comments

Comments

Reference an ActiveX Control by Its Name

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Comments

3 Comments

1 Comment

Comments

Comments

Reference an ActiveX Control by Its Name

Comments

Your Answer

Sign up or log in

Post as a guest

Related