Hi

I wonder if someone can help me. I am using Excel 97 and I've got a
textbox on the worksheet that I want to be filled with information on a
cell on another sheet. How would I reference this in VBA so that the
textbox was updated every time the sheet was activated?

I had looked on the web and found one way to do it, but it was a textbox
from the Drawing menu not the Control Toolbox menu, and it didn't have
scroll bars, which it needs to have.

So, I've got in VBA:

txtMessages.Text =

but I don't know how to reference the cell.

Thanks in advance.
--
Charles

Hi Charles

You may not need VB code. Place your cursor in the text box and then
activate the formula bar. Type "=" in the formula bar and then activate the
relevant cell on the worksheet you want the text box linked to.

If you want to use code, place the following in the relevant worksheet
module

Private Sub Worksheet_Activate()
Sheets("Sheet1").Shapes("Text Box 2").TextFrame.Characters.Text = _
Sheets("Sheet2").Range("B5")
End Sub

Untested in XL 97, tested in XL 2002


--
XL2002
Regards

William



"Charles" wrote in message
...
| Hi
|
| I wonder if someone can help me. I am using Excel 97 and I've got a
| textbox on the worksheet that I want to be filled with information on a
| cell on another sheet. How would I reference this in VBA so that the
| textbox was updated every time the sheet was activated?
|
| I had looked on the web and found one way to do it, but it was a textbox
| from the Drawing menu not the Control Toolbox menu, and it didn't have
| scroll bars, which it needs to have.
|
| So, I've got in VBA:
|
| txtMessages.Text =
|
| but I don't know how to reference the cell.
|
| Thanks in advance.
| --
| Charles

Ignore my post, it relates to a text box from the Drawing toolbar. Sorry

--
XL2002
Regards

William



"William" wrote in message
...
| Hi Charles
|
| You may not need VB code. Place your cursor in the text box and then
| activate the formula bar. Type "=" in the formula bar and then activate
the
| relevant cell on the worksheet you want the text box linked to.
|
| If you want to use code, place the following in the relevant worksheet
| module
|
| Private Sub Worksheet_Activate()
| Sheets("Sheet1").Shapes("Text Box 2").TextFrame.Characters.Text = _
| Sheets("Sheet2").Range("B5")
| End Sub
|
| Untested in XL 97, tested in XL 2002
|
|
| --
| XL2002
| Regards
|
| William
|
|
|
| "Charles" wrote in message
| ...
| | Hi
| |
| | I wonder if someone can help me. I am using Excel 97 and I've got a
| | textbox on the worksheet that I want to be filled with information on a
| | cell on another sheet. How would I reference this in VBA so that the
| | textbox was updated every time the sheet was activated?
| |
| | I had looked on the web and found one way to do it, but it was a textbox
| | from the Drawing menu not the Control Toolbox menu, and it didn't have
| | scroll bars, which it needs to have.
| |
| | So, I've got in VBA:
| |
| | txtMessages.Text =
| |
| | but I don't know how to reference the cell.
| |
| | Thanks in advance.
| | --
| | Charles
|
|
|