Data from sheet, through userform to text box on the sheet.

[Lucas]

Hello all,

I wrote program which uses data from sheet, than desplays data on the
userform and then prints data. User has to point which data should be
printed, data is transfered to text boxes on the particular sheet and then
printed. The point is that some data can't be put into textboxes on sheet
using vb. Ofcourse manually it is not a problem.

code that transfers data from userform to sheet looks like this:

info = UserForm5.TextBox1.Value

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, Left1, top1,
Width1, height_box).Select

Selection.Characters.Text = info

For some reasons in some cases code works just fine and in some it doesen't.
At the begining i thought that it is connected with number of characters
that can be placed in text box but then, if the number of characters would be
to big, there will be no possibility of placing them manually. I'm sure that
data is every time in 'info' variable. Any ideas.

Thanks in advance.

 

[NickH]

Hi Lucas

I don't have enough information from your message to determine what is wrong.

When you say "some cases it works and some it doesn't", what is the nature
of the failure? Do you get an error; is the text missing from the added
textbox; is the textbox actually added at all?

You can insert the text in a single statement like this:

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 10, 10, 50,
30).TextFrame.Characters.Text = "1234"

You can also do it like this:

Dim sp As Shape
Set sp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 30, 20,
40, 20)
With sp
.Name = "myBox"
.TextFrame.Characters.Text = "1234"
End With

The advantage here is that you know exacly which shape you are manipulating
and you can name it as you want.

If this doesn't help, then please provide more details about the problem.

 

[Lucas]

Than you for your reply NickH.

More info.

The problem is that data isn't placed in new text box on sheet. Ofcourse
textbox is created, everything about it is ok (format, lines, font) excapt
this that data isn't placed in it.

I use variable called "info" to transfer data from user form text box to
sheet text box. During code running i can see that variable info has proper
data but after executing:

Selection.Characters.Text = info or as you wrote (except use of variable
"info"):

Dim sp As Shape
Set sp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 30, 20,
40, 20)
With sp
.Name = "myBox"
.TextFrame.Characters.Text = info
End With

there is no data in text box in the sheet. There is no error info, code just
runs throught these lines bot there is no text in tb.

I noticed that there is no problem when there is less characters placed in
tb in sheet.
Then code works just fine.

I hope it helped.

Regards

Lucas




„NickH†pisze:

 

[NickH]

Hi Lucas

You say that code running, you can see values in your variable, but these
are not necessarily transferred to the text box. Is there anything specific
about the values themselves (e.g. special characters, control codes etc.)
which might affect the end result?

One thing I noticed when testing my code is that if the textbox is too small
to show the full text, then the box appears to be empty on the dispaly. Try
dragging it to a larger size for the problem values to see if this makes a
difference (or make the box very large in the first place). If this has any
effect, then you can manipulate the
textbox characteristics programmatically to suit.

You already discounted the maximum limit being a factor because you can
enter the requred data manually, so I'm ignoring that as a possibility.

 

[OssieMac]

Hi Lucas,

Not sure if any of this will help but I'll make the suggestions anyway.

First of all I suggest that you use Nick's code method but perhaps with a
modification of using the sheet name in lieu of ActiveSheet. That way you do
not have to ensure that the sheet is the selected sheet.

Next try saving the data to info after creating the text box.

If still failing insert a MsgBox to display the length of the info string
and that will isolate the problem to either info is not accepting the data or
the TextBox is not accepting the data.

I was not able to get either your code or Nick's code to fail and I tested
with up to 1,500 characters so also give some thought to Nick's questions in
his last post.

Dim sp As Shape
Dim info As String

Set sp = Sheets("Sheet1").Shapes.AddTextbox _
(msoTextOrientationHorizontal, _
left1, Top1, Width1, Height_box)

'Save data to info after creating the TextBox
info = UserForm1.TextBox1.Value

MsgBox Len(info) 'Check that info has a string

With sp
.Name = "myBox"
.TextFrame.Characters.Text = info
End With