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 also
have to create other controls on the fly too, specifically Labels &
textboxes.

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

Anyone have any ideas?

thanks

Kevin


--
jinx_uk_98
------------------------------------------------------------------------
jinx_uk_98's Profile: http://www.excelforum.com/member.php...o&userid=28878
View this thread: http://www.excelforum.com/showthread...hreadid=486908

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 wrote:

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 also
have to create other controls on the fly too, specifically Labels &
textboxes.

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

Anyone have any ideas?

thanks

Kevin

--
jinx_uk_98
------------------------------------------------------------------------
jinx_uk_98's Profile: http://www.excelforum.com/member.php...o&userid=28878
View this thread: http://www.excelforum.com/showthread...hreadid=486908

--

Dave Peterson

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


--
jinx_uk_98
------------------------------------------------------------------------
jinx_uk_98's Profile: http://www.excelforum.com/member.php...o&userid=28878
View this thread: http://www.excelforum.com/showthread...hreadid=486908

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





jinx_uk_98 wrote:

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

--
jinx_uk_98
------------------------------------------------------------------------
jinx_uk_98's Profile: http://www.excelforum.com/member.php...o&userid=28878
View this thread: http://www.excelforum.com/showthread...hreadid=486908

--

Dave Peterson