Solved

Excel 2003 Multipage Copy

Posted on 2013-01-24
2
300 Views
Last Modified: 2013-02-03
Hello,

I have a multipage which has a list of transactions, what I want to do based on the types of transactions that are in the list box I want to create sub pages for each transaction type and copy the list box over to the name page and filter the list box data based on the transaction type.

I was trying to use something like this to copy everything over but it is not working.

    With CorpBndProfile.MultiPage1
        PagesCnt = .Count
        Set newPage = .Pages.Add("Page" & (PagesCnt + 1), _
        "Page " & (PagesCnt + 1), PagesCnt)
        .Pages(PagesCnt).Controls.Copy
    End With
    newPage.Paste
0
Comment
Question by:Jordan_WM
2 Comments
 
LVL 14

Accepted Solution

by:
Faustulus earned 300 total points
ID: 38822035
I found a site on the web where they claimed that your copy/paste idea would work if you place all controls within a frame. It didn't work for me. Therefore I created code that copies each control. I tested this code on a userform with a multipage textbox and a CommandButton1 on it.
Option Explicit
 
Private Sub UserForm_Initialize()
 CommandButton1.Caption = "Add page"
End Sub
 
Private Sub CommandButton1_Click()

    Dim PageCnt As Long
    Dim Ctl As Control
    Dim NewCtl As Control

    With MultiPage1.Pages
        PageCnt = .Count
        .Add "Page" & (PageCnt + 1), "Page " & (PageCnt + 1)
        For Each Ctl In .Item(PageCnt - 1).Controls
            Set NewCtl = .Item(PageCnt).Controls.Add( _
                         "Forms." & TypeName(Ctl) & ".1", Ctl.Name)
            SetControlProperties NewCtl, Ctl
        Next Ctl
    End With
End Sub

Private Sub SetControlProperties(NewCtl As Control, _
                                 Ctl As Control)
    
    With NewCtl
        .Top = Ctl.Top
        .Left = Ctl.Left
        .Height = Ctl.Height
        .Width = Ctl.Width
        .Visible = True
        Select Case TypeName(Ctl)
            Case "TextBox"
                .Text = Ctl.Text
            Case "CheckBox"
                .Value = Ctl.Value
        End Select
    End With
End Sub

Open in new window

Run the Initialize procedure to repeat the test.

Once the new control has been created the sub SetControlProperties is called. In it you can copy all relevant properties from the original control to its copy. I just added some to show the principles. Not all types of controls have all available properties. To avoid runtime errors caused by trying to set properties that don't exist use the Select statement to set control-specific properties only for those controls that have them.

Beware of the index numbers of the pages! Pages are counted 1-based but they are indexed 0-based. So, your initial 2 pages are indexed 0 and 1. I didn't program the index number because VB will add it automatically, but if you do it shouldn't be PageCnt -1.

Let me know if you need any more help with this.
0
 

Author Closing Comment

by:Jordan_WM
ID: 38849388
Excellent thank you very much thats what I was looking for :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now