add controls on the fly/dynamically

[jinx_uk_98]

Hi All.

I use this code to rename the tabs of multipage control on a userform.

Sub page()

For Each Cell In Sheets("Data").Range("major_streams")
frm_daily_entry.MultiPage1.Pages.Add Cell.Text
Next

End Sub

The problem I have is that when creating the tabs on the fly I als
have to create other controls on the fly too, specifically Labels
textboxes.

The Labels & Textboxes will be defined by looking up the caption of th
current tab (on sheet1) and adding however many label/Textboxes tha
are required (one for every item under the heading on the sheet).

Anyone have any ideas?

thanks

Kevi

 

[Dave Peterson]

Chip Pearson shows how to write code that writes code at:
http://www.cpearson.com/excel/vbe.htm

But have you thought of just putting as many controls as you need and hide
them. Unhide/recaption them when you need them.

 

[jinx_uk_98]

Thanks Dave I have taken your advice and now have a "master" page with
all the controls on it and in the UserForm_Initialize event have ths
code:

Private Sub UserForm_Initialize()
Dim newPage As page
Dim nPages As Long
Application.ScreenUpdating = False
Sheets("data").Range("b825").Select
For Each Cell In Sheets("data").Range("major_streams")
With frm_daily_entry.MultiPage1
Set newPage = .Pages.Add("" & ActiveCell.Text)
..Page1.Controls.Copy
End With
newPage.Paste
ActiveCell.Offset(0, 1).Select
Next
End Sub

This copies the master tabs and renames it based on a range.

However I am having some trouble in getting the contols to hide and
recaption them.

Any advice appreichated

 

[Dave Peterson]

My suggestion was to create all the pages manually in the VBE. If you think you
need 10, make 15 (just in case). Then you could just unhide the ones you want
to use.

I put this in a general module to show the form:

Option Explicit
Public myRng As Range
Sub testme01()

Dim myForm As UserForm1

Set myRng = Worksheets("data").Range("Major_streams")

Set myForm = New UserForm1

If myRng.Cells.Count > myForm.MultiPage1.Pages.Count Then
MsgBox "design error--contact jinx"
Exit Sub
End If

myForm.Show

End Sub

And I put this behind the userform:

Option Explicit
Private Sub UserForm_Activate()

Dim pCtr As Long
Dim myCell As Range

For pCtr = myRng.Cells.Count To Me.MultiPage1.Pages.Count
Me.MultiPage1.Pages(pCtr - 1).Visible = False
Next pCtr

pCtr = 0
For Each myCell In myRng.Cells
Me.MultiPage1.Pages(pCtr).Caption = myCell.Value
Me.MultiPage1.Pages(pCtr).Visible = True
pCtr = pCtr + 1
Next myCell

End Sub