Dynamically write VBA code

[phagan]

I have looked all over the internet and I can't seem to find this assistance.

I am trying to write some vba code that adds controls to the user form with a click of the button. At the moment I can add the controls just fine with

Option Explicit
Dim CountPartNumber As Integer
Dim PartNumberTop As Long
Private Sub CommandButton1_Click()
CountPartNumber = CountPartNumber + 1
PartNumberTop = PartNumberTop + 24
Me.Controls.Add "Forms.textbox.1", "HeatNumber" & CountPartNumber
With Controls("HeatNumber" & CountPartNumber)
    .Top = PartNumberTop
    .Left = HeatLeft
    .Width = HeatWidth
End With

separate sub:

sub Private Sub UserForm_Initialize()
......
CountPartNumber = 1
PartNumberTop = 30
........

What I need is while the controls are created I want to write the following code to the project for each textbox created

.value = StrConv(.value, vbUpperCase)

or something like it.

I would write a function for it but I can't seem to figure that one out either.

Thanks in advance.

 

[GTO]

Not saying that is cannot be done (it can), but why? Just to learn or actually use the form? If to actually use, IMO, better to create the controls in design time. Hide them, resize the form conditionally, etc.

 

[phagan]

A little of both actually. I have a separate form where I do the design time and hide at run and unhide as I need them. But I had some idea of what the maximum amount of controls I would need then is. Now it could be as much as 100, the little bit of code I gave is for one control. In actually that one is a series of 9 controls. Of which that one was the easiest and fastest to write up. some of the other controls would need code attached to them as well but the rest are just informational.

This is really part of a log of some of the work the people do around here and an easy way to control what information gets stared where so that I can programatically use the information later.

 

[GTO]

Hi Phagan,

I'm off for a bit, but hope to check back in or that others jump in. Please do not take my comments or questions as to dissuade you outright, but more at making sure that its not just a bunch of work before discovering no advantage. If there is an advantage, by all means...

Okay, here's what I would think of as immediete concerns:

1. IF I am correct in assuming that each series of controls would be added at a time, and that these could be layed out on a 'row' so to speak, this means about 11 rows. If there is no limit to how many series can be added, you'll get a call that starts something like, "I pushed the button and now the top and bottom of the form are past the screen!"
2. As you mentioned, your example gave but the simplest example of what code might be needed for the added control. But after converting to UPPER, obviously we are planning on actually doing something with the text. Can we logically add different code to each control based on where it is in the array of controls?

Mark

 

[phagan]

Mark, you answers:

1 I'm not so concerned with what happens to the top of the form as long as the bottom and the corresponding buttons are still visible. This could eventually be an issue but I don't believe it would be. The other issue is that if I developed the form so it works on my monitor the persons actually using it will probably have a smaller monitor and so it wouldn't work for them.

2 A couple of the controls will need the convert to upper put on them but that is because that data will always need to be uppercase but yes there are a couple of the controls that will be using a query from a different spreadsheet to fill out that data, but after all the controls are filled out there will just be put into a log format spreadsheet so we can pull data from other sources.

The reason I was trying to do it this way also, was that I believe I read some place that every control allocates a certain amount of memory when it is created. so in an effort to keep memory usage down I would just create the amount of controls I need.