Excel TextBox Control
 

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.

Excel TextBox Control
 

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" wrote in message
...
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.

Excel TextBox Control
 

Thanks. That worked great.

"Peter T" wrote:

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" wrote in message
...
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.


.