Excel TextBox Control

[James]

I have a textbox named "txt_report_date" inserted in a worksheet called "admin"
When this worksheet is active I can populate the textbox value using the me
syntax, me.txt_report_date = ...
However I would like to set the value of the text box when the workbook opens.
Can you please tell me how to reference the text box from the on open event
of the workbook. Thanks.

 

[Peter T]

Qualify the object with a reference to the sheet, eg

ThisWorkbook.Sheet1.txt_report_date ' where sheet1 is the codename

But if in any doubt about the sheet and object existing use one of these -

ThisWorkbook.Worksheets("Sheet1").txt_report_date etc

or even -

Dim ole As OLEObject
On Error Resume Next
Set ole = ThisWorkbook.Worksheets("Sheet1").OLEObjects("txt_report_date")
On Error GoTo 0
If Not ole Is Nothing Then
ole.Object.Value = Format(Now, "dd mmm yyyy")
End If

Regards,
Peter T

 

[James]

Thanks. That worked great.

Qualify the object with a reference to the sheet, eg

ThisWorkbook.Sheet1.txt_report_date ' where sheet1 is the codename

But if in any doubt about the sheet and object existing use one of these -

ThisWorkbook.Worksheets("Sheet1").txt_report_date etc

or even -

Dim ole As OLEObject
On Error Resume Next
Set ole = ThisWorkbook.Worksheets("Sheet1").OLEObjects("txt_report_date")
On Error GoTo 0
If Not ole Is Nothing Then
ole.Object.Value = Format(Now, "dd mmm yyyy")
End If

Regards,
Peter T






.