Issues With Adding TextBox

[mferrisi]

I'm attempting to create a textbox on the application that the user can enter text into.

Apparently there is no 'xlTextBox' with AddFormControl?

If that is the case, I guess I need to use the OLEObjects?

If I do this

ActiveSheet.OLEObjects.Add(ClassType:="forms.TextBox.1", Link:=False, _
Left:=299.25, Top:=87, Width:=58.5, Height:= _
31.5).Select

I get a "Can't enter break mode at this time" message.

In instances where I create a textbox and select it, I can't figure out how to change the font size and style, and how to rename the box.

Could someone please point me in the right direction?

Thank you very much,

[Bob Phillips]

why bother? create in desiogn mode and hide it until needed.

[mferrisi]

It is not known where the textboxes will need to be placed, as they will be relational to whatever setup the user chooses

[Bob Phillips]

Then just unhide them and move them Still far easier.

[mferrisi]

Right-- But there could be anywhere from 1 to 100000.

[Bob Phillips]

Why? How can you have 100,000 textboxes, that is just ridiculous!

[mferrisi]

Hopefully, that wouldn't happen. But it is user defined....

[Bob Phillips]

Hoipefully! If it took 3 seconds a textbox, that woyld take 3.5 solida days to enter the data. Get real!

If it is that dynamic, I would suggest a userform is the wrong approach. Use a spreadsheet and let them just keep adding in.

[Norie]

mferrisi

I've got to agree with xld, 10,000 textboxes is just overkill.

What is it you are actually trying to achieve?

XLD

I don't actually think the OP is working with a userform, not from the code posted anyway.:)

[Bob Phillips]

Didn't even look at the code Norie, I always advise people to NOT add controls on the fly, no matter how many.

BTW he said 100,000 not 10,000! Maybe a typo, but that is what he said.

[Norie]

xld

I actually typed 100,000 at first but then thought 'that's ridiculous surely the OP said 10,000'.:)

I wouldn't particularly recommend adding controls, especially something like textboxes, on the fly.

There are plenty of other controls that might be used eg combobox, listbox.

But if they would be if any use it's hard to tell without knowinng what the OP is actually trying to do.

[mikerickson]

If you decide to ignore the advice of the people here (including me) that adding controls at run time is not a good way to go

this worked for me

Private Sub UserForm_Initialize()
Me.Controls.Add "Forms.TextBox.1", "myTextBox", True
End Sub

Considering the hassle of creating event routines for each of the created controls, perhaps creating them at design time and useing Visible = T/F would be a better way to go than creating/destroying controls at run time.

(10,000 textboxes?!?? No user has the capability to use that many. I can only fit about 2,000 cells onto the screen. Userforms are to make data entry easier.)

[mdmackillop]

Sub JustForFun()
Set myDocument = Worksheets(1)
For i = 1 To 100000
myDocument.Shapes.AddTextbox msoTextOrientationHorizontal, 0, 10 * i, 200, 8
Next
End Sub


but I only got 83,665 on the page, guess I'll need to upgrade to 2007
BTW it's 17MB with no data.