text box for displays of information in excel cells

I am trying to automate the creation of a series of textboxes (one for each
page of my workbook - which consists of many pages) that will grab data from
cells on different pages of the workbook and display the information to a
user. This is done interactively. The data in the box has to change
constantly depending on what triggers its display. I got much of this to work
but I am encountering a few problems, which I suspect are simple, but which
I can't resolve.

I enter text in excel cells. But sometimes the text is very long and ought
to be broken down into paragraphs. Is there any way either (1) to enter the
text with paragraph marks embeded so that when it is dumped into the text box
it will look formatted or (2) to bring it the text piecemeal (I can do that)
and add the appropriate marks as it is being read into the textbox (I could
add some character to the text that would allow a program to substitute it
for whatever is necessary.)

I have tried a number of things but none seem to work. It may be that the
root of my problem is that I do not have the text box set to mulitline. I
can't seem to get ".multiline = true" to work (I get a runtime error).

I do not want to create each box manually since there will be very many of
them, and I suspect I will want to change other things as I progres.
Currently I have a macro to do some of that. But I can't seem to get all the
details right.

There are other such issues. But initially, I would greatly appreciate help
in resolving the particular ones mentioned.

Thanks.

What sort of textbox, control toolbox or userform? Multiline should work,
and add Alt-Enter, or Chr(10) should give you the breaks.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"eugene" <eugene@discussions.microsoft.com> wrote in message
news:7B46CF5D-E70B-4842-872B-927B44C01C04@microsoft.com...
> I am trying to automate the creation of a series of textboxes (one for
each
> page of my workbook - which consists of many pages) that will grab data
from
> cells on different pages of the workbook and display the information to a
> user. This is done interactively. The data in the box has to change
> constantly depending on what triggers its display. I got much of this to
work
> but I am encountering a few problems, which I suspect are simple, but
which
> I can't resolve.
>
> I enter text in excel cells. But sometimes the text is very long and ought
> to be broken down into paragraphs. Is there any way either (1) to enter
the
> text with paragraph marks embeded so that when it is dumped into the text
box
> it will look formatted or (2) to bring it the text piecemeal (I can do
that)
> and add the appropriate marks as it is being read into the textbox (I
could
> add some character to the text that would allow a program to substitute it
> for whatever is necessary.)
>
> I have tried a number of things but none seem to work. It may be that the
> root of my problem is that I do not have the text box set to mulitline. I
> can't seem to get ".multiline = true" to work (I get a runtime error).
>
> I do not want to create each box manually since there will be very many of
> them, and I suspect I will want to change other things as I progres.
> Currently I have a macro to do some of that. But I can't seem to get all
the
> details right.
>
> There are other such issues. But initially, I would greatly appreciate
help
> in resolving the particular ones mentioned.
>
> Thanks.
>

> What sort of textbox, control toolbox or userform?

I don't know the difference. But this is how I create my text box:

dim StatisticsTextBox as shape
Set StatisticsTextBox = ActiveSheet.Shapes.AddTextbox
_(msoTextOrientationHorizontal, 3, 3, 200, 300)

When I add the following line, I get an error (using Windows XP and Excel
2004):
StatisticsTextBox.MultiLine = True

--
eugene


"Bob Phillips" wrote:

> What sort of textbox, control toolbox or userform? Multiline should work,
> and add Alt-Enter, or Chr(10) should give you the breaks.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "eugene" <eugene@discussions.microsoft.com> wrote in message
> news:7B46CF5D-E70B-4842-872B-927B44C01C04@microsoft.com...
> > I am trying to automate the creation of a series of textboxes (one for
> each
> > page of my workbook - which consists of many pages) that will grab data
> from
> > cells on different pages of the workbook and display the information to a
> > user. This is done interactively. The data in the box has to change
> > constantly depending on what triggers its display. I got much of this to
> work
> > but I am encountering a few problems, which I suspect are simple, but
> which
> > I can't resolve.
> >
> > I enter text in excel cells. But sometimes the text is very long and ought
> > to be broken down into paragraphs. Is there any way either (1) to enter
> the
> > text with paragraph marks embeded so that when it is dumped into the text
> box
> > it will look formatted or (2) to bring it the text piecemeal (I can do
> that)
> > and add the appropriate marks as it is being read into the textbox (I
> could
> > add some character to the text that would allow a program to substitute it
> > for whatever is necessary.)
> >
> > I have tried a number of things but none seem to work. It may be that the
> > root of my problem is that I do not have the text box set to mulitline. I
> > can't seem to get ".multiline = true" to work (I get a runtime error).
> >
> > I do not want to create each box manually since there will be very many of
> > them, and I suspect I will want to change other things as I progres.
> > Currently I have a macro to do some of that. But I can't seem to get all
> the
> > details right.
> >
> > There are other such issues. But initially, I would greatly appreciate
> help
> > in resolving the particular ones mentioned.
> >
> > Thanks.
> >
>
>
>

Thta's what I feraed.

Try creating a controls toolbox textbox which does support Multiline

Dim StatisticsTextBox As Object
Set StatisticsTextBox =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", _
Left:=200, Top:=100, Width:=80, Height:=32)
StatisticsTextBox .Object.MultiLine = True



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"eugene" <eugene@discussions.microsoft.com> wrote in message
news:34994476-E53E-43D9-9C5E-7E8327AFF412@microsoft.com...
> > What sort of textbox, control toolbox or userform?
>
> I don't know the difference. But this is how I create my text box:
>
> dim StatisticsTextBox as shape
> Set StatisticsTextBox = ActiveSheet.Shapes.AddTextbox
> _(msoTextOrientationHorizontal, 3, 3, 200, 300)
>
> When I add the following line, I get an error (using Windows XP and Excel
> 2004):
> StatisticsTextBox.MultiLine = True
>
> --
> eugene
>
>
> "Bob Phillips" wrote:
>
> > What sort of textbox, control toolbox or userform? Multiline should
work,
> > and add Alt-Enter, or Chr(10) should give you the breaks.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > news:7B46CF5D-E70B-4842-872B-927B44C01C04@microsoft.com...
> > > I am trying to automate the creation of a series of textboxes (one for
> > each
> > > page of my workbook - which consists of many pages) that will grab
data
> > from
> > > cells on different pages of the workbook and display the information
to a
> > > user. This is done interactively. The data in the box has to change
> > > constantly depending on what triggers its display. I got much of this
to
> > work
> > > but I am encountering a few problems, which I suspect are simple, but
> > which
> > > I can't resolve.
> > >
> > > I enter text in excel cells. But sometimes the text is very long and
ought
> > > to be broken down into paragraphs. Is there any way either (1) to
enter
> > the
> > > text with paragraph marks embeded so that when it is dumped into the
text
> > box
> > > it will look formatted or (2) to bring it the text piecemeal (I can do
> > that)
> > > and add the appropriate marks as it is being read into the textbox (I
> > could
> > > add some character to the text that would allow a program to
substitute it
> > > for whatever is necessary.)
> > >
> > > I have tried a number of things but none seem to work. It may be that
the
> > > root of my problem is that I do not have the text box set to
mulitline. I
> > > can't seem to get ".multiline = true" to work (I get a runtime
error).
> > >
> > > I do not want to create each box manually since there will be very
many of
> > > them, and I suspect I will want to change other things as I progres.
> > > Currently I have a macro to do some of that. But I can't seem to get
all
> > the
> > > details right.
> > >
> > > There are other such issues. But initially, I would greatly
appreciate
> > help
> > > in resolving the particular ones mentioned.
> > >
> > > Thanks.
> > >
> >
> >
> >

Thanks for the Alt-Enter suggestion. I just tried it (added it directly to
the Excel cell). That worked fine even without doing anything about
multiline.

That solves my immediate problem. But I still would appreciate a response
to the multiline issue since I am obviously doing something wrong, and it
seems to be affecting my ability to do other things as well.

It may help to know that an attempt to add vbCrLf to the end of chunks of
text also created much confusion. Excel couldn't handle my code. (I wonder
why!)

This works when I write chunk using (myshape).Insert String:=chunk in a loop
that increments i by 250 (so that I can overcome the 255 character
limitation).
chunk = Mid(textBoxText, i, 250)

When I said:
chunk = Mid(textBoxText, i, 250) & vbCrLf
I think nothing different happened.

But when I added two more & vbCrLf's to the line, it only printed one chunk
and stopped.


--
eugene


"Bob Phillips" wrote:

> What sort of textbox, control toolbox or userform? Multiline should work,
> and add Alt-Enter, or Chr(10) should give you the breaks.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "eugene" <eugene@discussions.microsoft.com> wrote in message
> news:7B46CF5D-E70B-4842-872B-927B44C01C04@microsoft.com...
> > I am trying to automate the creation of a series of textboxes (one for
> each
> > page of my workbook - which consists of many pages) that will grab data
> from
> > cells on different pages of the workbook and display the information to a
> > user. This is done interactively. The data in the box has to change
> > constantly depending on what triggers its display. I got much of this to
> work
> > but I am encountering a few problems, which I suspect are simple, but
> which
> > I can't resolve.
> >
> > I enter text in excel cells. But sometimes the text is very long and ought
> > to be broken down into paragraphs. Is there any way either (1) to enter
> the
> > text with paragraph marks embeded so that when it is dumped into the text
> box
> > it will look formatted or (2) to bring it the text piecemeal (I can do
> that)
> > and add the appropriate marks as it is being read into the textbox (I
> could
> > add some character to the text that would allow a program to substitute it
> > for whatever is necessary.)
> >
> > I have tried a number of things but none seem to work. It may be that the
> > root of my problem is that I do not have the text box set to mulitline. I
> > can't seem to get ".multiline = true" to work (I get a runtime error).
> >
> > I do not want to create each box manually since there will be very many of
> > them, and I suspect I will want to change other things as I progres.
> > Currently I have a macro to do some of that. But I can't seem to get all
> the
> > details right.
> >
> > There are other such issues. But initially, I would greatly appreciate
> help
> > in resolving the particular ones mentioned.
> >
> > Thanks.
> >
>
>
>

Thanks a million.

That worked (after deleting the space before between the "." and the word
Object). I suppose that this will help me for most if not all of the other
issues I have encountered. So I appreciate the help immensely. It's a real
time-saver.

But of course every solution comes with its own problems. I now have to see
if all my other code, which referenced the text box using Shapes, will
continue to work. So I may be back.

Rather than constantly pester this group, does anyone know where on the
Internet I can find out more about the difference between what I was doing
and what Bob suggested. The two are obviously fundamentally different and it
would obviously pay for me to understand the difference.


--
eugene


"Bob Phillips" wrote:

> Thta's what I feraed.
>
> Try creating a controls toolbox textbox which does support Multiline
>
> Dim StatisticsTextBox As Object
> Set StatisticsTextBox =
> ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", _
> Left:=200, Top:=100, Width:=80, Height:=32)
> StatisticsTextBox .Object.MultiLine = True
>
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "eugene" <eugene@discussions.microsoft.com> wrote in message
> news:34994476-E53E-43D9-9C5E-7E8327AFF412@microsoft.com...
> > > What sort of textbox, control toolbox or userform?
> >
> > I don't know the difference. But this is how I create my text box:
> >
> > dim StatisticsTextBox as shape
> > Set StatisticsTextBox = ActiveSheet.Shapes.AddTextbox
> > _(msoTextOrientationHorizontal, 3, 3, 200, 300)
> >
> > When I add the following line, I get an error (using Windows XP and Excel
> > 2004):
> > StatisticsTextBox.MultiLine = True
> >
> > --
> > eugene
> >
> >
> > "Bob Phillips" wrote:
> >
> > > What sort of textbox, control toolbox or userform? Multiline should
> work,
> > > and add Alt-Enter, or Chr(10) should give you the breaks.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > > news:7B46CF5D-E70B-4842-872B-927B44C01C04@microsoft.com...
> > > > I am trying to automate the creation of a series of textboxes (one for
> > > each
> > > > page of my workbook - which consists of many pages) that will grab
> data
> > > from
> > > > cells on different pages of the workbook and display the information
> to a
> > > > user. This is done interactively. The data in the box has to change
> > > > constantly depending on what triggers its display. I got much of this
> to
> > > work
> > > > but I am encountering a few problems, which I suspect are simple, but
> > > which
> > > > I can't resolve.
> > > >
> > > > I enter text in excel cells. But sometimes the text is very long and
> ought
> > > > to be broken down into paragraphs. Is there any way either (1) to
> enter
> > > the
> > > > text with paragraph marks embeded so that when it is dumped into the
> text
> > > box
> > > > it will look formatted or (2) to bring it the text piecemeal (I can do
> > > that)
> > > > and add the appropriate marks as it is being read into the textbox (I
> > > could
> > > > add some character to the text that would allow a program to
> substitute it
> > > > for whatever is necessary.)
> > > >
> > > > I have tried a number of things but none seem to work. It may be that
> the
> > > > root of my problem is that I do not have the text box set to
> mulitline. I
> > > > can't seem to get ".multiline = true" to work (I get a runtime
> error).
> > > >
> > > > I do not want to create each box manually since there will be very
> many of
> > > > them, and I suspect I will want to change other things as I progres.
> > > > Currently I have a macro to do some of that. But I can't seem to get
> all
> > > the
> > > > details right.
> > > >
> > > > There are other such issues. But initially, I would greatly
> appreciate
> > > help
> > > > in resolving the particular ones mentioned.
> > > >
> > > > Thanks.
> > > >
> > >
> > >
> > >
>
>
>

You won't get a definitive answer to that question unless you ask it in a
forum. There will be lots of stuff out there that tell you that, but unless
you know what you are looking for it is difficult to track down. It is a
question of getting an understanding of what each object does, what its
properties are, etc. Experience!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"eugene" <eugene@discussions.microsoft.com> wrote in message
news:F86B9A8F-9039-469D-9978-270C4EE2E46A@microsoft.com...
> Thanks a million.
>
> That worked (after deleting the space before between the "." and the word
> Object). I suppose that this will help me for most if not all of the other
> issues I have encountered. So I appreciate the help immensely. It's a real
> time-saver.
>
> But of course every solution comes with its own problems. I now have to
see
> if all my other code, which referenced the text box using Shapes, will
> continue to work. So I may be back.
>
> Rather than constantly pester this group, does anyone know where on the
> Internet I can find out more about the difference between what I was doing
> and what Bob suggested. The two are obviously fundamentally different and
it
> would obviously pay for me to understand the difference.
>
>
> --
> eugene
>
>
> "Bob Phillips" wrote:
>
> > Thta's what I feraed.
> >
> > Try creating a controls toolbox textbox which does support Multiline
> >
> > Dim StatisticsTextBox As Object
> > Set StatisticsTextBox =
> > ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", _
> > Left:=200, Top:=100, Width:=80, Height:=32)
> > StatisticsTextBox .Object.MultiLine = True
> >
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > news:34994476-E53E-43D9-9C5E-7E8327AFF412@microsoft.com...
> > > > What sort of textbox, control toolbox or userform?
> > >
> > > I don't know the difference. But this is how I create my text box:
> > >
> > > dim StatisticsTextBox as shape
> > > Set StatisticsTextBox = ActiveSheet.Shapes.AddTextbox
> > > _(msoTextOrientationHorizontal, 3, 3, 200, 300)
> > >
> > > When I add the following line, I get an error (using Windows XP and
Excel
> > > 2004):
> > > StatisticsTextBox.MultiLine = True
> > >
> > > --
> > > eugene
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > What sort of textbox, control toolbox or userform? Multiline should
> > work,
> > > > and add Alt-Enter, or Chr(10) should give you the breaks.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > > > news:7B46CF5D-E70B-4842-872B-927B44C01C04@microsoft.com...
> > > > > I am trying to automate the creation of a series of textboxes (one
for
> > > > each
> > > > > page of my workbook - which consists of many pages) that will grab
> > data
> > > > from
> > > > > cells on different pages of the workbook and display the
information
> > to a
> > > > > user. This is done interactively. The data in the box has to
change
> > > > > constantly depending on what triggers its display. I got much of
this
> > to
> > > > work
> > > > > but I am encountering a few problems, which I suspect are simple,
but
> > > > which
> > > > > I can't resolve.
> > > > >
> > > > > I enter text in excel cells. But sometimes the text is very long
and
> > ought
> > > > > to be broken down into paragraphs. Is there any way either (1) to
> > enter
> > > > the
> > > > > text with paragraph marks embeded so that when it is dumped into
the
> > text
> > > > box
> > > > > it will look formatted or (2) to bring it the text piecemeal (I
can do
> > > > that)
> > > > > and add the appropriate marks as it is being read into the textbox
(I
> > > > could
> > > > > add some character to the text that would allow a program to
> > substitute it
> > > > > for whatever is necessary.)
> > > > >
> > > > > I have tried a number of things but none seem to work. It may be
that
> > the
> > > > > root of my problem is that I do not have the text box set to
> > mulitline. I
> > > > > can't seem to get ".multiline = true" to work (I get a runtime
> > error).
> > > > >
> > > > > I do not want to create each box manually since there will be very
> > many of
> > > > > them, and I suspect I will want to change other things as I
progres.
> > > > > Currently I have a macro to do some of that. But I can't seem to
get
> > all
> > > > the
> > > > > details right.
> > > > >
> > > > > There are other such issues. But initially, I would greatly
> > appreciate
> > > > help
> > > > > in resolving the particular ones mentioned.
> > > > >
> > > > > Thanks.
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Oh boy!

Of course, I have encountered a problems with the code that has been
working until now.

In my original code (using shapes), I named my textboxes as follows:
TBName = "Statistics for Page " & ActiveSheet.Name
StatisticsTextBox.Name = TBName

(Objective here was to name the textbox on each sheet in a way that would
allow me reference each simply by using the sheet name.)
This seems still to work with the new code - the text box takes the name -
and I can still do some things using .Shapes(TBName) - eg deleting the text
box.

But I can't seem to write to the box.
Orignally, when writing to a text box, I used two commands, both of which
worked fine (I believe):
Sheets(...).Select
TBName = "Statistics for Page " & ActiveSheet.Name
ActiveSheet.Shapes(TBName).OLEFormat.Object.Text = "..." (or some string
variable)
or
ActiveSheet.Shapes(TBName).TextFrame.Characters.Text = "..."

Now neither seems to work.

Help!

If there is no generic solution to this sort of question, you won't hear the
end of me!

Thanks again.


--
eugene


"Bob Phillips" wrote:

> You won't get a definitive answer to that question unless you ask it in a
> forum. There will be lots of stuff out there that tell you that, but unless
> you know what you are looking for it is difficult to track down. It is a
> question of getting an understanding of what each object does, what its
> properties are, etc. Experience!
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "eugene" <eugene@discussions.microsoft.com> wrote in message
> news:F86B9A8F-9039-469D-9978-270C4EE2E46A@microsoft.com...
> > Thanks a million.
> >
> > That worked (after deleting the space before between the "." and the word
> > Object). I suppose that this will help me for most if not all of the other
> > issues I have encountered. So I appreciate the help immensely. It's a real
> > time-saver.
> >
> > But of course every solution comes with its own problems. I now have to
> see
> > if all my other code, which referenced the text box using Shapes, will
> > continue to work. So I may be back.
> >
> > Rather than constantly pester this group, does anyone know where on the
> > Internet I can find out more about the difference between what I was doing
> > and what Bob suggested. The two are obviously fundamentally different and
> it
> > would obviously pay for me to understand the difference.
> >
> >
> > --
> > eugene
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Thta's what I feraed.
> > >
> > > Try creating a controls toolbox textbox which does support Multiline
> > >
> > > Dim StatisticsTextBox As Object
> > > Set StatisticsTextBox =
> > > ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", _
> > > Left:=200, Top:=100, Width:=80, Height:=32)
> > > StatisticsTextBox .Object.MultiLine = True
> > >
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > > news:34994476-E53E-43D9-9C5E-7E8327AFF412@microsoft.com...
> > > > > What sort of textbox, control toolbox or userform?
> > > >
> > > > I don't know the difference. But this is how I create my text box:
> > > >
> > > > dim StatisticsTextBox as shape
> > > > Set StatisticsTextBox = ActiveSheet.Shapes.AddTextbox
> > > > _(msoTextOrientationHorizontal, 3, 3, 200, 300)
> > > >
> > > > When I add the following line, I get an error (using Windows XP and
> Excel
> > > > 2004):
> > > > StatisticsTextBox.MultiLine = True
> > > >
> > > > --
> > > > eugene
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > What sort of textbox, control toolbox or userform? Multiline should
> > > work,
> > > > > and add Alt-Enter, or Chr(10) should give you the breaks.
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (remove nothere from email address if mailing direct)
> > > > >
> > > > > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > > > > news:7B46CF5D-E70B-4842-872B-927B44C01C04@microsoft.com...
> > > > > > I am trying to automate the creation of a series of textboxes (one
> for
> > > > > each
> > > > > > page of my workbook - which consists of many pages) that will grab
> > > data
> > > > > from
> > > > > > cells on different pages of the workbook and display the
> information
> > > to a
> > > > > > user. This is done interactively. The data in the box has to
> change
> > > > > > constantly depending on what triggers its display. I got much of
> this
> > > to
> > > > > work
> > > > > > but I am encountering a few problems, which I suspect are simple,
> but
> > > > > which
> > > > > > I can't resolve.
> > > > > >
> > > > > > I enter text in excel cells. But sometimes the text is very long
> and
> > > ought
> > > > > > to be broken down into paragraphs. Is there any way either (1) to
> > > enter
> > > > > the
> > > > > > text with paragraph marks embeded so that when it is dumped into
> the
> > > text
> > > > > box
> > > > > > it will look formatted or (2) to bring it the text piecemeal (I
> can do
> > > > > that)
> > > > > > and add the appropriate marks as it is being read into the textbox
> (I
> > > > > could
> > > > > > add some character to the text that would allow a program to
> > > substitute it
> > > > > > for whatever is necessary.)
> > > > > >
> > > > > > I have tried a number of things but none seem to work. It may be
> that
> > > the
> > > > > > root of my problem is that I do not have the text box set to
> > > mulitline. I
> > > > > > can't seem to get ".multiline = true" to work (I get a runtime
> > > error).
> > > > > >
> > > > > > I do not want to create each box manually since there will be very
> > > many of
> > > > > > them, and I suspect I will want to change other things as I
> progres.
> > > > > > Currently I have a macro to do some of that. But I can't seem to
> get
> > > all
> > > > > the
> > > > > > details right.
> > > > > >
> > > > > > There are other such issues. But initially, I would greatly
> > > appreciate
> > > > > help
> > > > > > in resolving the particular ones mentioned.
> > > > > >
> > > > > > Thanks.
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

You can set the name and use that, but it is better IMO just to use the
object that you created when creating the textbox

Dim StatisticsTextBox As Object
Set StatisticsTextBox = ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.TextBox.1", _
Left:=200, Top:=100, Width:=80, Height:=32)
With StatisticsTextBox
.Name = "TB1"
With .Object
.MultiLine = True
.Text = "Hello"
End With
End With

and use StatisticsTextBox if you want to use it again later.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"eugene" <eugene@discussions.microsoft.com> wrote in message
news:3B83CCEE-9A9C-43DD-BE76-0166C23ACD35@microsoft.com...
> Oh boy!
>
> Of course, I have encountered a problems with the code that has been
> working until now.
>
> In my original code (using shapes), I named my textboxes as follows:
> TBName = "Statistics for Page " & ActiveSheet.Name
> StatisticsTextBox.Name = TBName
>
> (Objective here was to name the textbox on each sheet in a way that would
> allow me reference each simply by using the sheet name.)
> This seems still to work with the new code - the text box takes the name -
> and I can still do some things using .Shapes(TBName) - eg deleting the
text
> box.
>
> But I can't seem to write to the box.
> Orignally, when writing to a text box, I used two commands, both of which
> worked fine (I believe):
> Sheets(...).Select
> TBName = "Statistics for Page " & ActiveSheet.Name
> ActiveSheet.Shapes(TBName).OLEFormat.Object.Text = "..." (or some string
> variable)
> or
> ActiveSheet.Shapes(TBName).TextFrame.Characters.Text = "..."
>
> Now neither seems to work.
>
> Help!
>
> If there is no generic solution to this sort of question, you won't hear
the
> end of me!
>
> Thanks again.
>
>
> --
> eugene
>
>
> "Bob Phillips" wrote:
>
> > You won't get a definitive answer to that question unless you ask it in
a
> > forum. There will be lots of stuff out there that tell you that, but
unless
> > you know what you are looking for it is difficult to track down. It is a
> > question of getting an understanding of what each object does, what its
> > properties are, etc. Experience!
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > news:F86B9A8F-9039-469D-9978-270C4EE2E46A@microsoft.com...
> > > Thanks a million.
> > >
> > > That worked (after deleting the space before between the "." and the
word
> > > Object). I suppose that this will help me for most if not all of the
other
> > > issues I have encountered. So I appreciate the help immensely. It's a
real
> > > time-saver.
> > >
> > > But of course every solution comes with its own problems. I now have
to
> > see
> > > if all my other code, which referenced the text box using Shapes,
will
> > > continue to work. So I may be back.
> > >
> > > Rather than constantly pester this group, does anyone know where on
the
> > > Internet I can find out more about the difference between what I was
doing
> > > and what Bob suggested. The two are obviously fundamentally different
and
> > it
> > > would obviously pay for me to understand the difference.
> > >
> > >
> > > --
> > > eugene
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Thta's what I feraed.
> > > >
> > > > Try creating a controls toolbox textbox which does support Multiline
> > > >
> > > > Dim StatisticsTextBox As Object
> > > > Set StatisticsTextBox =
> > > > ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", _
> > > > Left:=200, Top:=100, Width:=80, Height:=32)
> > > > StatisticsTextBox .Object.MultiLine = True
> > > >
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > > > news:34994476-E53E-43D9-9C5E-7E8327AFF412@microsoft.com...
> > > > > > What sort of textbox, control toolbox or userform?
> > > > >
> > > > > I don't know the difference. But this is how I create my text box:
> > > > >
> > > > > dim StatisticsTextBox as shape
> > > > > Set StatisticsTextBox = ActiveSheet.Shapes.AddTextbox
> > > > > _(msoTextOrientationHorizontal, 3, 3, 200, 300)
> > > > >
> > > > > When I add the following line, I get an error (using Windows XP
and
> > Excel
> > > > > 2004):
> > > > > StatisticsTextBox.MultiLine = True
> > > > >
> > > > > --
> > > > > eugene
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > What sort of textbox, control toolbox or userform? Multiline
should
> > > > work,
> > > > > > and add Alt-Enter, or Chr(10) should give you the breaks.
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (remove nothere from email address if mailing direct)
> > > > > >
> > > > > > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > > > > > news:7B46CF5D-E70B-4842-872B-927B44C01C04@microsoft.com...
> > > > > > > I am trying to automate the creation of a series of textboxes
(one
> > for
> > > > > > each
> > > > > > > page of my workbook - which consists of many pages) that will
grab
> > > > data
> > > > > > from
> > > > > > > cells on different pages of the workbook and display the
> > information
> > > > to a
> > > > > > > user. This is done interactively. The data in the box has to
> > change
> > > > > > > constantly depending on what triggers its display. I got much
of
> > this
> > > > to
> > > > > > work
> > > > > > > but I am encountering a few problems, which I suspect are
simple,
> > but
> > > > > > which
> > > > > > > I can't resolve.
> > > > > > >
> > > > > > > I enter text in excel cells. But sometimes the text is very
long
> > and
> > > > ought
> > > > > > > to be broken down into paragraphs. Is there any way either (1)
to
> > > > enter
> > > > > > the
> > > > > > > text with paragraph marks embeded so that when it is dumped
into
> > the
> > > > text
> > > > > > box
> > > > > > > it will look formatted or (2) to bring it the text piecemeal
(I
> > can do
> > > > > > that)
> > > > > > > and add the appropriate marks as it is being read into the
textbox
> > (I
> > > > > > could
> > > > > > > add some character to the text that would allow a program to
> > > > substitute it
> > > > > > > for whatever is necessary.)
> > > > > > >
> > > > > > > I have tried a number of things but none seem to work. It may
be
> > that
> > > > the
> > > > > > > root of my problem is that I do not have the text box set to
> > > > mulitline. I
> > > > > > > can't seem to get ".multiline = true" to work (I get a
runtime
> > > > error).
> > > > > > >
> > > > > > > I do not want to create each box manually since there will be
very
> > > > many of
> > > > > > > them, and I suspect I will want to change other things as I
> > progres.
> > > > > > > Currently I have a macro to do some of that. But I can't seem
to
> > get
> > > > all
> > > > > > the
> > > > > > > details right.
> > > > > > >
> > > > > > > There are other such issues. But initially, I would greatly
> > > > appreciate
> > > > > > help
> > > > > > > in resolving the particular ones mentioned.
> > > > > > >
> > > > > > > Thanks.
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

I assume you are saying that if there are two pages each with a
StatisticsTextBox that there will be no conflict since I will be using
activesheet.stat.... to access each one. No need to reply if I understand you
correctly.

BTW. I searched and found the following line for dumping text into the
textbox. It works for even more than 255 characters at a time (my shape
version did not).

ActiveSheet.OLEObjects(TBName).Object.Value = textBoxText

Thanks for your help. I hope that's it for a while at least.


--
eugene


"Bob Phillips" wrote:

> You can set the name and use that, but it is better IMO just to use the
> object that you created when creating the textbox
>
> Dim StatisticsTextBox As Object
> Set StatisticsTextBox = ActiveSheet.OLEObjects.Add( _
> ClassType:="Forms.TextBox.1", _
> Left:=200, Top:=100, Width:=80, Height:=32)
> With StatisticsTextBox
> .Name = "TB1"
> With .Object
> .MultiLine = True
> .Text = "Hello"
> End With
> End With
>
> and use StatisticsTextBox if you want to use it again later.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "eugene" <eugene@discussions.microsoft.com> wrote in message
> news:3B83CCEE-9A9C-43DD-BE76-0166C23ACD35@microsoft.com...
> > Oh boy!
> >
> > Of course, I have encountered a problems with the code that has been
> > working until now.
> >
> > In my original code (using shapes), I named my textboxes as follows:
> > TBName = "Statistics for Page " & ActiveSheet.Name
> > StatisticsTextBox.Name = TBName
> >
> > (Objective here was to name the textbox on each sheet in a way that would
> > allow me reference each simply by using the sheet name.)
> > This seems still to work with the new code - the text box takes the name -
> > and I can still do some things using .Shapes(TBName) - eg deleting the
> text
> > box.
> >
> > But I can't seem to write to the box.
> > Orignally, when writing to a text box, I used two commands, both of which
> > worked fine (I believe):
> > Sheets(...).Select
> > TBName = "Statistics for Page " & ActiveSheet.Name
> > ActiveSheet.Shapes(TBName).OLEFormat.Object.Text = "..." (or some string
> > variable)
> > or
> > ActiveSheet.Shapes(TBName).TextFrame.Characters.Text = "..."
> >
> > Now neither seems to work.
> >
> > Help!
> >
> > If there is no generic solution to this sort of question, you won't hear
> the
> > end of me!
> >
> > Thanks again.
> >
> >
> > --
> > eugene
> >
> >
> > "Bob Phillips" wrote:
> >
> > > You won't get a definitive answer to that question unless you ask it in
> a
> > > forum. There will be lots of stuff out there that tell you that, but
> unless
> > > you know what you are looking for it is difficult to track down. It is a
> > > question of getting an understanding of what each object does, what its
> > > properties are, etc. Experience!
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > > news:F86B9A8F-9039-469D-9978-270C4EE2E46A@microsoft.com...
> > > > Thanks a million.
> > > >
> > > > That worked (after deleting the space before between the "." and the
> word
> > > > Object). I suppose that this will help me for most if not all of the
> other
> > > > issues I have encountered. So I appreciate the help immensely. It's a
> real
> > > > time-saver.
> > > >
> > > > But of course every solution comes with its own problems. I now have
> to
> > > see
> > > > if all my other code, which referenced the text box using Shapes,
> will
> > > > continue to work. So I may be back.
> > > >
> > > > Rather than constantly pester this group, does anyone know where on
> the
> > > > Internet I can find out more about the difference between what I was
> doing
> > > > and what Bob suggested. The two are obviously fundamentally different
> and
> > > it
> > > > would obviously pay for me to understand the difference.
> > > >
> > > >
> > > > --
> > > > eugene
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Thta's what I feraed.
> > > > >
> > > > > Try creating a controls toolbox textbox which does support Multiline
> > > > >
> > > > > Dim StatisticsTextBox As Object
> > > > > Set StatisticsTextBox =
> > > > > ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", _
> > > > > Left:=200, Top:=100, Width:=80, Height:=32)
> > > > > StatisticsTextBox .Object.MultiLine = True
> > > > >
> > > > >
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (remove nothere from email address if mailing direct)
> > > > >
> > > > > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > > > > news:34994476-E53E-43D9-9C5E-7E8327AFF412@microsoft.com...
> > > > > > > What sort of textbox, control toolbox or userform?
> > > > > >
> > > > > > I don't know the difference. But this is how I create my text box:
> > > > > >
> > > > > > dim StatisticsTextBox as shape
> > > > > > Set StatisticsTextBox = ActiveSheet.Shapes.AddTextbox
> > > > > > _(msoTextOrientationHorizontal, 3, 3, 200, 300)
> > > > > >
> > > > > > When I add the following line, I get an error (using Windows XP
> and
> > > Excel
> > > > > > 2004):
> > > > > > StatisticsTextBox.MultiLine = True
> > > > > >
> > > > > > --
> > > > > > eugene
> > > > > >
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > What sort of textbox, control toolbox or userform? Multiline
> should
> > > > > work,
> > > > > > > and add Alt-Enter, or Chr(10) should give you the breaks.
> > > > > > >
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > Bob Phillips
> > > > > > >
> > > > > > > (remove nothere from email address if mailing direct)
> > > > > > >
> > > > > > > "eugene" <eugene@discussions.microsoft.com> wrote in message
> > > > > > > news:7B46CF5D-E70B-4842-872B-927B44C01C04@microsoft.com...
> > > > > > > > I am trying to automate the creation of a series of textboxes
> (one
> > > for
> > > > > > > each
> > > > > > > > page of my workbook - which consists of many pages) that will
> grab
> > > > > data
> > > > > > > from
> > > > > > > > cells on different pages of the workbook and display the
> > > information
> > > > > to a
> > > > > > > > user. This is done interactively. The data in the box has to
> > > change
> > > > > > > > constantly depending on what triggers its display. I got much
> of
> > > this
> > > > > to
> > > > > > > work
> > > > > > > > but I am encountering a few problems, which I suspect are
> simple,
> > > but
> > > > > > > which
> > > > > > > > I can't resolve.
> > > > > > > >
> > > > > > > > I enter text in excel cells. But sometimes the text is very
> long
> > > and
> > > > > ought
> > > > > > > > to be broken down into paragraphs. Is there any way either (1)
> to
> > > > > enter
> > > > > > > the
> > > > > > > > text with paragraph marks embeded so that when it is dumped
> into
> > > the
> > > > > text
> > > > > > > box
> > > > > > > > it will look formatted or (2) to bring it the text piecemeal
> (I
> > > can do
> > > > > > > that)
> > > > > > > > and add the appropriate marks as it is being read into the
> textbox
> > > (I
> > > > > > > could
> > > > > > > > add some character to the text that would allow a program to
> > > > > substitute it
> > > > > > > > for whatever is necessary.)
> > > > > > > >
> > > > > > > > I have tried a number of things but none seem to work. It may
> be
> > > that
> > > > > the
> > > > > > > > root of my problem is that I do not have the text box set to
> > > > > mulitline. I
> > > > > > > > can't seem to get ".multiline = true" to work (I get a
> runtime
> > > > > error).
> > > > > > > >
> > > > > > > > I do not want to create each box manually since there will be
> very
> > > > > many of
> > > > > > > > them, and I suspect I will want to change other things as I
> > > progres.
> > > > > > > > Currently I have a macro to do some of that. But I can't seem
> to
> > > get
> > > > > all
> > > > > > > the
> > > > > > > > details right.
> > > > > > > >
> > > > > > > > There are other such issues. But initially, I would greatly
> > > > > appreciate
> > > > > > > help
> > > > > > > > in resolving the particular ones mentioned.
> > > > > > > >
> > > > > > > > Thanks.
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

"eugene" <eugene@discussions.microsoft.com> wrote in message
news:A09C6B33-90A6-423F-A5A7-672E60C652C1@microsoft.com...
> I assume you are saying that if there are two pages each with a
> StatisticsTextBox that there will be no conflict since I will be using
> activesheet.stat.... to access each one. No need to reply if I understand
you
> correctly.

Yes, but I would access via the sheet name not activesheet, safer.

> BTW. I searched and found the following line for dumping text into the
> textbox. It works for even more than 255 characters at a time (my shape
> version did not).
>
> ActiveSheet.OLEObjects(TBName).Object.Value = textBoxText

That's essentially the same as I gave you.

> Yes, but I would access via the sheet name not activesheet, safer.

Great.

> That's essentially the same as I gave you.

I realized that after I responded. My mind doesn't work well with "with"s.

Thanks again.

> Yes, but I would access via the sheet name not activesheet, safer.

Great.

> That's essentially the same as I gave you.

I realized that after I responded. My mind doesn't work well with "with"s.

Thanks again.