Improve company productivity with a Business Account.Sign Up

x
?
Solved

Excel 2003 Multipage Copy

Posted on 2013-01-24
2
Medium Priority
?
348 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 1200 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This article presents several of my favorite code snippets.
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.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

595 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