Announcement

Collapse
No announcement yet.

Multipage "dynamic" INDEX numbers

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Multipage "dynamic" INDEX numbers



    Hi Guys

    Im fairly new to VBA. know enough to get around the basics, but struggling to get this one going

    i have a user-form with a multi-page.
    the multi-page captions are dynamic from another sheet, that defines the tab names.

    so lets say, i have 5 expert names in that dynamic range, the multi-page will create 5 pages with the names of each of the experts.
    now i need to copy the "frame" that will have all the controls for each of the pages

    thats the part im struggling with, if i specify the amount of pages, it copies the controls, but lets say i specify 5 pages, and in the range only 4 names, i get an error

    any help would be appreciated.

    here is the code
    Code:
    Private Sub UserForm_Initialize()
    Dim pageCount As Long
    Dim n As Long
    Set pd = Worksheets("PED")
           pageCount = pd.Range("page")
        n = 0
            With MultiPage2
            For n = 0 To pageCount - 1
                If n >= .Pages.Count Then .Pages.Add
                .Pages(n).Caption = pd.Range("C" & n + 2).Value
            Next
        End With
    
    Dim l As Double, r As Double
    Dim ctl As Control
    Dim x As Integer
    
    For x = 1 To MultiPage2.Pages.Count + 1
        
        MultiPage2.Pages(0).Controls.Copy
        MultiPage2.Pages(x).Paste
    
         For Each ctl In MultiPage2.Pages(0).Controls
            If TypeOf ctl Is MSForms.Frame Then
                l = ctl.Left
                r = ctl.Top
                Exit For
            End If
        Next
    
        For Each ctl In MultiPage2.Pages(x).Controls
            If TypeOf ctl Is MSForms.Frame Then
                ctl.Left = l
                ctl.Top = r
                Exit For
            End If
        Next
    Next x
    End Sub

  • #2
    Can you attach ane example of the workbook
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3


      I have attached a sample page

      the code is on userform 1

      thanks in advance for the assistance
      Attached Files

      Comment

      Working...
      X