Solved

Excel 2003 Multipage Copy

Posted on 2013-01-24
2
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

756 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