Adding Controls to Pages programatically

I am using this code to add Controls to My User Form "PivotTableOptions"

[VBA]LC = "ItemCode"
NO = "NoItemNumber"
SBI = "SortbyItem"
SBP = "SortbyProduct"
PivotTableOptions.Controls.Add bstrProgId:="Forms.Frame.1", Name:=LC, Visible:=True
PivotTableOptions.Controls(LC).Top = 168
PivotTableOptions.Controls(LC).Left = 2
PivotTableOptions.Controls(LC).Height = 70
PivotTableOptions.Controls(LC).Width = 210
PivotTableOptions.Controls(LC).Caption = "Do you want Item# in the Report?"
PivotTableOptions.Controls(LC).Font.Bold = True
PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=SBP, Visible:=True
PivotTableOptions.Controls(SBP).Top = 14
PivotTableOptions.Controls(SBP).Left = 12
PivotTableOptions.Controls(SBP).Height = 14
PivotTableOptions.Controls(SBP).Width = 120
PivotTableOptions.Controls(SBP).Caption = "Yes, Sort by Product"
PivotTableOptions.Controls(SBP).Font.Bold = True
PivotTableOptions.Controls(SBP).GroupName = Itemcode


PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=SBI, Visible:=True
PivotTableOptions.Controls(SBI).Top = 38
PivotTableOptions.Controls(SBI).Left = 12
PivotTableOptions.Controls(SBI).Height = 14
PivotTableOptions.Controls(SBI).Width = 120
PivotTableOptions.Controls(SBI).Caption = "Yes, Sort by Item#"
PivotTableOptions.Controls(SBI).Font.Bold = True
PivotTableOptions.Controls(SBI).GroupName = Itemcode

PivotTableOptions.Controls(LC).Add bstrProgId:="Forms.optionbutton.1", Name:=NO, Visible:=True
PivotTableOptions.Controls(NO).Top = 27
PivotTableOptions.Controls(NO).Left = 140
PivotTableOptions.Controls(NO).Height = 14
PivotTableOptions.Controls(NO).Width = 30
PivotTableOptions.Controls(NO).Caption = "No"
PivotTableOptions.Controls(NO).Font.Bold = True
PivotTableOptions.Controls(NO).Value = True
PivotTableOptions.Controls(NO).GroupName = Itemcode
[/VBA]

However, Now I ran out of room on that form, so I added Pages to it. Now I want to use that code to add it to the page named "ReportSetup". I can't get it to work I thought I would put

[VBA]PivotTableOptions.ReportSetup.Controls[/VBA]

etc.. But that didn't work

Why would you want to add controls via code. It is not a good idea, and incredibly inefficient.

I am adding the controls programatically because they should only appear if the user chooses other options

[VBA]TextBox1.Visible = False[/VBA]

In part, what XLD is referring to here is that just because something CAN be done does not necessarily mean that it SHOULD be done as a matter of course or that it is the best option.

The course that Steve has alluded to would be the better option... You can have almost any number of controls on a form, just set their visible property to false until the conditions are met where you want them shown.

Another alternative would be for the extra controls to be (say) added onto the RHS of a form of width 2 * X, and when the form is activated set the form width as either X, or, 2 * X if your conditions to show the extra controls are met. Or maybe you'd want to show it as width X, and set the width as 2 * X when (say) a button is clicked... :)

Quote:

---

Originally Posted by johnske

In part, what XLD is referring to here is that just because something CAN be done does not necessarily mean that it SHOULD be done as a matter of course or that it is the best option.

The course that Steve has alluded to would be the better option... You can have almost any number of controls on a form, just set their visible property to false until the conditions are met where you want them shown.

Another alternative would be for the extra controls to be (say) added onto the RHS of a form of width 2 * X, and when the form is activated set the form width as either X, or, 2 * X if your conditions to show the extra controls are met. Or maybe you'd want to show it as width X, and set the width as 2 * X when (say) a button is clicked... :)

---

Exactly.

It is far better to have the controls and just tuck them away and bring them into play when required rather than adding them at run time. Remember also that if the control does not exist when the form is designed, there will be no event code for it. You are then into territory of either adding event code on the fly, not trivial with forms, and/or creating pseudo-control arrays. Either way, you are adding another level of complexity to the application which just is not warranted. I think I can safely say that I have NEVER built a commercial application that builds controls on the fly, just not necessary.

Thank you I never thought of that way. That does seem to be more efficent. I never knew about that way. I am learning, be patient with me.

Daniel