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
Solved

Excel 2003 Multipage Copy

Posted on 2013-01-24
2
316 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

837 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