Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 413
Default Word Control in Excel

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default Word Control in Excel

Try adding a reference to "microsoft forms 2.0 object library"

I'm not sure how this'll work with your Word problem, though.

Stuart wrote:

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004


--

Dave Peterson

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 413
Default Word Control in Excel

Thanks for that.
It now runs to the line "For Each etc" and then
immediately jumps to "End Sub"

Regards.

"Dave Peterson" wrote in message
...
Try adding a reference to "microsoft forms 2.0 object library"

I'm not sure how this'll work with your Word problem, though.

Stuart wrote:

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004


--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default Word Control in Excel

Hi Stuart,

Try:

Sub Test()
Dim myShape As Shape, rng As Range

For Each myShape In ActiveWorkbook.Sheets _
' ("Contract Master Order").Shapes
If myShape.Name Like "Text*" Then
Set rng = myShape.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

---
Regards,
Norman


"Stuart" wrote in message
...
Thanks for that.
It now runs to the line "For Each etc" and then
immediately jumps to "End Sub"

Regards.

"Dave Peterson" wrote in message
...
Try adding a reference to "microsoft forms 2.0 object library"

I'm not sure how this'll work with your Word problem, though.

Stuart wrote:

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004


--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default Word Control in Excel

Hi Stuart,

Please uncomment the continuation part of the For Each line!


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Stuart,

Try:

Sub Test()
Dim myShape As Shape, rng As Range

For Each myShape In ActiveWorkbook.Sheets _
' ("Contract Master Order").Shapes
If myShape.Name Like "Text*" Then
Set rng = myShape.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

---
Regards,
Norman


"Stuart" wrote in message
...
Thanks for that.
It now runs to the line "For Each etc" and then
immediately jumps to "End Sub"

Regards.

"Dave Peterson" wrote in message
...
Try adding a reference to "microsoft forms 2.0 object library"

I'm not sure how this'll work with your Word problem, though.

Stuart wrote:

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004

--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004








  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 413
Default Word Control in Excel

Thanks for that.
It steps through without an error, but no values appear
in the textboxes.
Any ideas, please?

Regards.

"Norman Jones" wrote in message
...
Hi Stuart,

Please uncomment the continuation part of the For Each line!


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Stuart,

Try:

Sub Test()
Dim myShape As Shape, rng As Range

For Each myShape In ActiveWorkbook.Sheets _
' ("Contract Master Order").Shapes
If myShape.Name Like "Text*" Then
Set rng = myShape.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

---
Regards,
Norman


"Stuart" wrote in message
...
Thanks for that.
It now runs to the line "For Each etc" and then
immediately jumps to "End Sub"

Regards.

"Dave Peterson" wrote in message
...
Try adding a reference to "microsoft forms 2.0 object library"

I'm not sure how this'll work with your Word problem, though.

Stuart wrote:

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004

--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default Word Control in Excel

Hi Stuart,

It steps through without an error, but no values appear
in the textboxes.


No value will appear *IN* the textbox. As written,. the routine enters Bingo
in the worksheet cell that corresponds to under the upper-left corner of the
text box. Depending on the precise positioning of the textbox, this cell
may be hidden (entirely covered). You can confirm this by changing;

Set rng = myShape.TopLeftCell

to;

Set rng = myShape.TopLeftCell(1,0)

which will enter Bingo in the cell one column to the left of the
TopLeftCell.


---
Regards,
Norman





"Stuart" wrote in message
...
Thanks for that.
It steps through without an error, but no values appear
in the textboxes.
Any ideas, please?

Regards.

"Norman Jones" wrote in message
...
Hi Stuart,

Please uncomment the continuation part of the For Each line!


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Stuart,

Try:

Sub Test()
Dim myShape As Shape, rng As Range

For Each myShape In ActiveWorkbook.Sheets _
' ("Contract Master Order").Shapes
If myShape.Name Like "Text*" Then
Set rng = myShape.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

---
Regards,
Norman


"Stuart" wrote in message
...
Thanks for that.
It now runs to the line "For Each etc" and then
immediately jumps to "End Sub"

Regards.

"Dave Peterson" wrote in message
...
Try adding a reference to "microsoft forms 2.0 object library"

I'm not sure how this'll work with your Word problem, though.

Stuart wrote:

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date:

05/07/2004

--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004




  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Word Control in Excel

I did what you described and ran the code from Norman (modified to refer to
the activesheet)

Sub Test()
Dim myShape As Shape, rng As Range

For Each myShape In ActiveSheet.Shapes
If myShape.Name Like "Text*" Then
Set rng = myShape.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

and it worked fine. However, the textbox in word was drawn from the drawing
toolbar and came into excel as a member of the textboxes collection. I
then put in a textbox in word from the control toolbox toolbar and in design
mode, selected it and copied it. Pasted into Excel, it worked with the
original code you posted (modified to refer to the activesheet)

Sub AATest()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
Thanks for that.
It steps through without an error, but no values appear
in the textboxes.
Any ideas, please?

Regards.

"Norman Jones" wrote in message
...
Hi Stuart,

Please uncomment the continuation part of the For Each line!


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Stuart,

Try:

Sub Test()
Dim myShape As Shape, rng As Range

For Each myShape In ActiveWorkbook.Sheets _
' ("Contract Master Order").Shapes
If myShape.Name Like "Text*" Then
Set rng = myShape.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

---
Regards,
Norman


"Stuart" wrote in message
...
Thanks for that.
It now runs to the line "For Each etc" and then
immediately jumps to "End Sub"

Regards.

"Dave Peterson" wrote in message
...
Try adding a reference to "microsoft forms 2.0 object library"

I'm not sure how this'll work with your Word problem, though.

Stuart wrote:

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date:

05/07/2004

--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004




  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 413
Default Word Control in Excel

Got it, thanks.
If the textbox is set to wraptext, and the user has entered
their data, is there a way to assign the value (data) in the
textbox to the same range in the sheet covered by the
textbox, and such that the data displays correctly?

Or do I just leave the textbox Visible but Disabled
before the file is saved?

Basically user opens the book, and the Open Event
sets up the single sheet, enabling textboxes, etc

User enters their data and then prints a single A4
range. The print should also include the textbox data
(that is untested).

When they Save the file, the Before Save Event creates
a new single sheet book, copies the range into the new
sheet (thus no code is copied) then saves the new book.

The original book is closed.

Regards and thanks.

"Norman Jones" wrote in message
...
Hi Stuart,

It steps through without an error, but no values appear
in the textboxes.


No value will appear *IN* the textbox. As written,. the routine enters

Bingo
in the worksheet cell that corresponds to under the upper-left corner of

the
text box. Depending on the precise positioning of the textbox, this cell
may be hidden (entirely covered). You can confirm this by changing;

Set rng = myShape.TopLeftCell

to;

Set rng = myShape.TopLeftCell(1,0)

which will enter Bingo in the cell one column to the left of the
TopLeftCell.


---
Regards,
Norman





"Stuart" wrote in message
...
Thanks for that.
It steps through without an error, but no values appear
in the textboxes.
Any ideas, please?

Regards.

"Norman Jones" wrote in message
...
Hi Stuart,

Please uncomment the continuation part of the For Each line!


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Stuart,

Try:

Sub Test()
Dim myShape As Shape, rng As Range

For Each myShape In ActiveWorkbook.Sheets _
' ("Contract Master Order").Shapes
If myShape.Name Like "Text*" Then
Set rng = myShape.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

---
Regards,
Norman


"Stuart" wrote in message
...
Thanks for that.
It now runs to the line "For Each etc" and then
immediately jumps to "End Sub"

Regards.

"Dave Peterson" wrote in message
...
Try adding a reference to "microsoft forms 2.0 object library"

I'm not sure how this'll work with your Word problem, though.

Stuart wrote:

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date:

05/07/2004

--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004


  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 413
Default Word Control in Excel

Many thanks.

That was my error. I had forgotten that the two types
of Control are different. My textbox was not an
ActiveX control.

Regards.

"Tom Ogilvy" wrote in message
...
I did what you described and ran the code from Norman (modified to refer

to
the activesheet)

Sub Test()
Dim myShape As Shape, rng As Range

For Each myShape In ActiveSheet.Shapes
If myShape.Name Like "Text*" Then
Set rng = myShape.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

and it worked fine. However, the textbox in word was drawn from the

drawing
toolbar and came into excel as a member of the textboxes collection. I
then put in a textbox in word from the control toolbox toolbar and in

design
mode, selected it and copied it. Pasted into Excel, it worked with the
original code you posted (modified to refer to the activesheet)

Sub AATest()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
Thanks for that.
It steps through without an error, but no values appear
in the textboxes.
Any ideas, please?

Regards.

"Norman Jones" wrote in message
...
Hi Stuart,

Please uncomment the continuation part of the For Each line!


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Stuart,

Try:

Sub Test()
Dim myShape As Shape, rng As Range

For Each myShape In ActiveWorkbook.Sheets _
' ("Contract Master Order").Shapes
If myShape.Name Like "Text*" Then
Set rng = myShape.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

---
Regards,
Norman


"Stuart" wrote in message
...
Thanks for that.
It now runs to the line "For Each etc" and then
immediately jumps to "End Sub"

Regards.

"Dave Peterson" wrote in message
...
Try adding a reference to "microsoft forms 2.0 object library"

I'm not sure how this'll work with your Word problem, though.

Stuart wrote:

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date:

05/07/2004

--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004




  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default Word Control in Excel

Hi Stuart,

If the textbox is set to wraptext, and the user has entered
their data, is there a way to assign the value (data) in the
textbox to the same range in the sheet covered by the
textbox


Yes, use the textbox TopLeftCell propert as Tom Ogilvy showed you. You can
do this for all your textboxes in one fell swoop, as in Tom's code, or
individully using (say) Textbox LostFocus event code.

and such that the data displays correctly?


Reading another of your threads:
http://tinyurl.com/ywpx5


which I had not previously read, I think that Tom has already covered this
issue.

Or do I just leave the textbox Visible but Disabled
before the file is saved?


This confuses me. If this were the other way round, it would still confuse
me - but less!

---
Regards,
Norman

textbox,"Stuart" wrote in message

...
Got it, thanks.
If the textbox is set to wraptext, and the user has entered
their data, is there a way to assign the value (data) in the
textbox to the same range in the sheet covered by the
textbox, and such that the data displays correctly?

Or do I just leave the textbox Visible but Disabled
before the file is saved?

Basically user opens the book, and the Open Event
sets up the single sheet, enabling textboxes, etc

User enters their data and then prints a single A4
range. The print should also include the textbox data
(that is untested).

When they Save the file, the Before Save Event creates
a new single sheet book, copies the range into the new
sheet (thus no code is copied) then saves the new book.

The original book is closed.

Regards and thanks.

"Norman Jones" wrote in message
...
Hi Stuart,

It steps through without an error, but no values appear
in the textboxes.


No value will appear *IN* the textbox. As written,. the routine enters

Bingo
in the worksheet cell that corresponds to under the upper-left corner of

the
text box. Depending on the precise positioning of the textbox, this

cell
may be hidden (entirely covered). You can confirm this by changing;

Set rng = myShape.TopLeftCell

to;

Set rng = myShape.TopLeftCell(1,0)

which will enter Bingo in the cell one column to the left of the
TopLeftCell.


---
Regards,
Norman





"Stuart" wrote in message
...
Thanks for that.
It steps through without an error, but no values appear
in the textboxes.
Any ideas, please?

Regards.

"Norman Jones" wrote in message
...
Hi Stuart,

Please uncomment the continuation part of the For Each line!


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Stuart,

Try:

Sub Test()
Dim myShape As Shape, rng As Range

For Each myShape In ActiveWorkbook.Sheets _
' ("Contract Master Order").Shapes
If myShape.Name Like "Text*" Then
Set rng = myShape.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

---
Regards,
Norman


"Stuart" wrote in message
...
Thanks for that.
It now runs to the line "For Each etc" and then
immediately jumps to "End Sub"

Regards.

"Dave Peterson" wrote in message
...
Try adding a reference to "microsoft forms 2.0 object library"

I'm not sure how this'll work with your Word problem, though.

Stuart wrote:

Using Office 2000, I open Word and in a new document
I insert a Textbox. I then copy and paste it into a

worksheet.

I cannot find a way to programmatically refer to it.

Sub Test()
Dim oleObj As OLEObject, rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = "Bingo"
End If
Next
End Sub

The 'If' statement gives the error:
User defined type not defined

How do I control the Textbox, please?

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date:

05/07/2004

--

Dave Peterson



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date:

05/07/2004








---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.716 / Virus Database: 472 - Release Date: 05/07/2004




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel spin box - no Control tab in Format Control dialong box tocoau Excel Worksheet Functions 7 August 10th 08 03:15 PM
Word wrapping in Text Box Control feature Stilla Excel Worksheet Functions 2 December 7th 05 09:08 PM
how to control word from excel Torstein S. Johnsen[_2_] Excel Programming 1 February 10th 04 02:10 PM
Print Control in Word/Excel Charles Maxson Excel Programming 0 December 30th 03 08:55 PM
Print Control in Word/Excel losmac[_2_] Excel Programming 0 December 30th 03 08:22 PM


All times are GMT +1. The time now is 07:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"