Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from sheet, through userform to text box on the sheet.
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(msoTextOrientationHo rizontal, 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from sheet, through userform to text box on the sheet.
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(msoTextOrientationHo rizontal, 10, 10, 50, 30).TextFrame.Characters.Text = "1234" You can also do it like this: Dim sp As Shape Set sp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, 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. -- Nick "Lucas" wrote: 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(msoTextOrientationHo rizontal, 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from sheet, through userform to text box on the sheet.
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(msoTextOrientationHo rizontal, 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: 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(msoTextOrientationHo rizontal, 10, 10, 50, 30).TextFrame.Characters.Text = "1234" You can also do it like this: Dim sp As Shape Set sp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, 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. -- Nick "Lucas" wrote: 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(msoTextOrientationHo rizontal, 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from sheet, through userform to text box on the sheet.
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. -- Nick "Lucas" wrote: 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(msoTextOrientationHo rizontal, 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: 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(msoTextOrientationHo rizontal, 10, 10, 50, 30).TextFrame.Characters.Text = "1234" You can also do it like this: Dim sp As Shape Set sp = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, 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. -- Nick "Lucas" wrote: 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(msoTextOrientationHo rizontal, 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data from sheet, through userform to text box on the sheet.
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 -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform - need to access data from a particular sheet | Excel Programming | |||
Update Data On Sheet From UserForm TextBoxes | Excel Programming | |||
Userform Stepping Through Sheet Data | Excel Programming | |||
Need assistance to populate sheet from Userform data | Excel Programming | |||
Userform data to Excel sheet. | Excel Programming |