Copy Elements From One Page To Another in Multipage with VBA in Excel

Question

I have a multipage in a userform. During run-time, the user can choose to add x number of pages at any time. The elements of each page will be the same. I am wondering if there is a way to duplicate these elements, or would I need to re-create these same elements for each new page? If so, how do I specify locations on the page where the element should be placed?

enter image description here

You can duplicate them by copying and pasting or by creating from scratch. — Siddharth Rout
– Siddharth Rout, Commented May 30, 2012 at 19:11

Siddharth Rout · Accepted Answer · 2012-05-30 19:21:26Z

9

The trick is to put all controls in a frame in the 1st page and then the rest becomes easy :)

This code will copy the controls from Page1 to Page2 after creating Page2 and align them accordingly.

Option Explicit

Private Sub CommandButton2_Click()
    Dim l As Double, r As Double
    Dim ctl As Control

    MultiPage1.Pages.Add

    MultiPage1.Pages(0).Controls.Copy
    MultiPage1.Pages(1).Paste

     For Each ctl In MultiPage1.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 MultiPage1.Pages(1).Controls
        If TypeOf ctl Is MSForms.Frame Then
            ctl.Left = l
            ctl.Top = r
            Exit For
        End If
    Next
End Sub

SNAPSHOT

enter image description here

answered May 30, 2012 at 19:21

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ehudz Over a year ago

When I run the above code, I get a "Run-time error '-2147417949 (80010108)': Automation error the object invoked as diconnected from its clients."

Siddharth Rout Over a year ago

which line is giving you the error? I just tested it again and it works.

Ehudz Over a year ago

if I comment everything out and leave just MultiPage1.Pages.Add it gives me the error

Siddharth Rout Over a year ago

That's strange. Can you upload thefile that you created just now and upload it in say wikisend.com and then share the link here.

Ehudz Over a year ago

I think there's some underlying issue affecting all my userforms in that same project. I created a new project add userform and tried the above code and it works. I'll try re-creating the userform and see what happens

|

Kazarenko · Accepted Answer · 2014-09-08 10:25:37Z

1

The "Run-time error '-2147417949 (80010108)' may be caused by having a Frame somewhere else on the form. Try removing any other frames and running again.

answered Sep 8, 2014 at 10:25

Kazarenko

111 bronze badge

Collectives™ on Stack Overflow

Copy Elements From One Page To Another in Multipage with VBA in Excel

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related